Report which shows the response time overdue and resolution time overdue

Database : MSSQL

SELECT wo.WORKORDERID "Request ID",sdo.NAME "Site",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",rtdef.NAME "Request Type",pd.PRIORITYNAME "Priority",urgdef.NAME "Urgency",

longtodate(wo.FR_DUETIME) "Response DueBy Time",

longtodate(wo.DUEBYTIME) "DueBy Time",

(case when wos.IS_FR_OVERDUE='1' then 'True' else 'False' end) "First Response Overdue Status",

(case when wos.IS_FR_OVERDUE='1'

then (case when wo.respondedtime is NULL then datediff(hh,dateadd(s,wo.FR_DUETIME/1000,'1970-01-01 00:00:00'),getdate()) else datediff(hh,dateadd(s,wo.FR_DUETIME/1000,'1970-01-01 00:00:00'),dateadd(s,wo.respondedtime/1000,'1970-01-01 00:00:00')) end)

else 0 end) "Time delayed after Response due by time",

(case when wos.ISOVERDUE='1' then 'True' else 'False' end) "Overdue Status",

(case when wos.ISOVERDUE='1'

then (case when wo.completedtime=0 then datediff(hh,dateadd(s,wo.DUEBYTIME/1000,'1970-01-01 00:00:00'),getdate()) else datediff(hh,dateadd(s,wo.DUEBYTIME/1000,'1970-01-01 00:00:00'),dateadd(s,wo.completedtime/1000,'1970-01-01 00:00:00')) end)

else 0 end) "Time delayed after Due by time" FROM WorkOrder wo LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN UrgencyDefinition urgdef ON wos.URGENCYID=urgdef.URGENCYID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID INNER JOIN AccountSiteMapping ON wo.SITEID=AccountSiteMapping.SITEID

WHERE wo.ISPARENT='1'

and wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>

How to compare date column with auto filled date templates?

Here is the example for getting this week data :

CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>

<from_thisweek> - Starting date of this week

<to_thisweek> - Ending date of this week

Available Date Templates

Today - <from_today> - <to_today>

This week - <from_thisweek> - <to_thisweek>

Last week - <from_lastweek> - <to_lastweek>

This month - <from_thismonth> - <to_thismonth>