SQL Combining several SELECT results

Question

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))

Answer 1

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

Answer 2

@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.