Query problem: counting records from another table without using a subquery

greenie2600

I'm working on a rather complex SQL query, which retrieves a list of clients that match certain criteria.

One of the criteria I need to test is the number of business days that have elapsed since the client's enrollment.

To facilitate this, I've created and populated a table called business_days - containing one record for every business day (non-weekends and non-holidays) for the next ten years. Using this, I can just count the number of business_days between two dates, like this:

SELECT COUNT(*) FROM business_days WHERE date > "2005-01-01" AND date <= CURDATE();

The problem arises when I try to put this inside my larger query:

SELECT
	enrollment_date
FROM
	clients
WHERE
	( SELECT COUNT(*) FROM business_days WHERE date > client.enrollment_date AND date <= CURDATE() ) < 30

This doesn't work, of course - the subquery exists independently of the main query, so the reference to "client.enrollment_date" doesn't have anything to latch onto. So I don't think a subquery is the way to do this.

I need some way to count the business_day records between two dates without resorting to a subquery - or, alternately, some way to inject the context of the main query into the subquery. I've screwed around with a few other approaches, but had no luck. I'm officially stumped at this point. Can anyone offer suggestions?

greenie2600

Well, I've gotten somewhere...

SELECT
	clientid,
	(
		SELECT
			COUNT(*)
		FROM
			business_days
		WHERE
			business_days.date > client.enrollment_date AND 
			business_days.date <= CURDATE()
	) AS business_days_elapsed
FROM
	client

This adds a column to the resultset containing the number of business days elapsed since the client's enrollment - but when I add "WHERE business_days_elapsed < 30" to the query, I get an error: "Unknown column 'business_days_elapsed' in 'where clause'".

Argh!

greenie2600

Got it!

I had to add "HAVING business_days_elapsed < 30" instead of "WHERE business_days_elapsed < 30". This is probably horribly inefficient, but it works.

tomhath

I didn't test this, but it seems like all you need is an INNER JOIN:

SELECT clientid,
	COUNT(*) AS business_days_elapsed
FROM client 
inner join business_days
on business_days.date > client.enrollment_date
  AND business_days.date <= CURDATE()
group by clientid
having count(*) > 30