help with SQL conditional report

jmichael18
jmichael18 used Ask the Experts™
on
i have a table (stacked) with variables cipcode, awlevel, unitid, and year.  

im trying to generate a report that will tell me the distinct count of unitid where awlevel=5 and cipcode=45.0601 in 2001 but not 2011.  Basically i need results where cipcode only exists when year=2001 and doesnt exist when year=2011.

so far i have this, which gives me the results for 2001 only
	select count(distinct(unitid)) as Grad11 label="Schools with Graduations",
	from stacked
	where awlevel=5 and cipcode='45.0601' and year=2001;

Open in new window


I think the answer involves nested selects but i cant wrap my head around it.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Administrator
Top Expert 2005
Commented:
DECLARE @cipcode FLOAT,
   @awlevel INT;

SELECT @cipcode = 45.0201,
   @awlevel = 5

SELECT COUNT (DISTINCT unitid) AS Grad1
FROM stacked AS s2001
LEFT OUTER JOIN stacked AS s2011
   ON s2001.cipcode = s2011.cipcode
WHERE s2001.cipcode = @cipcode
   AND s2001.awlevel = 5
   AND s2011.cipcode IS NULL

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial