• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

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!
0
md0333
Asked:
md0333
1 Solution
 
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now