One of Frequently asked Reports could not be executed
Hi Expert,
I'd like to add one of Frequently asked Reports into SDP system, which is called Count of request created based on hour range. But when I exeute the query, the error messages poped-up. Refer to the attached file for more details.
The query of report is:
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'))
Please help to modify it.
Our system version is 9100 build 9112 (DB PostgreSQL).
Thanks
Oliver
I'd like to add one of Frequently asked Reports into SDP system, which is called Count of request created based on hour range. But when I exeute the query, the error messages poped-up. Refer to the attached file for more details.
The query of report is:
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'))
Please help to modify it.
Our system version is 9100 build 9112 (DB PostgreSQL).
Thanks
Oliver