Query to show difference between current and previous value of a Date additional field

Query to show difference between current and previous value of a Date additional field

MSSQL:

SELECT "wo"."WORKORDERID" AS "Request ID",
 "mdd"."MODENAME" AS "Request Mode", 
 "qd"."QUEUENAME" AS "Group", 
 "aau"."FIRST_NAME" AS "Requester", 
 "cd"."CATEGORYNAME" AS "Category", 
 "scd"."NAME" AS "Subcategory", 
 "wo"."TITLE" AS "Subject", 
 "sdo"."NAME" AS "Site", 
 "pd"."PRIORITYNAME" AS "Priority", 
 "lvd"."LEVELNAME" AS "Level", 
 "std"."STATUSNAME" AS "Request Status", 
 LONGTODATE(wo.CREATEDTIME) "Created Time", 
 LONGTODATE(wo.RESPONDEDTIME) "Responded Date", 
 "wos"."IS_FR_OVERDUE" AS "First Response Overdue Status", 
 LONGTODATE(wo.COMPLETEDTIME) "Completed Time", 
 "wos"."ISOVERDUE" AS "Overdue Status", 
 LONGTODATE(wo.RESOLVEDTIME) "Resolved Time",  
 "wmf"."UDF_CHAR31" AS "Updated to TMF Client",
  "wof"."UDF_CHAR32" AS "Please specify the update",
 LONGTODATE(wof.UDF_DATE4) "Current Updated Time" ,
 "wohd"."PREV_VALUE" AS "Previous Updated Time",
 "wohd"."CURRENT_VALUE" AS "Changed Updated Time",
 CAST(DATEDIFF(hh, (CONVERT(DateTime, SUBSTRING(wohd.prev_value, 9, 3) + SUBSTRING(wohd.prev_value, 5, 4) + SUBSTRING(wohd.prev_value, 25,5) + SUBSTRING(wohd.prev_value, 12,10) , 113)) , (CONVERT(DateTime, SUBSTRING(wohd.current_value, 9, 3) + SUBSTRING(wohd.current_value, 5, 4) + SUBSTRING(wohd.current_value, 25,5) + SUBSTRING(wohd.current_value, 12,10) , 113)) ) as bigint)   
 as "Difference Time in hours",
 CASE 
 WHEN CAST(DATEDIFF(mi, (CONVERT(DateTime, SUBSTRING(wohd.prev_value, 9, 3) + SUBSTRING(wohd.prev_value, 5, 4) + SUBSTRING(wohd.prev_value, 25,5) + SUBSTRING(wohd.prev_value, 12,10) , 113)) , (CONVERT(DateTime, SUBSTRING(wohd.current_value, 9, 3) + SUBSTRING(wohd.current_value, 5, 4) + SUBSTRING(wohd.current_value, 25,5) + SUBSTRING(wohd.current_value, 12,10) , 113)) ) as bigint) > 240 THEN 'Yes'
 ELSE 'No' 
 END AS "Is SLA Violated" 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 "SDOrganization" "sdo" ON "wo"."SITEID"="sdo"."ORG_ID" 
 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 "PriorityDefinition" "pd" ON "wos"."PRIORITYID"="pd"."PRIORITYID" 
 LEFT JOIN "LevelDefinition" "lvd" ON "wos"."LEVELID"="lvd"."LEVELID" 
 LEFT JOIN "StatusDefinition" "std" ON "wos"."STATUSID"="std"."STATUSID" 
 LEFT JOIN "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID" 
 LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID"
 LEFT JOIN "WorkOrder_Fields" "wof" ON "wo"."WORKORDERID"="wof"."WORKORDERID" 
 LEFT JOIN "WorkOrder_Multi_Fields" "wmf" ON "wo"."WORKORDERID"="wmf"."WORKORDERID" 
 LEFT JOIN "AccountSiteMapping" "asm" ON "wo"."SITEID"="asm"."SITEID" 
 LEFT JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID" 
 LEFT JOIN "WorkOrderHistory" "woh" ON "wo"."WORKORDERID"="woh"."WORKORDERID"
 LEFT JOIN "WorkOrderHistoryDiff" "wohd" ON "woh"."HISTORYID"="wohd"."HISTORYID"
 WHERE "wohd"."COLUMNNAME" LIKE '%Updated time%' AND "wohd"."PREV_VALUE"!='-' AND "wohd"."CURRENT_VALUE"!='-' AND "ad"."ORG_NAME" = N'TMF Group' AND "sdo"."NAME" = N'TMF AppSupport' AND "pd"."PRIORITYNAME" = N'Critical' AND wo.ISPARENT = '1' AND wo.createdtime>=<from_THISWEEK>and wo.createdtime<=<to_THISWEEK>

NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead. You can also remove or modify the filters as highlighted.

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>



      New to ADSelfService Plus?

        Resources

            • Related Articles

            • Query on current asset value

              PGSQL: SELECT productType.COMPONENTTYPENAME AS "Product Type", resource.RESOURCENAME AS "Asset Name1", resource.RESOURCENAME AS "Asset Name", product.COMPONENTNAME AS "Product", LONGTODATE(resource.ACQUISITIONDATE) AS "Acquisition Date", aaov.NAME AS ...
            • Query to display additional field associations with templates

              Use case The reports shows in which templates the created additional fields are associated  Query select sd.name "Service Catalog Name",            rt.templatename "Template name",            fc.field_name "Fields" from requesttemplate_list rt left ...
            • How to diff of two Date& Time fields using FAFR

              If you have two date and time additional fields or default fields, and if you want to capture the difference between those two fields in Hours and minutes, then use the below script to capture the value in an additional field, Under the respective ...
            • Query to show support groups and its individual custom attributes

              PGSQL & MSSQL: SELECT ci.CINAME AS "Support Group Name", su.first_name "Owned By", ci.DESCRIPTION AS "Description",ia.attributename "Additional Attributes Name",ia.attributevalue "Additional Attributes Value"  FROM BaseElement baseci LEFT JOIN CI ci ...
            • 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", ...