December 7, 2009 at 4:07 pm
Hi
I have a table that holds data relating to our service desk call logging. I would like to use a matrix table to calculate the total calls logged and total calls closed per month and year.
The result I want is:
Month, Opened, closed
January, 100, 102
February,120, 100
March,150, 160
April,50,120
etc
Here are the two queries I am using, BUT would like to combine them to use in a matrix table
Opend Calls:
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
Closed Calls
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
Any help would be appreciated
Regards
Greg
December 7, 2009 at 8:05 pm
Your query could be something like ...
SELECT 'Opened' AS OpenClosed,
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 OpenClosedCount
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
UNION ALL
SELECT 'Closed' AS OpenClosed,
DATENAME(year, sys_requestclosedate) AS Year,
DATEPART(mm, sys_requestclosedate) AS Month_No,
DATENAME(month, sys_requestclosedate) AS Month_Name,
COUNT(sys_requestclosedate) AS OpenClosedCount
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
December 7, 2009 at 8:25 pm
Thank for this. The total are right but not the format. The reesult I need it to be:
year, month_no, month,opened, closed.
2009,1,January,200, 204
2009,2,February,100,95
Thanks
GReg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply