I'm using SQL Server 2008 R2 and trying to do multiple counts on different columns and can't figure out how to get it to work properly. I have two queries that work independently, but having trouble when trying to combine them. Trying to count the number of tickets opened and closed during each individual date while passing through an @StartDate and @EndDate.
For example, here's my first query: (we assign certain codes to differentiate between different reasons tickets are created, just wanted to throw that out there in case that line creates any confusion. Also our dates are datetime format, hence the use of CAST)
SELECT
Cast(CreateDate AS Date) AS 'Date',
Count(CreateDate) AS '# Created'
FROM dbo.Tickets
WHERE
Cast(CreateDate AS Date) >= @StartDate and
Cast(CreateDate AS Date) <=@EndDate and
(Reason >= 36 and Reason <= 41 OR Reason = 17)
GROUP BY Cast(CreateDate AS Date)
ORDER BY 'Date'
Which yields the following results:
Date # Created
---------- -------------
5/1/2013 396
5/2/2013 418
5/3/2013 288
5/4/2013 28
5/5/2013 100
My second query is the exact same as the "Create Date" query, just replaced with "Resolved Date" wherever "CreateDate" is located. However, there were no tickets resolved on the date 5/4/2013.
How can I combine these two queries into one, and get it to return a result set like below?
Date # Created # Resolved
---------- ------------- -------------
5/1/2013 396 400
5/2/2013 418 322
5/3/2013 288 280
5/4/2013 28 0
5/5/2013 100 11
I think the part that is throwing me off is that there is no resolved date of 5/4/2013 in the table. Any help would be much appreciated. Thanks!