Tickets opened by hour by site query
I'd like to change the following query to do by site instead of by group. Is that possible?
I am running ManageEngine ServiceDesk 14.5 with sql database.
SELECT qd.QUEUENAME AS "Group", YEAR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) 'Year', CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=1 THEN '01. JAN' WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=2 THEN '02. FEB' WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=3 THEN '03. MAR' WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=4 THEN '04. APR' WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=5 THEN '05. MAY' WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=6 THEN '06. JUN' WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=7 THEN '07. JUL' WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=8 THEN '08. AUG' WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=9 THEN '09. SEP' WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=10 THEN '10. OCT' WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=11 THEN '11. NOV' WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00'))=12 THEN '12. DEC' ELSE NULL END 'MONTH',
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =0 THEN 1 ELSE NULL END) '12a-1a' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =1 THEN 1 ELSE NULL END) '1a-2a' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =2 THEN 1 ELSE NULL END) '2a-3a' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =3 THEN 1 ELSE NULL END) '3a-4a' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =4 THEN 1 ELSE NULL END) '4a-5a' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =5 THEN 1 ELSE NULL END) '5a-6a' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =6 THEN 1 ELSE NULL END) '6a-7a' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =7 THEN 1 ELSE NULL END) '7a-8a' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =8 THEN 1 ELSE NULL END) '8a-9a' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =9 THEN 1 ELSE NULL END) '9a-10a' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =10 THEN 1 ELSE NULL END) '10a-11a' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =11 THEN 1 ELSE NULL END) '11a-12p' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =12 THEN 1 ELSE NULL END) '12p-1p' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =13 THEN 1 ELSE NULL END) '1p-2p' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =14 THEN 1 ELSE NULL END) '2p-3p' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =15 THEN 1 ELSE NULL END) '3p-4p' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =16 THEN 1 ELSE NULL END) '4p-5p' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =17 THEN 1 ELSE NULL END) '5p-6p' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =18 THEN 1 ELSE NULL END) '6p-7p' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =19 THEN 1 ELSE NULL END) '7p-8p' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =20 THEN 1 ELSE NULL END) '8p-9p' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =21 THEN 1 ELSE NULL END) '9p-10p' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =22 THEN 1 ELSE NULL END) '10p-11p' ,
COUNT(CASE WHEN HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) =23 THEN 1 ELSE NULL END) '11p-12a' from workorder LEFT JOIN WorkOrder_Queue woq ON workorder.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID where
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.createdtime/1000),'1970-01-01 00:00:00') >= convert(varchar,'2024-01-01 00:00',21) and
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.createdtime/1000),'1970-01-01 00:00:00') <= convert(varchar,'2024-12-31 23:59',21)
GROUP BY MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')), YEAR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (workorder.CREATEDTIME/1000),'1970-01-01 00:00:00')) ,qd.QUEUENAME ORDER BY 1,2