December 7, 2009 at 3:49 pm
HI
I have the following two queries, BUT would like to conbine then into one:
SELECT DATENAME(year, sys_requestdate) AS Year, DATEPART(mm, sys_requestdate) AS Month_No, DATENAME(month, sys_requestdate) AS Month_Name,
COUNT(sys_request_id) AS Opened
FROM request
GROUP BY DATENAME(month, sys_requestdate), DATENAME(year, sys_requestdate), DATEPART(mm, sys_requestdate)
HAVING (DATENAME(year, sys_requestdate) = @year)
ORDER BY Year, Month_No, Month_Name
SELECT DATENAME(year, sys_requestclosedate) AS Year, DATEPART(mm, sys_requestclosedate) AS Month_No, DATENAME(month, sys_requestclosedate)
AS Month_Name, COUNT(sys_requestclosedate) AS Closed
FROM request
GROUP BY DATENAME(month, sys_requestclosedate), DATENAME(year, sys_requestclosedate), DATEPART(mm, sys_requestclosedate)
HAVING (DATENAME(year, sys_requestclosedate) = @year) AND (NOT (COUNT(sys_requestclosedate) IS NULL))
ORDER BY Year, Month_No, Month_Name
The result that in want is as follows
Year, Month. Opened, Closed
2009, January, 200,215
2009, February, 250,157
2009, March, 180,185
Can anyone help?
Thanks
Greg
December 7, 2009 at 4:18 pm
Greg,
See how this works:
SELECT DATENAME(year, sys_requestdate) AS Year, DATEPART(mm, sys_requestdate) AS Month_No, DATENAME(month, sys_requestdate) AS Month_Name,
COUNT(sys_request_id) AS Opened, COUNT(sys_requestclosedate) AS Closed
FROM request
GROUP BY DATENAME(month, sys_requestdate), DATENAME(year, sys_requestdate), DATEPART(mm, sys_requestdate)
HAVING (DATENAME(year, sys_requestdate) = @year)
ORDER BY Year, Month_No, Month_Name
To make it easier for forum users to help you in the future, you may want to include CREATE TABLE syntax with INSERT statements to provide sample data to test with 🙂
_________________________________
seth delconte
http://sqlkeys.com
December 7, 2009 at 5:57 pm
Thanks Seth, This works , BUT the problem is that the closed totals are a little out and it because the opened date could be in a previous month but closed in the month paramater. So I would need to have the query pull out all where the requestdate is for the month parameter and the requestclosedate where requestclosedate is = to the month parameter even if the requestdate is in a previouse month. Hope you can understand what I mean
Thanks
Greg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply