Report \ Hour range
Hi,
How to fix this report that it reads the data only for the last month?
select DATENAME(mm,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) 'Month',sum(CASE WHEN DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00'))>0 AND DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) <= 3 THEN 1 ELSE 0 END) '0:00 to 3:00 hrs',sum(CASE WHEN DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00'))>3 AND DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) <= 6 THEN 1 ELSE 0 END) '3:00 to 6:00 hrs',sum(CASE WHEN DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00'))>6 AND DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) <= 9 THEN 1 ELSE 0 END) '6:00 to 9:00 hrs',sum(CASE WHEN DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00'))>9 AND DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) <= 12 THEN 1 ELSE 0 END) '9:00 to 12:00 hrs',sum(CASE WHEN DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00'))>12 AND DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) <= 15 THEN 1 ELSE 0 END) '12:00 to 15:00 hrs',sum(CASE WHEN DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00'))>15 AND DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) <= 18 THEN 1 ELSE 0 END) '15:00 to 18:00 hrs',sum(CASE WHEN DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00'))>18 AND DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) <= 21 THEN 1 ELSE 0 END) '18:00 to 21:00 hrs',sum(CASE WHEN DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00'))>21 AND DATEPART(hh,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) <= 24 THEN 1 ELSE 0 END) '21:00 to 24:00 hrs' from workorder wo group by DATENAME(mm,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00'))
Version SD: 9.1 Build 9121
MSSQL (MS SQL Server 2012 SP3)
Thank you!