SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status", ti.FIRST_NAME AS "Technician", ((wo.TIMESPENTONREQ)/1000/3600) "Time elapsed" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID WHERE (wo.ISPARENT='1') AND wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2019-01-01 00:00:00') * 1000 AS BIGINT) AND wo.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2019-01-30 00:00:00') * 1000 AS BIGINT) ORDER BY 5
SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status", ti.FIRST_NAME AS "Technician", ((wo.TIMESPENTONREQ)/1000/3600) "Time elapsed" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID WHERE (wo.ISPARENT='1') AND dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') >= convert(varchar,'2019-01-01 00:00', 21) AND dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') <= convert(varchar,'2019-01-30 23:59', 21) ORDER BY 5
CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status", ti.FIRST_NAME AS "Technician", ((wo.TIMESPENTONREQ)/1000/3600) "Time elapsed" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek> ORDER BY 5
PGSQL:
Last 7 days :
to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) - '7 days'::interval,'yyyy-mm-dd 00:00:00'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS')
Last 15 days :
to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) - '15 days'::interval,'yyyy-mm-dd 00:00:00'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS')
Last 30 days:
to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) - '30 days'::interval,'yyyy-mm-dd 00:00:00'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS')
Next 7 days :
to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) + '7 days'::interval,'yyyy-mm-dd 23:59:59'),'YYYY-MM-DD HH24:MI:SS')
Next 15 days :
to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) + '15 days'::interval,'yyyy-mm-dd 23:59:59'),'YYYY-MM-DD HH24:MI:SS')
Next 30 days :
to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) + '30 days'::interval,'yyyy-mm-dd 23:59:59'),'YYYY-MM-DD HH24:MI:SS')
MSSQL:
Last 7 days :
dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') >= DATEADD(day, DATEDIFF(day, 0, GETDATE())-7, 0) and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') <= GETDATE()
Last 15 days :
dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') >= DATEADD(day, DATEDIFF(day, 0, GETDATE())-15, 0) and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') <= GETDATE()
Last 30 days :
dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') >= DATEADD(day, DATEDIFF(day, 0, GETDATE())-30, 0) and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') <= GETDATE()
Next 7 days :
dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') >= GETDATE() and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') <= DATEADD(s,-1,DATEADD(day, DATEDIFF(day,0,GETDATE())+7,1))
Next 15 days :
dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') >= GETDATE() and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') <= DATEADD(s,-1,DATEADD(day, DATEDIFF(day,0,GETDATE())+15,1))
Next 30 days :
dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') >= GETDATE() and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') <= DATEADD(s,-1,DATEADD(day, DATEDIFF(day,0,GETDATE())+30,1))
SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status", ti.FIRST_NAME AS "Technician" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID WHERE (wo.ISPARENT='1') AND std.statusname = 'open' AND extract(epoch FROM(now()::TIMESTAMP - to_timestamp(wo.createdtime/1000)::TIMESTAMP))/3600/24 > 7
SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status", ti.FIRST_NAME AS "Technician" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID WHERE (wo.ISPARENT='1') AND std.statusname = 'open' AND DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wo.createdtime/1000),'1970-01-01 00:00:00'),GETDATE()) > 7
SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status", ti.FIRST_NAME AS "Technician", ((wo.TIMESPENTONREQ)/1000/3600) "Time elapsed" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID WHERE (wo.ISPARENT='1') ORDER BY 5