Query to show tickets created during and outside Operational Hrs _ MSSQL

Query to show tickets created during and outside Operational Hrs _ MSSQL

NON OP Hrs:

SELECT "wo"."WORKORDERID" AS "Request ID", LONGTODATE(wo.createdtime) "Created Time", "wo"."TITLE" AS "Title", queuedefinition.queuename "Group", au.first_name "Technician", "pd"."PRIORITYNAME" AS "Priority", ad.org_name "Account" FROM "WorkOrder" "wo" LEFT JOIN "ModeDefinition" "mdd" ON "wo"."MODEID"="mdd"."MODEID" LEFT JOIN "SDUser" "sdu" ON "wo"."REQUESTERID"="sdu"."USERID" LEFT JOIN "AaaUser" "aau" ON "sdu"."USERID"="aau"."USER_ID" LEFT JOIN "DepartmentDefinition" "dpt" ON "wo"."DEPTID"="dpt"."DEPTID" LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID" LEFT JOIN "CategoryDefinition" "cd" ON "wos"."CATEGORYID"="cd"."CATEGORYID" LEFT JOIN "SubCategoryDefinition" "scd" ON "wos"."SUBCATEGORYID"="scd"."SUBCATEGORYID" LEFT JOIN "ItemDefinition" "icd" ON "wos"."ITEMID"="icd"."ITEMID" LEFT JOIN "PriorityDefinition" "pd" ON "wos"."PRIORITYID"="pd"."PRIORITYID" left join workorder_queue woq on wo.workorderid=woq.workorderid left join queuedefinition on woq.queueid=queuedefinition.queueid INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id left join aaauser AU
on wos.ownerid=au.user_id  WHERE wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth> AND wo.ISPARENT='1' and ((HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) < 8.30 or (HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) > 18.30 and datepart(dw, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00')) in (1,2,3,4,5))) or datepart(dw, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00')) in (6,7))  
ORDER BY 2

OP Hrs:

SELECT "wo"."WORKORDERID" AS "Request ID", LONGTODATE(wo.createdtime) "Created Time", "wo"."TITLE" AS "Title", queuedefinition.queuename "Group", au.first_name "Technician", "pd"."PRIORITYNAME" AS "Priority", ad.org_name "Account" FROM "WorkOrder" "wo" LEFT JOIN "ModeDefinition" "mdd" ON "wo"."MODEID"="mdd"."MODEID" LEFT JOIN "SDUser" "sdu" ON "wo"."REQUESTERID"="sdu"."USERID" LEFT JOIN "AaaUser" "aau" ON "sdu"."USERID"="aau"."USER_ID" LEFT JOIN "DepartmentDefinition" "dpt" ON "wo"."DEPTID"="dpt"."DEPTID" LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID" LEFT JOIN "CategoryDefinition" "cd" ON "wos"."CATEGORYID"="cd"."CATEGORYID" LEFT JOIN "SubCategoryDefinition" "scd" ON "wos"."SUBCATEGORYID"="scd"."SUBCATEGORYID" LEFT JOIN "ItemDefinition" "icd" ON "wos"."ITEMID"="icd"."ITEMID" LEFT JOIN "PriorityDefinition" "pd" ON "wos"."PRIORITYID"="pd"."PRIORITYID" left join workorder_queue woq on wo.workorderid=woq.workorderid left join queuedefinition on woq.queueid=queuedefinition.queueid INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id left join aaauser AU
on wos.ownerid=au.user_id  WHERE wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth> AND wo.ISPARENT='1' and (((HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')))*60 + 
datepart(mi, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00'))) > 510
and 
((HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')))*60 + 
datepart(mi, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00'))) < 1110
and  
datepart(dw, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00')) in (2,3,4,5,6) ) 
ORDER BY 2

NOTE:

1. TIme can be modified based on the OP hrs.

2. Days denotes 1 for Sunday , 7 for Saturday etc,

3. Date filter highlighted in the query can be modified and below parameters can be used instead.

How to compare date column with auto filled date templates?
  1. 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
  2. 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>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>

      • Related Articles

      • Query to show Count of tickets based on OP Hrs_ MSSQL

        MSSQL: OP Hrs: (8.30 - 18.30) SELECT  qd.queuename "Group", "pd"."PRIORITYNAME" AS "Priority", ad.org_name "Account", count(wo.workorderid) "Total Tickets"  FROM WorkOrder wo LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN ...
      • Tickets created outisde operational hours_MSSQL

        MSSQL: SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", "icd"."NAME" AS "Item", ...
      • Query to show tickets older than 30 days_MSSQL

        MSSQL: SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", sdo.NAME as "Site", ...
      • Query to show tickets created during weekends (Saturdays and Sundays)_MSSQL

        MSSQL: SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", "icd"."NAME" AS "Item", ...
      • Query to show tickets first assigned today irrespective of created date

        MSSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(dpt.DEPTNAME) "Department", max(wo.TITLE) "Subject", max(qd.QUEUENAME)"Group", max(ti.FIRST_NAME) "Technician", max(std.STATUSNAME) "Request Status", ...