SQL Statement to Get items older than specific date

OK... I have this SQL Statement that works correctly...

SELECT COUNT(*) AS Deals, Department.DepartmentName
FROM  Deal INNER JOIN Department ON Deal.DepartmentID = Department.DepartmentID
WHERE Deal.DealerID in (32)
GROUP BY Department.DepartmentName

My Result Set looks like
20, Men's Dept
30, Boy's Dept

But I need to also get the count of Deals done in prior months so something like....

SELECT COUNT(*) AS Deals, Department.DepartmentName, count(deal.dealdate < '05/01/2011')  as OldDeals
FROM  Deal INNER JOIN Department ON Deal.DepartmentID = Department.DepartmentID
WHERE Deal.DealerID in (32)
GROUP BY Department.DepartmentName

My Result Set would look something like
20, Men's Dept, 8
30, Boy's Dept, 4

I'm trying to avoid multiple SQL statements because I need to keep it grouped by department name and I already have another sql statement I am comparing with in code... trying to avoid a third.

Thanks!
md0333Asked:
Who is Participating?
 
devlab2012Commented:
Use the following query:
SELECT COUNT(*) AS Deals, Department.DepartmentName, sum(case when deal.dealdate < '05/01/2011' then 1 else 0 end)  as OldDeals
FROM  Deal INNER JOIN Department ON Deal.DepartmentID = Department.DepartmentID 
WHERE Deal.DealerID in (32) 
GROUP BY Department.DepartmentName

Open in new window

0
 
md0333Author Commented:
Outstanding! Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.