Referring to SELECT SubQueries in HAVING clause 1

[mhoyt]

I have a situation where one table (STD_DEMO) has one record, and another table (STD_PRGMS) has multiple records for that student, each with a unique START_DATE.

I need to return the name of the student, but only if their earlist program start date occurred before 3/14/02.

SELECT A.STD_NUMBER, A.LAST_NAME, A.FIRST_NAME
,(SELECT MIN(CSQ1.START_DATE) FROM STD_PRGMS CSQ1
WHERE CSQ1.ID_STD_DEMO = A.ID_STD_DEMO) AS MIN_START_DATE
FROM STD_DEMO A
GROUP BY A.STD_NUMBER, A.LAST_NAME, A.FIRST_NAME
HAVING MIN_START_DATE <= '2002-03-14 00:00:00.000'

So I am using a correlated subquery in the SELECT clause to determine that minimum date, and then assiging it the column alias of MIN_START_DATE, but I can't use that alias in the HAVING clause (nor in the WHERE clause, I tried).

Any ideas?
TIA

 

[Signit]

Is this query part of a stored procedure or an ad hoc job? (You get a couple of extra options if it's a stored proc).

As an ad hoc job why not do something like this:

SELECT A.STD_NUMBER,
       A.LAST_NAME,
       A.FIRST_NAME
  FROM STD_DEMO A 
 WHERE '2002-03-14 00:00:00.000' >= (SELECT MIN(CSQ1.START_DATE)
                                       FROM STD_PRGMS CSQ1
                                      WHERE CSQ1.ID_STD_DEMO = A.ID_STD_DEMO)
GROUP BY A.STD_NUMBER, A.LAST_NAME, A.FIRST_NAME

 

[mhoyt]

Good solution! Thank you. I hadn't thought about putting the subquery in the WHERE clause, but it works fine.

I understated the problem a bit for posting, you mind if I ask you the more complex situation? In the second table there are multiple records per student, and what I actuallyhave to compare to is the minimum start date and maximum end date.

Your solution works for this, I can put both criteria in the WHERE. But what if I want to return those two values? That's why I was trying to get them in SELECT as distinct columns, then I'd have multiple records in my result set for each student, but grouping them would eliminate the duplication.

Also, I could make it a sp, be curious as to what other options you suggest.

Thanks again! Mike

 

[mhoyt]

Never mind. Figured it out, nothing stops me from putting the same subquery into the SELECT to return the values I want.

Working query in all it's glory.

SELECT A.STD_NUMBER, A.LAST_NAME, A.FIRST_NAME
,(SELECT MIN(CSQ1.START_DATE) FROM CDM_STD_ELL CSQ1
WHERE CSQ1.ID_STD_DEMO_DISTRICT = A.ID_STD_DEMO_DISTRICT) AS MIN_START_DATE
,(SELECT MAX(CSQ2.END_DATE) FROM CDM_STD_ELL CSQ2
WHERE CSQ2.ID_STD_DEMO_DISTRICT = A.ID_STD_DEMO_DISTRICT) AS MAX_END_DATE
FROM CSL_STD_DEMO_DISTRICT A JOIN CSL_STD_DEMO_SCHOOL B
ON A.ID_STD_DEMO_DISTRICT = B.ID_STD_DEMO_DISTRICT
WHERE B.ACTIVE_STATUS = 'Y'
AND (SELECT MIN(CSQ1.START_DATE) FROM CDM_STD_ELL CSQ1
WHERE CSQ1.ID_STD_DEMO_DISTRICT = A.ID_STD_DEMO_DISTRICT) <= '2002-03-14 00:00:00.000'
AND (SELECT MAX(CSQ2.END_DATE) FROM CDM_STD_ELL CSQ2
WHERE CSQ2.ID_STD_DEMO_DISTRICT = A.ID_STD_DEMO_DISTRICT) >= '2005-03-14 00:00:00.000'
GROUP BY A.ID_STD_DEMO_DISTRICT, A.STD_NUMBER, A.LAST_NAME, A.FIRST_NAME
ORDER BY A.STD_NUMBER

Still be curious as your other option if I make it a sp. But thanks so much!

 

[Signit]

Looks good!

 

[maswien]

This is a pretty typical group by with having example,
try following:

select sd.std_number, a.last_name, a.first_name, min(sp.START_DATE)
from
std_demo sd inner join std_prgms sp
on sd.std_number = sp.std_number
group by sd.std_number, a.last_name, a.first_name
having min(sp.START_DATE) < '2002-03-14 00:00:00.000'

 

[mhoyt]

MASWIEN, I really appreciate your help. Your solution is so much more elegant and I'm sure you are right, this does seem a typical problem so you've left me better prepared to deal with this in the future. Here is my query rewritten to conform to your design. I'm very happy to say it returns the exact same result set as my previous effort.

SELECT A.STD_NUMBER, A.LAST_NAME, A.FIRST_NAME, MIN(C.START_DATE), MAX(C.END_DATE)
FROM CSL_STD_DEMO_DISTRICT A JOIN CSL_STD_DEMO_SCHOOL B
ON A.ID_STD_DEMO_DISTRICT = B.ID_STD_DEMO_DISTRICT
JOIN CDM_STD_ELL C
ON C.ID_STD_DEMO_DISTRICT = A.ID_STD_DEMO_DISTRICT
WHERE B.ACTIVE_STATUS = 'Y'
GROUP BY A.ID_STD_DEMO_DISTRICT, A.STD_NUMBER, A.LAST_NAME, A.FIRST_NAME
HAVING MIN(C.START_DATE)<= '2002-03-14 00:00:00.000'
AND MAX(C.END_DATE) >= '2005-03-14 00:00:00.000'
ORDER BY A.STD_NUMBER