CODEWITHSUNDEEP
sqlsql-server
Mitch
Mitch

Reputation: 2551

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

Upvotes: 4

Views: 2357

Answers (2)

Myat Htut
Myat Htut

Reputation: 155

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

Upvotes: 0

Guffa
Guffa

Reputation: 700910

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

Upvotes: 13

Related Questions