Query to Get Group and monthly totals
I need a query that will get the monthly total for each group from 01.01.2013 -- 08.31.2014. I have the below query that will easily return the information for 2013, but when modified for just 2014, I do not trust the numbers. They seem a bit high, but I do not know the data.
SELECT qd.QUEUENAME "Group",ti.FIRST_NAME "Technician", COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=1 THEN 1 ELSE NULL END) 'JAN',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=2 THEN 1 ELSE NULL END) 'FEB',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=3 THEN 1 ELSE NULL END) 'MAR',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=4 THEN 1 ELSE NULL END) 'APR',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=5 THEN 1 ELSE NULL END) 'MAY' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=6 THEN 1 ELSE NULL END) 'JUN' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=7 THEN 1 ELSE NULL END) 'JUL' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=8 THEN 1 ELSE NULL END) 'AUG' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=9 THEN 1 ELSE NULL END) 'SEP' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=10 THEN 1 ELSE NULL END) 'OCT' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=11 THEN 1 ELSE NULL END) 'NOV' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=12 THEN 1 ELSE NULL END) 'DEC'
from workorder
LEFT JOIN WorkOrderStates wos ON workorder.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN WorkOrder_Queue woq ON workorder.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID where workorder.ISPARENT=1 AND dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,'2013-01-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,'2013-12-31 23:59',21) group by qd.QUEUENAME,ti.FIRST_NAME order by 1
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=2 THEN 1 ELSE NULL END) 'FEB',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=3 THEN 1 ELSE NULL END) 'MAR',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=4 THEN 1 ELSE NULL END) 'APR',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=5 THEN 1 ELSE NULL END) 'MAY' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=6 THEN 1 ELSE NULL END) 'JUN' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=7 THEN 1 ELSE NULL END) 'JUL' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=8 THEN 1 ELSE NULL END) 'AUG' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=9 THEN 1 ELSE NULL END) 'SEP' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=10 THEN 1 ELSE NULL END) 'OCT' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=11 THEN 1 ELSE NULL END) 'NOV' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=12 THEN 1 ELSE NULL END) 'DEC'
from workorder
LEFT JOIN WorkOrderStates wos ON workorder.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN WorkOrder_Queue woq ON workorder.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID where workorder.ISPARENT=1 AND dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,'2013-01-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,'2013-12-31 23:59',21) group by qd.QUEUENAME,ti.FIRST_NAME order by 1