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