Tickets created outisde operational hours

Tickets created outisde operational hours

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", "wo"."TITLE" AS "Subject", longtodate("wo"."CREATEDTIME") AS "Created Time", "pd"."PRIORITYNAME" AS "Priority"  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" WHERE  ("pd"."PRIORITYNAME" COLLATE Latin1_General_CI_AS = N'1. Critical') AND  wo.ISPARENT='1' and ((HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) < 7.30 or (HOUR(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) > 16 and datepart(dw, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00')) in (2,3,4,5,6))) or datepart(dw, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00')) in (1,7)) AND ("wo"."CREATEDTIME" >= datetolong('2018-01-01') AND (("wo"."CREATEDTIME" != 0) AND ("wo"."CREATEDTIME" IS NOT NULL))) AND ("wo"."CREATEDTIME" <= 1516629599000) AND ("wo"."CREATEDTIME" != 0) AND ("wo"."CREATEDTIME" IS NOT NULL)


Note: Operational hours highlighted in the query can be modified as per requirement.

    • Related Articles

    • No Managed Connections. Steps to enable Read Committed Snapshot Isolation- MSSQL

      Enabling Read Committed Snapshot Isolation in MS SQL server In MS SQL environments enabling 'Read committed Snapshot isolation (RCSI)' is the first step in handling application server performance and crash issues. What is RCSI?   Since databases ...

    • Steps to be followed for FOS Crash Issue

      Kindly enable the access logger of Tomcat of the product. This can be done by modifying the <MASTER_prod_home>/conf/server.xml file. Have a BACKUP of this file by creating a COPY of it before doing any modifications. 1) Search for "access log" string ...

    • Skip forced backup before upgrade

      Please ensure there is a recent VM backup/snapshot or a valid SQL backup or PGSQL backup that was taken just before the upgrade and then skip application backup. For 9000 Upgrade: Edit the batch file called 'Updatemanager.bat' found under ...