4

I have three SELECT statements that each return a total, 'New Cases', 'Closes Cases', 'Existing Cases'. How do I combine these so they are returned in one resultset.

ie I need a table returned with 3 fields, 'New Cases', 'Closes Cases' and 'Existing Cases' each with one total

SELECT     COUNT(CaseID) AS 'New Cases'
FROM         dbo.ClientCase
WHERE     (CaseStartDate >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
  AND     (CaseStartDate <= CONVERT(DATETIME, '2009-03-31 00:00:00', 102))

SELECT     COUNT(CaseID) AS 'Closed Cases'
FROM         dbo.ClientCase
WHERE     (CaseClosedDate >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102))
  AND     (CaseClosedDate <= CONVERT(DATETIME, '2009-03-31 00:00:00', 102))

SELECT     COUNT(CaseID) AS 'Existing Cases'
FROM         dbo.ClientCase
WHERE     (CaseStartDate <= CONVERT(DATETIME, '2009-03-31 00:00:00', 102))
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Mitch
  • 2,471
  • 8
  • 38
  • 46

2 Answers2

13

As you are counting the same data, you can do it in parallel:

select
   sum(case when CaseStartDate between '2009-01-01' and '2009-03-31' then 1 else 0 end) as [New Cases],
   sum(case when CaseClosedDate between '2009-01-01' and '2009-03-31' then 1 else 0 end) as [Closed Cases],
   sum(case when CaseStartDate <= '2009-03-31' then 1 else 0 end) as [Existing Cases]
from
   dbo.ClientCase
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
0

@Mitch , @Guffa .. A good solution but in order to get the correct result, you need to take both of CaseStartDate and CaseClosedDate fields into consideration. Eg. in New Cases, if you don't filter CaseClosedDate data you won't get the correct no. of NewCases as it will still count in the closed cases too.

Sorry I have to post it as an answer coz i don't have enuf reputations to add a comment.

Myat Htut
  • 155
  • 2
  • 9
  • Well, that depends on whether you want 'New Cases' or 'New Cases That Has Not Yet Been Closed'. – Guffa May 09 '09 at 20:20