SQL Combining several SELECT results

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

From stackoverflow

• 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
  

  John Gietzen : +1 for reading in to the question. This would be quite efficient.

  codeulike : yeah, thats a nice solution

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

  Guffa : Well, that depends on whether you want 'New Cases' or 'New Cases That Has Not Yet Been Closed'.