locked
calculate date time excluding weekend RRS feed

  • Question

  • I have below table named  "act_log"

    CREATE TABLE [dbo].[act_log](
    [id] [int] NOT NULL,
    [persid] [nvarchar](30) NULL,
    [call_req_id] [nvarchar](30) NULL,
    [last_mod_dt] [int] NULL,
    [time_spent] [int] NULL,
    [time_stamp] [int] NULL,
    [system_time] [int] NULL,
    [analyst] [binary](16) NULL,
    [description] [ntext] NULL,
    [action_desc] [ntext] NULL,
    [type] [nvarchar](30) NULL,
    [knowledge_session] [nvarchar](80) NULL,
    [knowledge_tool] [nvarchar](12) NULL,
    [internal] [int] NULL,
    [tenant] [binary](16) NULL,
    [rel_ticket_type] [nvarchar](12) NULL,
    [zClarityLastSync] [int] NULL,

    Below are the result based on the following query executed :

    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type

     from act_log
      where call_req_id = 'cr:410416'

    Output

    ------------------

    call_req_id type Status Date description
    cr:410416 INIT 3/5/2019 2:01 Create a new request/incident/problem/change/issue
    cr:410416 ST 3/5/2019 2:01 Status changed from 'Open' to 'Acknowledged'
    cr:410416 ST 3/5/2019 2:02 Status changed from 'Acknowledged' to 'Awaiting Vendor'
    cr:410416 SLADELAY 3/5/2019 2:02 The Service Type(s) on a ticket were delayed
    cr:410416 ST 3/5/2019 2:26 Status changed from 'Awaiting Vendor' to 'In Progress'
    cr:410416 SLARESUME 3/5/2019 2:26 The Service Type(s) on the ticket were resumed
    cr:410416 ST 3/5/2019 2:33 Status changed from 'In Progress' to 'Awaiting End User Response'
    cr:410416 SLADELAY 3/5/2019 2:33 The Service Type(s) on a ticket were delayed
    cr:410416 RE 3/5/2019 2:39 Status changed from 'Awaiting End User Response' to 'Resolved'
    cr:410416 SLARESUME 3/5/2019 2:39 The Service Type(s) on the ticket were resumed


    -------------------------------------

    I want to calculate the time difference between SLADELAY and SLARESUME in Days Hours Mins. Where in the above example there is multiple SLADELAY and SLARESUME then it will give total sum.  But it should be on criteria where it will not include Friday and Saturday  which is non working days and also only calculate the Business Hours which is 7 am to 6PM

    Please advise. 




    • Edited by du00805 Tuesday, March 5, 2019 8:00 AM
    Tuesday, March 5, 2019 7:55 AM

Answers

  • Yes, I did but now giving error :

    Msg 207, Level 16, State 1, Line 489
    Invalid column name 'Vendor Elapsed Time'.

    try like

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      --where call_req_id = 'cr:410416'
    ),Final
    AS
    (
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.BusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'07:00','18:00') AS BusinessTime
    FROM CTE
    WHERE type IN ('SLADELAY','SLARESUME')
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )
    
    
    SELECT 
    
                  /*call_req.id*/
    
          call_req.persid,
    
          call_req.ref_num as "Ticket Num"
    
          ,call_req.summary as "Summary",
    
             call_req.description as "Description"
    
          /*,call_req.status*/
    
          ,call_req.active_flag as "Active Flag"
    
      ,DATEADD(ss, al.last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Last Status Date"
    
      ,al.description as StatusComments
    
          ,call_req.open_date
    
          ,call_req.time_spent_sum as  "Time Spent Sum"
    
          ,call_req.last_mod_dt      
    
          ,call_req.close_date
    
             ,call_req.resolve_date
    
          /*,call_req.rootcause as  "Root Cause"*/
    
          ,Rootcause.sym as "Root Cause"
    
          ,call_req.charge_back_id as "Charge_Back id"
    
          ,call_req.support_lev as "Support Level"
    
          /*,call_req.category*/
    
          /*,call_req.impact*/
    
          /*,call_req.priority*/
    
          /*,call_req.urgency*/
    
          /*,call_req.severity*/
    
          ,call_req.extern_ref as "External Reference"
    
      ,ca_resource_department.name as DepartmentName
    
      ,ca_resource_cost_center.name as CostCenter
    
      ,ca_location.location_name as LocationName
    
      ,ca_site.name as SiteName
    
      
    
            ,call_req.last_act_id as "Last Action Id"
    
          /*,call_req.cr_tticket as  "Cr tticket"*/
    
          /*,call_req.parent as "Parent"*/
    
          ,Parent.ref_num as "Parent"
    
          ,Parent.status as "Parent Status"
    
          /*,call_req.template_name as "Template Name"*/
    
          ,call_req.sla_violation as "SLA Violation"
    
          ,call_req.predicted_sla_viol as "Predicted SLA Violation"
    
          ,call_req.macro_predict_viol as "Macro Predicted Violation"
    
          /*,call_req.created_via*/
    
          /*,call_req.call_back_date*/
    
          ,call_req.call_back_flag as "Call Back Flag"
    
          ,call_req.event_token as "Event Token"
    
          ,call_req.sched_token as "Sched Token"
    
          ,call_req.type as "Ticket Type"
    
          /*,call_req.string1 as "string1"
    
          ,call_req.string2 as "string2"
    
          ,call_req.string3 as "string3"
    
          ,call_req.string4 as "string4"
    
          ,call_req.string5 as "string5"
    
          ,call_req.string6 as "string6"*/
    
          /*,call_req.problem as "Problem"*/
    
          ,Problem.ref_num as "Problem"
    
          ,Problem.status as "Problem Status"
    
          
    
          ,call_req.incident_priority as "Incident Priority"
    
          /*,call_req.change as "Change"*/
    
          ,Change.chg_ref_num as "Change"
    
          ,Change.status as "Change Status"
    
          ,call_req.ticket_avoided as "Ticket Avoided"
    
          /*,call_req.caused_by_chg as "Caused By Chg"*/
    
          ,caused_by_chg.chg_ref_num as "Caused By Chg"
    
          ,caused_by_chg.status as "Caused By Chg Status"
    
          /*,call_req.outage_start_time*/
    
          /*,call_req.outage_end_time*/
    
          ,call_req.external_system_ticket as "External System Ticket"
    
          ,call_req.incorrectly_assigned as "Incorrectly Assigned"
    
          ,call_req.major_incident as "Major Incident"
    
          ,call_req.outage_detail_what as "Outage Detail What"
    
          ,call_req.outage_detail_who as "Outage Detail Who"
    
          ,call_req.outage_detail_why as "Outage Detail Why"
    
          ,call_req.outage_reason_desc as "Outage Reason Desc "
    
          ,call_req.outage_type as "Outage Type"
    
          ,call_req.pct_service_restored as "Pct Service Restored"
    
          ,call_req.remote_control_used as "Remote Control Used"
    
          /*,call_req.resolution_code as "Resolution Code"*/
    
          ,res_code.sym as "Resolution Code"  
    
          /*,call_req.resolution_method as "Resolution Method"*/
    
          ,res_method.sym as "Resolution Method"
    
          ,call_req.resolvable_at_lower as "Resolvable At Lower"
    
          ,call_req.return_to_service as "Return to Service"
    
          /*,call_req.symptom_code as "Symptom Code"*/
    
          ,symptom.sym as "Symptom Code"
    
          ,call_req.target_closed_count as "Target Closed Count"
    
          ,call_req.target_closed_last as "Target Closed Last"
    
          ,call_req.target_hold_count as "Target Hold Count"
    
          ,call_req.target_hold_last as "Target Hold Last"
    
          ,call_req.target_resolved_count as "Target Resolved Count"
    
          ,call_req.target_resolved_last as "Target Resolved Last"
    
          ,call_req.target_start_last as "Target Start Last"
    
          ,call_req.fcr as "fcr"
    
          ,call_req.zusmrequestid as "USM Request ID"
    
          ,call_req.zusmrequestitemid as "USM Request Item ID"
    
          /*,call_req.zcategory_init as "Initial category"*/
    
          ,init_cat.sym as "Initial Category",
    
             /*call_req.heat as "Heat",*/
    
          
    
          DATEADD(ss, call_req.open_date +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Open Date",
    
          DATEADD(ss, call_req.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Last Modification Date",
    
          DATEADD(ss, call_req.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Close Date",
    
          DATEADD(ss, call_req.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Resolve Date",
    
          DATEADD(ss, call_req.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Call_back Date",
    
          DATEADD(ss, call_req.outage_start_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage Start Time",
    
          DATEADD(ss, call_req.outage_end_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage End Time",
    
          
    
          DATEADD(ss, parent.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Open Date",
    
          DATEADD(ss, parent.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Last Modification Date",
    
          DATEADD(ss, parent.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Close Date",
    
          DATEADD(ss, parent.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Resolve Date",
    
          DATEADD(ss, parent.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Call_back Date",
    
          
    
          DATEADD(ss, problem.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Open Date",
    
          DATEADD(ss, problem.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Last Modification Date",
    
          DATEADD(ss, problem.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Close Date",
    
          DATEADD(ss, problem.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Resolve Date",
    
          DATEADD(ss, problem.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Call_back Date",
    
          
    
          DATEADD(ss, change.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Open Date",
    
          DATEADD(ss, change.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Last Modification Date",
    
          DATEADD(ss, change.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Close Date",
    
          DATEADD(ss, change.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Resolve Date",
    
          DATEADD(ss, change.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Call_back Date",
    
          
    
          /*Time Since opening-- only for open tickets*/
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) END AS "Time Since Opening(Seconds)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60 END AS "Time Since Opening(Hours)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 END AS "Time Since Opening(Days)",
    
          
    
           /*Time Since Last modification -- only for open tickets*/
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt) END AS "Time Since Last Modification(Seconds)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60 END AS "Time Since Last Modification(Hours)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60/24 END AS "Time Since Last Modification(Days)",
    
          
    
          
    
          /*Time to resolve-- if Ticket is not resolved, time till now*/
    
          case when ( call_req.resolve_date is not null   and call_req.resolve_date >= call_req.open_date) THEN      
    
             (call_req.resolve_date - call_req.open_date)else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) end AS "Time To Resolve(Seconds)",    
    
               
    
          case when ( call_req.resolve_date is not null AND call_req.open_date is not null  and call_req.resolve_date >= call_req.open_date) THEN      
    
             (call_req.resolve_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60  end AS "Time To Resolve(Hours)",
    
             
    
             case when ( call_req.resolve_date is not null AND call_req.open_date is not null  and call_req.resolve_date >= call_req.open_date) THEN      
    
             (call_req.resolve_date - call_req.open_date)/60/60/24  else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 end AS "Time To Resolve(Days)",
    
             
    
             /*Time to close-- if Ticket is not closed, time till now*/
    
             case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
    
             (call_req.close_date - call_req.open_date) else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)  end AS "Time To Close(Seconds)",    
    
               
    
          case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
    
             (call_req.close_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60  end AS "Time To Close(Hours)",
    
             
    
             case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
    
             (call_req.close_date - call_req.open_date)/60/60/24  else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24  end AS "Time To Close (Days)",
    
             
    
             /*Resolve to close, if ticket is not closed time from resolve to Now.*/
    
             case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
    
             (call_req.close_date - call_req.resolve_date)  WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)   end AS "Resolve To Close(Seconds)", 
    
               
    
          case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
    
             (call_req.close_date - call_req.resolve_date)/60/60 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60   end AS "Resolve To Close(Hours)",
    
           
    
             case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
    
             (call_req.close_date - call_req.resolve_date)/60/60/24 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24   end AS "Resolve To Close(Days)", 
    
             
    
            
    
               
    
          
    
                          /*Datediff (hour, call_req.open_date,call_req.resolve_date) as "Time To Resolve (Hours)",      
    
                          Datediff (day, call_req.resolve_date, call_req.close_date ) as "Resolve To Close (Days)",
    
                          Datediff (hour, call_req.resolve_date, call_req.close_date) as "Resolve To Close (Hours)",*/
    
         
    
         
    
          
    
             
    
           srv_desc.sym as "Service Type",
    
           sevrty.sym as "Severity",
    
           urgncy.sym as "Urgency", 
    
           prob_ctg.sym as "Category",
    
           
    
           net_res.resource_name as "Asset Name",
    
           gen_res.name as "Asset Class",
    
           resfam.name  as "Asset Family",
    
           
    
           service_net_res.resource_name as "Affected Service",
    
           service_gen_res.name as "Affected Service Class",
    
           service_resfam.name  as "Affected Service Family",
    
           ci_enterprise_service.business_impact as "Business Impact", 
    
           ci_enterprise_service.business_priority as "Business Priority", 
    
           
    
           impact.sym as "Impact", 
    
           
    
           cr_stat.sym as "Status",
    
           pri.sym as "Priority",
    
           interface.sym as "Created Via", 
    
           
    
           
    
           View_Group.last_name as "Group", 
    
           
    
           
    
           /*customer.last_name as "Customer Lastname",
    
           customer.first_name as "Customer Firstname",
    
           customer.middle_name as "Customer Middlename", */
    
           customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ')  as "Customer", 
    
      --customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ')  as "Supervisior", 
    
           customer_loc.location_name as       "Customer Location", 
    
           customer_org.org_name        as     "Customer Organization", 
    
           customer_admin.org_name      as "Customer Admin Organization",
    
           
    
           /*assignee.last_name as "Assignee Lastname", 
    
           assignee.first_name as "Assignee Firstname", 
    
           assignee.middle_name as "Assignee Middlename",*/
    
           assignee.last_name + coalesce( ', ' + assignee.first_name , ' ')+ coalesce (' ' + assignee.middle_name,' ') as "Assignee",
    
           assignee_loc.location_name as       "Assignee Location", 
    
           assignee_org.org_name        as     "Assignee Organization", 
    
           assignee_admin.org_name as   "Assignee Admin Organization",
    
           
    
           /*log_agent.last_name as "Log Agent Lastname", 
    
           log_agent.first_name as "Log Agent Firstname", 
    
           log_agent.middle_name as "Log Agent Middlename",*/
    
           log_agent.last_name + coalesce( ', ' + log_agent.first_name , ' ')+ coalesce (' ' + log_agent.middle_name,' ') as "Log Agent",          
    
           log_agent_loc.location_name as      "Log Agent Location",        
    
           log_agent_org.org_name       as     "Log Agent Organization", 
    
           log_agent_admin.org_name as  "Log Agent Admin Organization",
    
           
    
           /*last_mod_by.last_name as "Analyst Last Modified Lastname", 
    
           last_mod_by.first_name as "Analyst Last Modified Firstname", 
    
           last_mod_by.middle_name as "Analyst Last Modified Middlename",*/
    
           last_mod_by.last_name + coalesce( ', ' + last_mod_by.first_name , ' ')+ coalesce (' ' + last_mod_by.middle_name,' ') as "Analyst Last Modified",
    
           last_mod_by_loc.location_name as    "Analyst Last Modified Location",    
    
           last_mod_by_org.org_name     as     "Analyst Last Modified Organization", 
    
           last_mod_by_admin.org_name as       "Analyst Last Modified Admin Organization",
    	   CONCAT(DATEDIFF(dd,0,DATEADD(ss,f.TotalBusinessTime,0)), ' Days ', 
    		LEFT(CONVERT(varchar(8),DATEADD(ss,f.TotalBusinessTime,0),108),2),' Hours ' ,
    		SUBSTRING(CONVERT(varchar(8),DATEADD(ss,f.TotalBusinessTime,0),108),4,2),' Minutes ',
    		SUBSTRING(CONVERT(varchar(8),DATEADD(ss,f.TotalBusinessTime,0),108),7,2),' Seconds') as  [Vendor Elapsed Time]
    
    FROM (
    
           (
    
                  (      
    
                          (
    
                                 (
    
                                        (
    
                                               (
    
                                                      (
    
                                                              (      
    
                                                                     (
    
                                                                            (
    
                                                                                   (
    
                                                                                           (
    
                                                                                                  (
    
                                                                                                         (
    
                                                                                                                (
    
                                                                                                                       (
    
                                                                                                                               (
    
                                                                                                                                      (
    
                                                                                                                                             (
    
                                                                                                                                                    (
    
                                                                                                                                                            (
    
                                                                                                                                                                   (
    
                                                                                                                                                                          (
    
                                                                                                                                                                                 (
    
                                                                                                                                                                                        (
    
                                                                                                                                                                                                (
    
    (      
    
           (
    
                  (
    
                          (
    
                                 (
    
                                        (
    
                                               (
    
                                                      (
    
                                                              (
    
                                                                     (
    
                                                                            (
    
                                                                                   (
    
                                                                                           (      
    
                                                                                                                                                                                                
    
    (  call_req 
    
    left join RootCause ON call_req.rootcause = rootcause.id)
    
    left join call_req Problem ON call_req.problem = Problem.persid)
    
    left join call_req Parent ON call_req.problem = Parent.persid)
    
    left join Chg Change ON call_req.change = Change.id)
    
    left join usp_resolution_code res_code ON call_req.resolution_code= res_code.id)
    
    left join usp_resolution_method res_method ON call_req.resolution_method= res_method.id)
    
    left join usp_symptom_code symptom ON call_req.symptom_code= symptom.id)
    
    left join Chg caused_by_chg ON call_req.change = caused_by_chg.id)
    
    inner join  pri ON call_req.priority = pri.enum) 
    
    inner join  ca_contact customer ON call_req.customer =customer.contact_uuid) 
    
    left join ca_resource_department on ca_resource_department.id=customer.department
    
    left join ca_resource_cost_center on ca_resource_cost_center.id=customer.cost_center
    
    left join ca_location on ca_location.location_uuid=customer.location_uuid
    
    left join ca_site on ca_location.site_id=ca_site.id
    
    left join 
    
    (
    
    SELECT *,ROW_NUMBER() OVER (PARTITION BY call_req_id ORDER BY last_mod_dt DESC) AS Seq
    
    FROM act_log 
    
    where (act_log.type like 'ST' or act_log.type like 'INIT' or act_log.type like 'Log' or act_log.type like 'RE') and act_log.description not like '%Status changed%'
    
    and act_log.description not like '%Workflow task%' and  act_log.description not like '%AHD%' and  act_log.description not like '%create%'
    
       )al on al.call_req_id= call_req.persid and al.Seq = 1
    
    left join Final f
    on f.call_req_id = call_req.persid
    
    left join  srv_desc ON call_req.support_lev = srv_desc.code) 
    
    left join  sevrty ON call_req.severity = sevrty.enum) 
    
    left join  urgncy ON call_req.urgency = urgncy.enum) 
    
    left join  impact ON call_req.impact = impact.enum)
    
    left join  ca_owned_resource net_res ON call_req.affected_rc = net_res.own_resource_uuid)
    
    left join  ca_resource_class gen_res ON net_res.resource_class = gen_res.id) 
    
    left join  ca_resource_family resfam ON gen_res.family_id = resfam.id)
    
    left join  ca_owned_resource service_net_res ON call_req.affected_service = service_net_res.own_resource_uuid)
    
    left join  ci_enterprise_service ON call_req.affected_service = ci_enterprise_service.own_resource_uuid)
    
    left join  ca_resource_class service_gen_res ON service_net_res.resource_class = service_gen_res.id) 
    
    left join  ca_resource_family service_resfam ON service_gen_res.family_id = service_resfam.id)
    
           
    
    left join  prob_ctg ON call_req.category = prob_ctg.persid) 
    
    left join  prob_ctg init_cat ON call_req.zcategory_init = init_cat.persid) 
    
    left join  View_Group ON call_req.group_id = View_Group.contact_uuid) 
    
    left join  ca_contact assignee ON call_req.assignee = assignee.contact_uuid)
    
    left join  ca_contact log_agent ON call_req.log_agent = log_agent.contact_uuid)
    
    left join  ca_contact last_mod_by ON call_req.log_agent = last_mod_by.contact_uuid)
    
    left join  cr_stat ON call_req.status = cr_stat.code)
    
    left join  Interface ON call_req.created_via = Interface.id)
    
           left join  ca_organization   customer_org ON  customer.organization_uuid = customer_org.organization_uuid) 
    
           left join  ca_organization   customer_admin ON customer.admin_organization_uuid = customer_admin.organization_uuid)
    
           left join  ca_location              customer_loc ON customer.location_uuid =     customer_loc.location_uuid) 
    
           
    
           
    
           left join  ca_organization   assignee_org ON  assignee.organization_uuid = assignee_org.organization_uuid) 
    
           left join  ca_organization   assignee_admin ON assignee.admin_organization_uuid = assignee_admin.organization_uuid)
    
           left join  ca_location              assignee_loc ON assignee.location_uuid =     assignee_loc.location_uuid) 
    
           
    
           left join  ca_organization   log_agent_org ON  log_agent.organization_uuid = log_agent_org.organization_uuid) 
    
           left join  ca_organization   log_agent_admin ON log_agent.admin_organization_uuid =        log_agent_admin.organization_uuid)
    
           left join  ca_location              log_agent_loc ON log_agent.location_uuid =    log_agent_loc.location_uuid) 
    
           
    
           left join  ca_organization   last_mod_by_org ON  last_mod_by.organization_uuid = last_mod_by_org.organization_uuid) 
    
           left join  ca_organization   last_mod_by_admin ON last_mod_by.admin_organization_uuid =        last_mod_by_admin.organization_uuid)
    
           left join  ca_location              last_mod_by_loc ON last_mod_by.location_uuid = last_mod_by_loc.location_uuid) 
    
           
    
           where call_req.template_name is null 
    
                     --and call_req.ref_num='RE5054'
    
                      order by al.last_mod_dt

    Please copy and use the above as is


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by du00805 Wednesday, March 6, 2019 1:23 PM
    Wednesday, March 6, 2019 12:22 PM
  • HI Visakh,

    While running the below query I am getting the error  as shown in below screenshot

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      --where call_req_id = 'cr:410416'
    ),Final
    AS
    (
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.CalculateBusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'07:00','18:00') AS BusinessTime
    WHERE type IN ('SLADELAY','SLARESUME')
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )
    
    SELECT call_req_id,
    CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds')
    FROM Final

    try like this

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      --where call_req_id = 'cr:410416'
    ),Final
    AS
    (
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.CalculateBusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'07:00','18:00') AS BusinessTime
    FROM CTE
    WHERE type IN ('SLADELAY','SLARESUME')
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )
    
    SELECT call_req_id,
    CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds')
    FROM Final


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by du00805 Wednesday, March 6, 2019 8:37 AM
    Tuesday, March 5, 2019 12:00 PM
  • sure will do. Visakh can you check what modification I can do while creating the view for the query modified by you.

    https://social.msdn.microsoft.com/Forums/en-US/30a71ec3-5ebb-400a-82c7-486db85c3528/create-view-error?forum=transactsql

    ORDER BY should be removed as it is meaningless inside view code unless you had a TOP x critieria

    so do like this

    create view test as 
    With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      --where call_req_id = 'cr:401219'
    ),Final
    AS
    (
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.BusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'09:00','17:00') AS BusinessTime
    FROM CTE
    WHERE type IN ('SLADELAY','SLARESUME')
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )
    
    SELECT 
                  /*call_req.id*/
          call_req.persid,
          call_req.ref_num as "Ticket Num"
          ,call_req.summary as "Summary",
             call_req.description as "Description"
          /*,call_req.status*/
          ,call_req.active_flag as "Active Flag"
      ,DATEADD(ss, al.last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Last Status Date"
      ,al.description as StatusComments
          ,call_req.open_date
          ,call_req.time_spent_sum as  "Time Spent Sum"
          ,call_req.last_mod_dt      
          ,call_req.close_date
             ,call_req.resolve_date
          /*,call_req.rootcause as  "Root Cause"*/
          ,Rootcause.sym as "Root Cause"
          ,call_req.charge_back_id as "Charge_Back id"
          ,call_req.support_lev as "Support Level"
          /*,call_req.category*/
          /*,call_req.impact*/
          /*,call_req.priority*/
          /*,call_req.urgency*/
          /*,call_req.severity*/
          ,call_req.extern_ref as "External Reference"
      ,ca_resource_department.name as DepartmentName
      ,ca_resource_cost_center.name as CostCenter
      ,ca_location.location_name as LocationName
      ,ca_site.name as SiteName
      
            ,call_req.last_act_id as "Last Action Id"
          /*,call_req.cr_tticket as  "Cr tticket"*/
          /*,call_req.parent as "Parent"*/
          ,Parent.ref_num as "Parent"
          ,Parent.status as "Parent Status"
          /*,call_req.template_name as "Template Name"*/
          ,call_req.sla_violation as "SLA Violation"
          ,call_req.predicted_sla_viol as "Predicted SLA Violation"
          ,call_req.macro_predict_viol as "Macro Predicted Violation"
          /*,call_req.created_via*/
          /*,call_req.call_back_date*/
          ,call_req.call_back_flag as "Call Back Flag"
          ,call_req.event_token as "Event Token"
          ,call_req.sched_token as "Sched Token"
          ,call_req.type as "Ticket Type"
          /*,call_req.string1 as "string1"
          ,call_req.string2 as "string2"
          ,call_req.string3 as "string3"
          ,call_req.string4 as "string4"
          ,call_req.string5 as "string5"
          ,call_req.string6 as "string6"*/
          /*,call_req.problem as "Problem"*/
          ,Problem.ref_num as "Problem"
          ,Problem.status as "Problem Status"
          
          ,call_req.incident_priority as "Incident Priority"
          /*,call_req.change as "Change"*/
          ,Change.chg_ref_num as "Change"
          ,Change.status as "Change Status"
          ,call_req.ticket_avoided as "Ticket Avoided"
          /*,call_req.caused_by_chg as "Caused By Chg"*/
          ,caused_by_chg.chg_ref_num as "Caused By Chg"
          ,caused_by_chg.status as "Caused By Chg Status"
          /*,call_req.outage_start_time*/
          /*,call_req.outage_end_time*/
          ,call_req.external_system_ticket as "External System Ticket"
          ,call_req.incorrectly_assigned as "Incorrectly Assigned"
          ,call_req.major_incident as "Major Incident"
          ,call_req.outage_detail_what as "Outage Detail What"
          ,call_req.outage_detail_who as "Outage Detail Who"
          ,call_req.outage_detail_why as "Outage Detail Why"
          ,call_req.outage_reason_desc as "Outage Reason Desc "
          ,call_req.outage_type as "Outage Type"
          ,call_req.pct_service_restored as "Pct Service Restored"
          ,call_req.remote_control_used as "Remote Control Used"
          /*,call_req.resolution_code as "Resolution Code"*/
          ,res_code.sym as "Resolution Code"  
          /*,call_req.resolution_method as "Resolution Method"*/
          ,res_method.sym as "Resolution Method"
          ,call_req.resolvable_at_lower as "Resolvable At Lower"
          ,call_req.return_to_service as "Return to Service"
          /*,call_req.symptom_code as "Symptom Code"*/
          ,symptom.sym as "Symptom Code"
          ,call_req.target_closed_count as "Target Closed Count"
          ,call_req.target_closed_last as "Target Closed Last"
          ,call_req.target_hold_count as "Target Hold Count"
          ,call_req.target_hold_last as "Target Hold Last"
          ,call_req.target_resolved_count as "Target Resolved Count"
          ,call_req.target_resolved_last as "Target Resolved Last"
          ,call_req.target_start_last as "Target Start Last"
          ,call_req.fcr as "fcr"
          ,call_req.zusmrequestid as "USM Request ID"
          ,call_req.zusmrequestitemid as "USM Request Item ID"
          /*,call_req.zcategory_init as "Initial category"*/
          ,init_cat.sym as "Initial Category",
             /*call_req.heat as "Heat",*/
          
          DATEADD(ss, call_req.open_date +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Open Date",
          DATEADD(ss, call_req.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Last Modification Date",
          DATEADD(ss, call_req.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Close Date",
          DATEADD(ss, call_req.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Resolve Date",
          DATEADD(ss, call_req.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Call_back Date",
          DATEADD(ss, call_req.outage_start_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage Start Time",
          DATEADD(ss, call_req.outage_end_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage End Time",
          
          DATEADD(ss, parent.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Open Date",
          DATEADD(ss, parent.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Last Modification Date",
          DATEADD(ss, parent.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Close Date",
          DATEADD(ss, parent.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Resolve Date",
          DATEADD(ss, parent.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Call_back Date",
          
          DATEADD(ss, problem.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Open Date",
          DATEADD(ss, problem.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Last Modification Date",
          DATEADD(ss, problem.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Close Date",
          DATEADD(ss, problem.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Resolve Date",
          DATEADD(ss, problem.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Call_back Date",
          
          DATEADD(ss, change.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Open Date",
          DATEADD(ss, change.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Last Modification Date",
          DATEADD(ss, change.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Close Date",
          DATEADD(ss, change.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Resolve Date",
          DATEADD(ss, change.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Call_back Date",
          
          /*Time Since opening-- only for open tickets*/
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) END AS "Time Since Opening(Seconds)",
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60 END AS "Time Since Opening(Hours)",
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 END AS "Time Since Opening(Days)",
      case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24/7 END AS "Time Since Opening(Weeks)",
      case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24/30 END AS "Time Since Opening(Months)",
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24/365 END AS "Time Since Opening(Years)",
          
           /*Time Since Last modification -- only for open tickets*/
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt) END AS "Time Since Last Modification(Seconds)",
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60 END AS "Time Since Last Modification(Hours)",
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60/24 END AS "Time Since Last Modification(Days)",
          
          
          /*Time to resolve-- if Ticket is not resolved, time till now*/
          case when ( call_req.resolve_date is not null   and call_req.resolve_date >= call_req.open_date) THEN      
             (call_req.resolve_date - call_req.open_date)else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) end AS "Time To Resolve(Seconds)",    
               
          case when ( call_req.resolve_date is not null AND call_req.open_date is not null  and call_req.resolve_date >= call_req.open_date) THEN      
             (call_req.resolve_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60  end AS "Time To Resolve(Hours)",
             
             case when ( call_req.resolve_date is not null AND call_req.open_date is not null  and call_req.resolve_date >= call_req.open_date) THEN      
             (call_req.resolve_date - call_req.open_date)/60/60/24  else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 end AS "Time To Resolve(Days)",
             
             /*Time to close-- if Ticket is not closed, time till now*/
             case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
             (call_req.close_date - call_req.open_date) else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)  end AS "Time To Close(Seconds)",    
               
          case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
             (call_req.close_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60  end AS "Time To Close(Hours)",
             
             case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
             (call_req.close_date - call_req.open_date)/60/60/24  else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24  end AS "Time To Close (Days)",
             
             /*Resolve to close, if ticket is not closed time from resolve to Now.*/
             case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
             (call_req.close_date - call_req.resolve_date)  WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)   end AS "Resolve To Close(Seconds)", 
               
          case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
             (call_req.close_date - call_req.resolve_date)/60/60 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60   end AS "Resolve To Close(Hours)",
           
             case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
             (call_req.close_date - call_req.resolve_date)/60/60/24 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24   end AS "Resolve To Close(Days)", 
             
            
               
          
                          /*Datediff (hour, call_req.open_date,call_req.resolve_date) as "Time To Resolve (Hours)",      
                          Datediff (day, call_req.resolve_date, call_req.close_date ) as "Resolve To Close (Days)",
                          Datediff (hour, call_req.resolve_date, call_req.close_date) as "Resolve To Close (Hours)",*/
         
         
          
             
           srv_desc.sym as "Service Type",
           sevrty.sym as "Severity",
           urgncy.sym as "Urgency", 
           prob_ctg.sym as "Category",
           
           net_res.resource_name as "Asset Name",
           gen_res.name as "Asset Class",
           resfam.name  as "Asset Family",
           
           service_net_res.resource_name as "Affected Service",
           service_gen_res.name as "Affected Service Class",
           service_resfam.name  as "Affected Service Family",
           ci_enterprise_service.business_impact as "Business Impact", 
           ci_enterprise_service.business_priority as "Business Priority", 
           
           impact.sym as "Impact", 
           
           cr_stat.sym as "Status",
           pri.sym as "Priority",
           interface.sym as "Created Via", 
           
           
           View_Group.last_name as "Group", 
           
           
           /*customer.last_name as "Customer Lastname",
           customer.first_name as "Customer Firstname",
           customer.middle_name as "Customer Middlename", */
           customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ')  as "Customer", 
         --  customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ')  as "Supervisior", 
          sup.last_name + coalesce( ', ' +sup.first_name ,' ')+ coalesce(' ' + sup.middle_name, ' ')  as "supervisior",
           customer_loc.location_name as       "Customer Location", 
           customer_org.org_name        as     "Customer Organization", 
           customer_admin.org_name      as "Customer Admin Organization",
           
           /*assignee.last_name as "Assignee Lastname", 
           assignee.first_name as "Assignee Firstname", 
           assignee.middle_name as "Assignee Middlename",*/
             assignee.last_name + coalesce( ', ' + assignee.first_name , ' ')+ coalesce (' ' + assignee.middle_name,' ') as "Assignee",
           assignee_loc.location_name as       "Assignee Location", 
           assignee_org.org_name        as     "Assignee Organization", 
           assignee_admin.org_name as   "Assignee Admin Organization",
           
           /*log_agent.last_name as "Log Agent Lastname", 
           log_agent.first_name as "Log Agent Firstname", 
           log_agent.middle_name as "Log Agent Middlename",*/
           log_agent.last_name + coalesce( ', ' + log_agent.first_name , ' ')+ coalesce (' ' + log_agent.middle_name,' ') as "Log Agent",          
           log_agent_loc.location_name as      "Log Agent Location",        
           log_agent_org.org_name       as     "Log Agent Organization", 
           log_agent_admin.org_name as  "Log Agent Admin Organization",
           
           /*last_mod_by.last_name as "Analyst Last Modified Lastname", 
           last_mod_by.first_name as "Analyst Last Modified Firstname", 
           last_mod_by.middle_name as "Analyst Last Modified Middlename",*/
           last_mod_by.last_name + coalesce( ', ' + last_mod_by.first_name , ' ')+ coalesce (' ' + last_mod_by.middle_name,' ') as "Analyst Last Modified",
           last_mod_by_loc.location_name as    "Analyst Last Modified Location",    
           last_mod_by_org.org_name     as     "Analyst Last Modified Organization", 
           last_mod_by_admin.org_name as       "Analyst Last Modified Admin Organization",
       CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds') as  [Total Elapsed Time]
    
    FROM (
           (
                  (      
                          (
                                 (
                                        (
                                               (
                                                      (
                                                              (      
                                                                     (
                                                                            (
                                                                                   (
                                                                                           (
                                                                                                  (
                                                                                                         (
                                                                                                                (
                                                                                                                       (
                                                                                                                               (
                                                                                                                                      (
                                                                                                                                             (
                                                                                                                                                    (
                                                                                                                                                            (
                                                                                                                                                                   (
                                                                                                                                                                          (
                                                                                                                                                                                 (
                                                                                                                                                                                        (
                                                                                                                                                                                                (
    (      
           (
                  (
                          (
                                 (
                                        (
                                               (
                                                      (
                                                              (
                                                                     (
                                                                            (
                                                                                   (
                                                                                           (      
                                                                                                                                                                                                
    (  call_req 
    left join RootCause ON call_req.rootcause = rootcause.id)
    left join call_req Problem ON call_req.problem = Problem.persid)
    left join call_req Parent ON call_req.problem = Parent.persid)
    left join Chg Change ON call_req.change = Change.id)
    left join usp_resolution_code res_code ON call_req.resolution_code= res_code.id)
    left join usp_resolution_method res_method ON call_req.resolution_method= res_method.id)
    left join usp_symptom_code symptom ON call_req.symptom_code= symptom.id)
    left join Chg caused_by_chg ON call_req.change = caused_by_chg.id)
    inner join  pri ON call_req.priority = pri.enum) 
    inner join  ca_contact customer ON call_req.customer =customer.contact_uuid) 
    left join ca_resource_department on ca_resource_department.id=customer.department
    left join ca_resource_cost_center on ca_resource_cost_center.id=customer.cost_center
    left join ca_location on ca_location.location_uuid=customer.location_uuid
    left join ca_site on ca_location.site_id=ca_site.id
    left join  ca_contact sup on sup.contact_uuid=customer.supervisor_contact_uuid
    left join 
    (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY call_req_id ORDER BY last_mod_dt DESC) AS Seq
    FROM act_log 
    where (act_log.type like 'ST' or act_log.type like 'INIT' or act_log.type like 'Log' or act_log.type like 'RE') and act_log.description not like '%Status changed%'
    and act_log.description not like '%Workflow task%' and  act_log.description not like '%AHD%' and  act_log.description not like '%create%'
       )al on al.call_req_id= call_req.persid and al.Seq = 1
    
    
    left join Final f on f.call_req_id=call_req.persid
    left join  srv_desc ON call_req.support_lev = srv_desc.code) 
    left join  sevrty ON call_req.severity = sevrty.enum) 
    left join  urgncy ON call_req.urgency = urgncy.enum) 
    left join  impact ON call_req.impact = impact.enum)
    
    left join  ca_owned_resource net_res ON call_req.affected_rc = net_res.own_resource_uuid)
    left join  ca_resource_class gen_res ON net_res.resource_class = gen_res.id) 
    left join  ca_resource_family resfam ON gen_res.family_id = resfam.id)
    
    left join  ca_owned_resource service_net_res ON call_req.affected_service = service_net_res.own_resource_uuid)
    left join  ci_enterprise_service ON call_req.affected_service = ci_enterprise_service.own_resource_uuid)
    left join  ca_resource_class service_gen_res ON service_net_res.resource_class = service_gen_res.id) 
    left join  ca_resource_family service_resfam ON service_gen_res.family_id = service_resfam.id)
    
           
    left join  prob_ctg ON call_req.category = prob_ctg.persid) 
    left join  prob_ctg init_cat ON call_req.zcategory_init = init_cat.persid) 
    
    left join  View_Group ON call_req.group_id = View_Group.contact_uuid) 
    left join  ca_contact assignee ON call_req.assignee = assignee.contact_uuid)
    left join  ca_contact log_agent ON call_req.log_agent = log_agent.contact_uuid)
    left join  ca_contact last_mod_by ON call_req.log_agent = last_mod_by.contact_uuid)
    left join  cr_stat ON call_req.status = cr_stat.code)
    
    left join  Interface ON call_req.created_via = Interface.id)
    
           left join  ca_organization   customer_org ON  customer.organization_uuid = customer_org.organization_uuid) 
           left join  ca_organization   customer_admin ON customer.admin_organization_uuid = customer_admin.organization_uuid)
           left join  ca_location              customer_loc ON customer.location_uuid =     customer_loc.location_uuid) 
           
           
           left join  ca_organization   assignee_org ON  assignee.organization_uuid = assignee_org.organization_uuid) 
           left join  ca_organization   assignee_admin ON assignee.admin_organization_uuid = assignee_admin.organization_uuid)
           left join  ca_location              assignee_loc ON assignee.location_uuid =     assignee_loc.location_uuid) 
           
           left join  ca_organization   log_agent_org ON  log_agent.organization_uuid = log_agent_org.organization_uuid) 
           left join  ca_organization   log_agent_admin ON log_agent.admin_organization_uuid =        log_agent_admin.organization_uuid)
           left join  ca_location              log_agent_loc ON log_agent.location_uuid =    log_agent_loc.location_uuid) 
           
           left join  ca_organization   last_mod_by_org ON  last_mod_by.organization_uuid = last_mod_by_org.organization_uuid) 
           left join  ca_organization   last_mod_by_admin ON last_mod_by.admin_organization_uuid =        last_mod_by_admin.organization_uuid)
           left join  ca_location              last_mod_by_loc ON last_mod_by.location_uuid = last_mod_by_loc.location_uuid) 
           
           where call_req.template_name is null 


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by du00805 Thursday, March 7, 2019 1:01 PM
    Thursday, March 7, 2019 12:50 PM

All replies

  • Can you provide sample data? INSERT INTO?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, March 5, 2019 8:13 AM
    Answerer
  • you can created table with options (

    call_req_id type Status Date description

    )

    And the insert the values provided in the output

    Tuesday, March 5, 2019 8:19 AM
  • You can use a UDF like below for your purpose

    https://visakhm.blogspot.com/2010/03/calculating-business-hours.html

    and tweak it like this to adapt to your scenario

    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME ='BusinessHours' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE='FUNCTION')
    DROP FUNCTION dbo.BusinessHours
    GO
    CREATE FUNCTION dbo.BusinessHours
    (
    @StartDate datetime, --start of period of consideration for calculation of business hours
    @EndDate datetime, --end of period of consideration for calculation of business hours
    @BusinessStart datetime, --start of business hours
    @BusinessEnd datetime --end of business hours
    )
    RETURNS bigint
    AS
    BEGIN
    DECLARE @TotalSeconds bigint
    IF DATEDIFF(dd,0,@StartDate)=DATEDIFF(dd,0,@EndDate)
    BEGIN
     IF @BusinessStart>@BusinessEnd
     BEGIN
      SET @TotalSeconds= -1
     END
     ELSE
    BEGIN
    SELECT @BusinessStart=CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessEnd
    THEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) ELSE @BusinessStart END,
    @BusinessEnd =CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessEnd
    THEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) ELSE @BusinessEnd END
      SET @TotalSeconds= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd OR DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart
          THEN 0
    ELSE DATEDIFF(ss,@BusinessStart,@BusinessEnd)
    END
    END
    END
    ELSE
    BEGIN
     SELECT @StartDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),1))
     WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))
       ELSE @StartDate
       END,
      @EndDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),-1))
     WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),0))
       ELSE @EndDate
       END
      ;With Calendar_CTE (Date,Day,WeekDay)
     AS
     (
     SELECT DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)), CASE WHEN DATEDIFF(dd,0,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))%7  NOT IN (4,5) THEN 1 ELSE 0 END
      UNION ALL
      SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATEDIFF(dd,0,DATEADD(dd,1,Date))%7 NOT IN (4,5) THEN 1 ELSE 0 END
     FROM Calendar_CTE
     WHERE DATEADD(dd,1,Date) <= @EndDate
     )
    
      SELECT @TotalSeconds=CEILING(SUM(
       CASE WHEN DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(ss,@StartDate,@EndDate)
    WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(ss,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
       WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(ss,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
       ELSE DATEDIFF(ss,@BusinessStart,@BusinessEnd)
       END
       ))
     FROM Calendar_CTE c
     LEFT JOIN Holiday h
     ON h.[date]= c.Date
     WHERE WeekDay=1
     AND h.[date] IS NULL
     OPTION (MAXRECURSION 0)
    END
    RETURN @TotalSeconds
    END

    If you dont have Holiday table then you can remove that part

    and use it in code like below

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      where call_req_id = 'cr:410416'
    )
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.CalculateBusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'07:00','18:00') AS BusinessTime
    WHERE type IN ('SLADELAY','SLARESUME')
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id

    to get total business time in your desired format just make it like this

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      where call_req_id = 'cr:410416'
    ),Final
    AS
    (
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.CalculateBusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'07:00','18:00') AS BusinessTime
    WHERE type IN ('SLADELAY','SLARESUME')
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )
    
    SELECT call_req_id,
    CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds')
    FROM Final


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page




    Tuesday, March 5, 2019 8:24 AM
  • you can created table with options (

    call_req_id type Status Date description

    )

    And the insert the values provided in the output

    So what's your expected results? It seems that the sample data above couldn't be in calculation based on your logic.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 5, 2019 8:55 AM
  • Thanks Visakh for the prompt response. I come across the link shared by you  on calculating Business hours 

    https://visakhm.blogspot.com/2010/03/calculating-business-hours.html

    But where in function shared by you pointing the custom condition of Business Days. Usually business days are genric but in my condition it is custom which is like :

    Business Days : Sunday to Thursday

    Non-Business Days : Friday and Saturday

    Tuesday, March 5, 2019 9:01 AM
  • HI Visakh,

    While running the below query I am getting the error  as shown in below screenshot

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      --where call_req_id = 'cr:410416'
    ),Final
    AS
    (
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.CalculateBusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'07:00','18:00') AS BusinessTime
    WHERE type IN ('SLADELAY','SLARESUME')
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )
    
    SELECT call_req_id,
    CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds')
    FROM Final

    Tuesday, March 5, 2019 11:40 AM
  • Thanks Visakh for the prompt response. I come across the link shared by you  on calculating Business hours 

    https://visakhm.blogspot.com/2010/03/calculating-business-hours.html

    But where in function shared by you pointing the custom condition of Business Days. Usually business days are genric but in my condition it is custom which is like :

    Business Days : Sunday to Thursday

    Non-Business Days : Friday and Saturday

    I already posted the modified logic above to account for this (Fri,Sat as weekend) so just use the modified version above instead of one on the link

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, March 5, 2019 11:59 AM
  • HI Visakh,

    While running the below query I am getting the error  as shown in below screenshot

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      --where call_req_id = 'cr:410416'
    ),Final
    AS
    (
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.CalculateBusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'07:00','18:00') AS BusinessTime
    WHERE type IN ('SLADELAY','SLARESUME')
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )
    
    SELECT call_req_id,
    CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds')
    FROM Final

    try like this

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      --where call_req_id = 'cr:410416'
    ),Final
    AS
    (
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.CalculateBusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'07:00','18:00') AS BusinessTime
    FROM CTE
    WHERE type IN ('SLADELAY','SLARESUME')
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )
    
    SELECT call_req_id,
    CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds')
    FROM Final


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by du00805 Wednesday, March 6, 2019 8:37 AM
    Tuesday, March 5, 2019 12:00 PM
  • Thanks Visakh. It is working bingo. 

    But where I can place this is in my exiting query below where I want to show [Vendor Elapsed Time] against Ticket Num where ticket number is coming from table call_req already mentioned in my exiting query. Join of the table will be left join  act_log on act_log.call_req_id= call_req.persid

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      --where call_req_id = 'cr:410416'
    ),Final
    AS
    (

    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.BusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'07:00','18:00') AS BusinessTime
    FROM CTE
    WHERE type IN ('SLADELAY','SLARESUME')
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )

    SELECT call_req_id,
    CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds') as  [Vendor Elapsed Time]
    FROM Final

    ------------

    Exiting Query

    ----------------

    SELECT 

                  /*call_req.id*/

          call_req.persid,

          call_req.ref_num as "Ticket Num"

          ,call_req.summary as "Summary",

             call_req.description as "Description"

          /*,call_req.status*/

          ,call_req.active_flag as "Active Flag"

      ,DATEADD(ss, al.last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Last Status Date"

      ,al.description as StatusComments

          ,call_req.open_date

          ,call_req.time_spent_sum as  "Time Spent Sum"

          ,call_req.last_mod_dt      

          ,call_req.close_date

             ,call_req.resolve_date

          /*,call_req.rootcause as  "Root Cause"*/

          ,Rootcause.sym as "Root Cause"

          ,call_req.charge_back_id as "Charge_Back id"

          ,call_req.support_lev as "Support Level"

          /*,call_req.category*/

          /*,call_req.impact*/

          /*,call_req.priority*/

          /*,call_req.urgency*/

          /*,call_req.severity*/

          ,call_req.extern_ref as "External Reference"

      ,ca_resource_department.name as DepartmentName

      ,ca_resource_cost_center.name as CostCenter

      ,ca_location.location_name as LocationName

      ,ca_site.name as SiteName

      

            ,call_req.last_act_id as "Last Action Id"

          /*,call_req.cr_tticket as  "Cr tticket"*/

          /*,call_req.parent as "Parent"*/

          ,Parent.ref_num as "Parent"

          ,Parent.status as "Parent Status"

          /*,call_req.template_name as "Template Name"*/

          ,call_req.sla_violation as "SLA Violation"

          ,call_req.predicted_sla_viol as "Predicted SLA Violation"

          ,call_req.macro_predict_viol as "Macro Predicted Violation"

          /*,call_req.created_via*/

          /*,call_req.call_back_date*/

          ,call_req.call_back_flag as "Call Back Flag"

          ,call_req.event_token as "Event Token"

          ,call_req.sched_token as "Sched Token"

          ,call_req.type as "Ticket Type"

          /*,call_req.string1 as "string1"

          ,call_req.string2 as "string2"

          ,call_req.string3 as "string3"

          ,call_req.string4 as "string4"

          ,call_req.string5 as "string5"

          ,call_req.string6 as "string6"*/

          /*,call_req.problem as "Problem"*/

          ,Problem.ref_num as "Problem"

          ,Problem.status as "Problem Status"

          

          ,call_req.incident_priority as "Incident Priority"

          /*,call_req.change as "Change"*/

          ,Change.chg_ref_num as "Change"

          ,Change.status as "Change Status"

          ,call_req.ticket_avoided as "Ticket Avoided"

          /*,call_req.caused_by_chg as "Caused By Chg"*/

          ,caused_by_chg.chg_ref_num as "Caused By Chg"

          ,caused_by_chg.status as "Caused By Chg Status"

          /*,call_req.outage_start_time*/

          /*,call_req.outage_end_time*/

          ,call_req.external_system_ticket as "External System Ticket"

          ,call_req.incorrectly_assigned as "Incorrectly Assigned"

          ,call_req.major_incident as "Major Incident"

          ,call_req.outage_detail_what as "Outage Detail What"

          ,call_req.outage_detail_who as "Outage Detail Who"

          ,call_req.outage_detail_why as "Outage Detail Why"

          ,call_req.outage_reason_desc as "Outage Reason Desc "

          ,call_req.outage_type as "Outage Type"

          ,call_req.pct_service_restored as "Pct Service Restored"

          ,call_req.remote_control_used as "Remote Control Used"

          /*,call_req.resolution_code as "Resolution Code"*/

          ,res_code.sym as "Resolution Code"  

          /*,call_req.resolution_method as "Resolution Method"*/

          ,res_method.sym as "Resolution Method"

          ,call_req.resolvable_at_lower as "Resolvable At Lower"

          ,call_req.return_to_service as "Return to Service"

          /*,call_req.symptom_code as "Symptom Code"*/

          ,symptom.sym as "Symptom Code"

          ,call_req.target_closed_count as "Target Closed Count"

          ,call_req.target_closed_last as "Target Closed Last"

          ,call_req.target_hold_count as "Target Hold Count"

          ,call_req.target_hold_last as "Target Hold Last"

          ,call_req.target_resolved_count as "Target Resolved Count"

          ,call_req.target_resolved_last as "Target Resolved Last"

          ,call_req.target_start_last as "Target Start Last"

          ,call_req.fcr as "fcr"

          ,call_req.zusmrequestid as "USM Request ID"

          ,call_req.zusmrequestitemid as "USM Request Item ID"

          /*,call_req.zcategory_init as "Initial category"*/

          ,init_cat.sym as "Initial Category",

             /*call_req.heat as "Heat",*/

          

          DATEADD(ss, call_req.open_date +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Open Date",

          DATEADD(ss, call_req.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Last Modification Date",

          DATEADD(ss, call_req.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Close Date",

          DATEADD(ss, call_req.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Resolve Date",

          DATEADD(ss, call_req.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Call_back Date",

          DATEADD(ss, call_req.outage_start_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage Start Time",

          DATEADD(ss, call_req.outage_end_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage End Time",

          

          DATEADD(ss, parent.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Open Date",

          DATEADD(ss, parent.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Last Modification Date",

          DATEADD(ss, parent.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Close Date",

          DATEADD(ss, parent.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Resolve Date",

          DATEADD(ss, parent.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Call_back Date",

          

          DATEADD(ss, problem.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Open Date",

          DATEADD(ss, problem.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Last Modification Date",

          DATEADD(ss, problem.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Close Date",

          DATEADD(ss, problem.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Resolve Date",

          DATEADD(ss, problem.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Call_back Date",

          

          DATEADD(ss, change.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Open Date",

          DATEADD(ss, change.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Last Modification Date",

          DATEADD(ss, change.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Close Date",

          DATEADD(ss, change.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Resolve Date",

          DATEADD(ss, change.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Call_back Date",

          

          /*Time Since opening-- only for open tickets*/

          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) END AS "Time Since Opening(Seconds)",

          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60 END AS "Time Since Opening(Hours)",

          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 END AS "Time Since Opening(Days)",

          

           /*Time Since Last modification -- only for open tickets*/

          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt) END AS "Time Since Last Modification(Seconds)",

          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60 END AS "Time Since Last Modification(Hours)",

          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60/24 END AS "Time Since Last Modification(Days)",

          

          

          /*Time to resolve-- if Ticket is not resolved, time till now*/

          case when ( call_req.resolve_date is not null   and call_req.resolve_date >= call_req.open_date) THEN      

             (call_req.resolve_date - call_req.open_date)else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) end AS "Time To Resolve(Seconds)",    

               

          case when ( call_req.resolve_date is not null AND call_req.open_date is not null  and call_req.resolve_date >= call_req.open_date) THEN      

             (call_req.resolve_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60  end AS "Time To Resolve(Hours)",

             

             case when ( call_req.resolve_date is not null AND call_req.open_date is not null  and call_req.resolve_date >= call_req.open_date) THEN      

             (call_req.resolve_date - call_req.open_date)/60/60/24  else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 end AS "Time To Resolve(Days)",

             

             /*Time to close-- if Ticket is not closed, time till now*/

             case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      

             (call_req.close_date - call_req.open_date) else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)  end AS "Time To Close(Seconds)",    

               

          case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      

             (call_req.close_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60  end AS "Time To Close(Hours)",

             

             case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      

             (call_req.close_date - call_req.open_date)/60/60/24  else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24  end AS "Time To Close (Days)",

             

             /*Resolve to close, if ticket is not closed time from resolve to Now.*/

             case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      

             (call_req.close_date - call_req.resolve_date)  WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)   end AS "Resolve To Close(Seconds)", 

               

          case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      

             (call_req.close_date - call_req.resolve_date)/60/60 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60   end AS "Resolve To Close(Hours)",

           

             case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      

             (call_req.close_date - call_req.resolve_date)/60/60/24 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24   end AS "Resolve To Close(Days)", 

             

            

               

          

                          /*Datediff (hour, call_req.open_date,call_req.resolve_date) as "Time To Resolve (Hours)",      

                          Datediff (day, call_req.resolve_date, call_req.close_date ) as "Resolve To Close (Days)",

                          Datediff (hour, call_req.resolve_date, call_req.close_date) as "Resolve To Close (Hours)",*/

         

         

          

             

           srv_desc.sym as "Service Type",

           sevrty.sym as "Severity",

           urgncy.sym as "Urgency", 

           prob_ctg.sym as "Category",

           

           net_res.resource_name as "Asset Name",

           gen_res.name as "Asset Class",

           resfam.name  as "Asset Family",

           

           service_net_res.resource_name as "Affected Service",

           service_gen_res.name as "Affected Service Class",

           service_resfam.name  as "Affected Service Family",

           ci_enterprise_service.business_impact as "Business Impact", 

           ci_enterprise_service.business_priority as "Business Priority", 

           

           impact.sym as "Impact", 

           

           cr_stat.sym as "Status",

           pri.sym as "Priority",

           interface.sym as "Created Via", 

           

           

           View_Group.last_name as "Group", 

           

           

           /*customer.last_name as "Customer Lastname",

           customer.first_name as "Customer Firstname",

           customer.middle_name as "Customer Middlename", */

           customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ')  as "Customer", 

      --customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ')  as "Supervisior", 

           customer_loc.location_name as       "Customer Location", 

           customer_org.org_name        as     "Customer Organization", 

           customer_admin.org_name      as "Customer Admin Organization",

           

           /*assignee.last_name as "Assignee Lastname", 

           assignee.first_name as "Assignee Firstname", 

           assignee.middle_name as "Assignee Middlename",*/

           assignee.last_name + coalesce( ', ' + assignee.first_name , ' ')+ coalesce (' ' + assignee.middle_name,' ') as "Assignee",

           assignee_loc.location_name as       "Assignee Location", 

           assignee_org.org_name        as     "Assignee Organization", 

           assignee_admin.org_name as   "Assignee Admin Organization",

           

           /*log_agent.last_name as "Log Agent Lastname", 

           log_agent.first_name as "Log Agent Firstname", 

           log_agent.middle_name as "Log Agent Middlename",*/

           log_agent.last_name + coalesce( ', ' + log_agent.first_name , ' ')+ coalesce (' ' + log_agent.middle_name,' ') as "Log Agent",          

           log_agent_loc.location_name as      "Log Agent Location",        

           log_agent_org.org_name       as     "Log Agent Organization", 

           log_agent_admin.org_name as  "Log Agent Admin Organization",

           

           /*last_mod_by.last_name as "Analyst Last Modified Lastname", 

           last_mod_by.first_name as "Analyst Last Modified Firstname", 

           last_mod_by.middle_name as "Analyst Last Modified Middlename",*/

           last_mod_by.last_name + coalesce( ', ' + last_mod_by.first_name , ' ')+ coalesce (' ' + last_mod_by.middle_name,' ') as "Analyst Last Modified",

           last_mod_by_loc.location_name as    "Analyst Last Modified Location",    

           last_mod_by_org.org_name     as     "Analyst Last Modified Organization", 

           last_mod_by_admin.org_name as       "Analyst Last Modified Admin Organization"

    FROM (

           (

                  (      

                          (

                                 (

                                        (

                                               (

                                                      (

                                                              (      

                                                                     (

                                                                            (

                                                                                   (

                                                                                           (

                                                                                                  (

                                                                                                         (

                                                                                                                (

                                                                                                                       (

                                                                                                                               (

                                                                                                                                      (

                                                                                                                                             (

                                                                                                                                                    (

                                                                                                                                                            (

                                                                                                                                                                   (

                                                                                                                                                                          (

                                                                                                                                                                                 (

                                                                                                                                                                                        (

                                                                                                                                                                                                (

    (      

           (

                  (

                          (

                                 (

                                        (

                                               (

                                                      (

                                                              (

                                                                     (

                                                                            (

                                                                                   (

                                                                                           (      

                                                                                                                                                                                                

    (  call_req 

    left join RootCause ON call_req.rootcause = rootcause.id)

    left join call_req Problem ON call_req.problem = Problem.persid)

    left join call_req Parent ON call_req.problem = Parent.persid)

    left join Chg Change ON call_req.change = Change.id)

    left join usp_resolution_code res_code ON call_req.resolution_code= res_code.id)

    left join usp_resolution_method res_method ON call_req.resolution_method= res_method.id)

    left join usp_symptom_code symptom ON call_req.symptom_code= symptom.id)

    left join Chg caused_by_chg ON call_req.change = caused_by_chg.id)

    inner join  pri ON call_req.priority = pri.enum) 

    inner join  ca_contact customer ON call_req.customer =customer.contact_uuid) 

    left join ca_resource_department on ca_resource_department.id=customer.department

    left join ca_resource_cost_center on ca_resource_cost_center.id=customer.cost_center

    left join ca_location on ca_location.location_uuid=customer.location_uuid

    left join ca_site on ca_location.site_id=ca_site.id

    left join 

    (

    SELECT *,ROW_NUMBER() OVER (PARTITION BY call_req_id ORDER BY last_mod_dt DESC) AS Seq

    FROM act_log 

    where (act_log.type like 'ST' or act_log.type like 'INIT' or act_log.type like 'Log' or act_log.type like 'RE') and act_log.description not like '%Status changed%'

    and act_log.description not like '%Workflow task%' and  act_log.description not like '%AHD%' and  act_log.description not like '%create%'

       )al on al.call_req_id= call_req.persid and al.Seq = 1

    left join  srv_desc ON call_req.support_lev = srv_desc.code) 

    left join  sevrty ON call_req.severity = sevrty.enum) 

    left join  urgncy ON call_req.urgency = urgncy.enum) 

    left join  impact ON call_req.impact = impact.enum)

    left join  ca_owned_resource net_res ON call_req.affected_rc = net_res.own_resource_uuid)

    left join  ca_resource_class gen_res ON net_res.resource_class = gen_res.id

    left join  ca_resource_family resfam ON gen_res.family_id = resfam.id)

    left join  ca_owned_resource service_net_res ON call_req.affected_service = service_net_res.own_resource_uuid)

    left join  ci_enterprise_service ON call_req.affected_service = ci_enterprise_service.own_resource_uuid)

    left join  ca_resource_class service_gen_res ON service_net_res.resource_class = service_gen_res.id

    left join  ca_resource_family service_resfam ON service_gen_res.family_id = service_resfam.id)

           

    left join  prob_ctg ON call_req.category = prob_ctg.persid) 

    left join  prob_ctg init_cat ON call_req.zcategory_init = init_cat.persid) 

    left join  View_Group ON call_req.group_id = View_Group.contact_uuid) 

    left join  ca_contact assignee ON call_req.assignee = assignee.contact_uuid)

    left join  ca_contact log_agent ON call_req.log_agent = log_agent.contact_uuid)

    left join  ca_contact last_mod_by ON call_req.log_agent = last_mod_by.contact_uuid)

    left join  cr_stat ON call_req.status = cr_stat.code)

    left join  Interface ON call_req.created_via = Interface.id)

           left join  ca_organization   customer_org ON  customer.organization_uuid = customer_org.organization_uuid) 

           left join  ca_organization   customer_admin ON customer.admin_organization_uuid = customer_admin.organization_uuid)

           left join  ca_location              customer_loc ON customer.location_uuid =     customer_loc.location_uuid) 

           

           

           left join  ca_organization   assignee_org ON  assignee.organization_uuid = assignee_org.organization_uuid) 

           left join  ca_organization   assignee_admin ON assignee.admin_organization_uuid = assignee_admin.organization_uuid)

           left join  ca_location              assignee_loc ON assignee.location_uuid =     assignee_loc.location_uuid) 

           

           left join  ca_organization   log_agent_org ON  log_agent.organization_uuid = log_agent_org.organization_uuid) 

           left join  ca_organization   log_agent_admin ON log_agent.admin_organization_uuid =        log_agent_admin.organization_uuid)

           left join  ca_location              log_agent_loc ON log_agent.location_uuid =    log_agent_loc.location_uuid) 

           

           left join  ca_organization   last_mod_by_org ON  last_mod_by.organization_uuid = last_mod_by_org.organization_uuid) 

           left join  ca_organization   last_mod_by_admin ON last_mod_by.admin_organization_uuid =        last_mod_by_admin.organization_uuid)

           left join  ca_location              last_mod_by_loc ON last_mod_by.location_uuid = last_mod_by_loc.location_uuid) 

           

           where call_req.template_name is null 

                     --and call_req.ref_num='RE5054'

                      order by al.last_mod_dt

    Tuesday, March 5, 2019 12:29 PM
  • db00805,

    I have tried to solve this question.

    Here's the code, just copy paste and execute.

    SET NOCOUNT ON
    GO
    If Object_ID('tempdb..#act_log','U') Is Not Null
    	Drop Table #act_log
    GO
    CREATE TABLE [dbo].[#act_log]
    (
    	[id] [int] NOT NULL,
    	[persid] [nvarchar](30) NULL,
    	[call_req_id] [nvarchar](30) NULL,
    	[last_mod_dt] [int] NULL,
    	[time_spent] [int] NULL,
    	[time_stamp] [int] NULL,
    	[system_time] [int] NULL,
    	[analyst] [binary](16) NULL,
    	[description] [ntext] NULL,
    	[action_desc] [ntext] NULL,
    	[type] [nvarchar](30) NULL,
    	[knowledge_session] [nvarchar](80) NULL,
    	[knowledge_tool] [nvarchar](12) NULL,
    	[internal] [int] NULL,
    	[tenant] [binary](16) NULL,
    	[rel_ticket_type] [nvarchar](12) NULL,
    	[zClarityLastSync] [int] NULL
    )
    GO
    Insert [#act_log]
    (ID,call_req_id,type,[last_mod_dt],description)
    Values
    (1,'cr:410416','INIT',60,'Create a new request/incident/problem/change/issue'),
    (2,'cr:410416','ST',60,'Status changed from ''Open'' to ''Acknowledged'''),
    (3,'cr:410416','ST',120,'Status changed from ''Acknowledged'' to ''Awaiting Vendor'''),
    (4,'cr:410416','SLADELAY',120,'The Service Type(s) on a ticket were delayed'),
    (5,'cr:410416','ST',240,'Status changed from ''Awaiting Vendor'' to ''In Progress'''),
    (6,'cr:410416','SLARESUME',360,'The Service Type(s) on the ticket were resumed'),
    (7,'cr:410416','ST',1980,'Status changed from ''In Progress'' to ''Awaiting End User Response'),
    (8,'cr:410416','SLADELAY',1980,'The Service Type(s) on a ticket were delayed'),
    (9,'cr:410416','RE',2340,'Status changed from ''Awaiting End User Response'' to ''Resolved'''),
    (10,'cr:410416','SLARESUME',2340,'The Service Type(s) on the ticket were resumed')
    GO
    Select call_req_id, SUM(DiffInDays) DiffInDays, SUM(DiffInHours) DiffInHours, SUM(DiffInMinutes) DiffInMinutes
    From (
    	Select T1.call_req_id, 
    	--T1.type, Left(Convert(Varchar(50),T1.StatusDate,120),16) StatusDateDelay, Convert(Varchar(50),T1.description) descriptionDelay, 
    	--T2.type, Left(Convert(Varchar(50),T2.StatusDate,120),16) StatusDateResume,  Convert(Varchar(50),T2.description) descriptionResume, 
    	datediff(dd,T1.StatusDate,T2.StatusDate) DiffInDays, 
    	datediff(hh,T1.StatusDate,T2.StatusDate) DiffInHours, 
    	datediff(mi,T1.StatusDate,T2.StatusDate) DiffInMinutes
    	From (
    		Select ID, call_req_id, type,
    		DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '20190305') as StatusDate,
    		description 
    		From #act_log T1
    		Where call_req_id = 'cr:410416' And type = 'SLADELAY' 
    		And DATEPART(dw,DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '20190305')) >= 2
    		And DATEPART(dw,DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '20190305')) <= 6
    	) T1
    	Join (
    		Select ID, call_req_id, type,
    		DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '20190305') as StatusDate,
    		description 
    		From #act_log T1
    		Where call_req_id = 'cr:410416' And type = 'SLARESUME'
    		And DATEPART(dw,DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '20190305')) >= 2
    		And DATEPART(dw,DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '20190305')) <= 6
    	) T2
    		On T1.call_req_id = T2.call_req_id
    		And T1.id < T2.id
    	Left Join (
    		Select ID, call_req_id, type,
    		DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '20190305') as StatusDate,
    		description 
    		From #act_log T1
    		Where call_req_id = 'cr:410416' And type = 'SLADELAY'
    		And DATEPART(dw,DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '20190305')) >= 2
    		And DATEPART(dw,DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '20190305')) <= 6
    	) T3
    		On T1.call_req_id = T3.call_req_id
    		And T1.id < T3.id
    		ANd T2.id > T3.id
    	Where T3.id Is Null
    ) T
    Group By call_req_id
    GO
    

    Results:

    call_req_id      DiffInDays  DiffInHours DiffInMinutes              
    ---------------- ----------- ----------- -------------
    cr:410416        0           0           10
    
    

    Hope this is helpful. DO tell me if this gives incorrect results or errors. I will fix it.

    Sharat

    • Proposed as answer by SharatGupta Tuesday, March 5, 2019 1:05 PM
    Tuesday, March 5, 2019 1:04 PM
  • like this I guess

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      --where call_req_id = 'cr:410416'
    ),Final
    AS
    (
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.BusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'07:00','18:00') AS BusinessTime
    FROM CTE
    WHERE type IN ('SLADELAY','SLARESUME')
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )
    
    SELECT call_req_id,
    CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds') as  [Vendor Elapsed Time]
    FROM Final
    
    ------------
    
    Exiting Query
    
    ----------------
    
    SELECT 
    
                  /*call_req.id*/
    
          call_req.persid,
    
          call_req.ref_num as "Ticket Num"
    
          ,call_req.summary as "Summary",
    
             call_req.description as "Description"
    
          /*,call_req.status*/
    
          ,call_req.active_flag as "Active Flag"
    
      ,DATEADD(ss, al.last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Last Status Date"
    
      ,al.description as StatusComments
    
          ,call_req.open_date
    
          ,call_req.time_spent_sum as  "Time Spent Sum"
    
          ,call_req.last_mod_dt      
    
          ,call_req.close_date
    
             ,call_req.resolve_date
    
          /*,call_req.rootcause as  "Root Cause"*/
    
          ,Rootcause.sym as "Root Cause"
    
          ,call_req.charge_back_id as "Charge_Back id"
    
          ,call_req.support_lev as "Support Level"
    
          /*,call_req.category*/
    
          /*,call_req.impact*/
    
          /*,call_req.priority*/
    
          /*,call_req.urgency*/
    
          /*,call_req.severity*/
    
          ,call_req.extern_ref as "External Reference"
    
      ,ca_resource_department.name as DepartmentName
    
      ,ca_resource_cost_center.name as CostCenter
    
      ,ca_location.location_name as LocationName
    
      ,ca_site.name as SiteName
    
      
    
            ,call_req.last_act_id as "Last Action Id"
    
          /*,call_req.cr_tticket as  "Cr tticket"*/
    
          /*,call_req.parent as "Parent"*/
    
          ,Parent.ref_num as "Parent"
    
          ,Parent.status as "Parent Status"
    
          /*,call_req.template_name as "Template Name"*/
    
          ,call_req.sla_violation as "SLA Violation"
    
          ,call_req.predicted_sla_viol as "Predicted SLA Violation"
    
          ,call_req.macro_predict_viol as "Macro Predicted Violation"
    
          /*,call_req.created_via*/
    
          /*,call_req.call_back_date*/
    
          ,call_req.call_back_flag as "Call Back Flag"
    
          ,call_req.event_token as "Event Token"
    
          ,call_req.sched_token as "Sched Token"
    
          ,call_req.type as "Ticket Type"
    
          /*,call_req.string1 as "string1"
    
          ,call_req.string2 as "string2"
    
          ,call_req.string3 as "string3"
    
          ,call_req.string4 as "string4"
    
          ,call_req.string5 as "string5"
    
          ,call_req.string6 as "string6"*/
    
          /*,call_req.problem as "Problem"*/
    
          ,Problem.ref_num as "Problem"
    
          ,Problem.status as "Problem Status"
    
          
    
          ,call_req.incident_priority as "Incident Priority"
    
          /*,call_req.change as "Change"*/
    
          ,Change.chg_ref_num as "Change"
    
          ,Change.status as "Change Status"
    
          ,call_req.ticket_avoided as "Ticket Avoided"
    
          /*,call_req.caused_by_chg as "Caused By Chg"*/
    
          ,caused_by_chg.chg_ref_num as "Caused By Chg"
    
          ,caused_by_chg.status as "Caused By Chg Status"
    
          /*,call_req.outage_start_time*/
    
          /*,call_req.outage_end_time*/
    
          ,call_req.external_system_ticket as "External System Ticket"
    
          ,call_req.incorrectly_assigned as "Incorrectly Assigned"
    
          ,call_req.major_incident as "Major Incident"
    
          ,call_req.outage_detail_what as "Outage Detail What"
    
          ,call_req.outage_detail_who as "Outage Detail Who"
    
          ,call_req.outage_detail_why as "Outage Detail Why"
    
          ,call_req.outage_reason_desc as "Outage Reason Desc "
    
          ,call_req.outage_type as "Outage Type"
    
          ,call_req.pct_service_restored as "Pct Service Restored"
    
          ,call_req.remote_control_used as "Remote Control Used"
    
          /*,call_req.resolution_code as "Resolution Code"*/
    
          ,res_code.sym as "Resolution Code"  
    
          /*,call_req.resolution_method as "Resolution Method"*/
    
          ,res_method.sym as "Resolution Method"
    
          ,call_req.resolvable_at_lower as "Resolvable At Lower"
    
          ,call_req.return_to_service as "Return to Service"
    
          /*,call_req.symptom_code as "Symptom Code"*/
    
          ,symptom.sym as "Symptom Code"
    
          ,call_req.target_closed_count as "Target Closed Count"
    
          ,call_req.target_closed_last as "Target Closed Last"
    
          ,call_req.target_hold_count as "Target Hold Count"
    
          ,call_req.target_hold_last as "Target Hold Last"
    
          ,call_req.target_resolved_count as "Target Resolved Count"
    
          ,call_req.target_resolved_last as "Target Resolved Last"
    
          ,call_req.target_start_last as "Target Start Last"
    
          ,call_req.fcr as "fcr"
    
          ,call_req.zusmrequestid as "USM Request ID"
    
          ,call_req.zusmrequestitemid as "USM Request Item ID"
    
          /*,call_req.zcategory_init as "Initial category"*/
    
          ,init_cat.sym as "Initial Category",
    
             /*call_req.heat as "Heat",*/
    
          
    
          DATEADD(ss, call_req.open_date +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Open Date",
    
          DATEADD(ss, call_req.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Last Modification Date",
    
          DATEADD(ss, call_req.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Close Date",
    
          DATEADD(ss, call_req.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Resolve Date",
    
          DATEADD(ss, call_req.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Call_back Date",
    
          DATEADD(ss, call_req.outage_start_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage Start Time",
    
          DATEADD(ss, call_req.outage_end_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage End Time",
    
          
    
          DATEADD(ss, parent.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Open Date",
    
          DATEADD(ss, parent.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Last Modification Date",
    
          DATEADD(ss, parent.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Close Date",
    
          DATEADD(ss, parent.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Resolve Date",
    
          DATEADD(ss, parent.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Call_back Date",
    
          
    
          DATEADD(ss, problem.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Open Date",
    
          DATEADD(ss, problem.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Last Modification Date",
    
          DATEADD(ss, problem.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Close Date",
    
          DATEADD(ss, problem.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Resolve Date",
    
          DATEADD(ss, problem.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Call_back Date",
    
          
    
          DATEADD(ss, change.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Open Date",
    
          DATEADD(ss, change.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Last Modification Date",
    
          DATEADD(ss, change.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Close Date",
    
          DATEADD(ss, change.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Resolve Date",
    
          DATEADD(ss, change.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Call_back Date",
    
          
    
          /*Time Since opening-- only for open tickets*/
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) END AS "Time Since Opening(Seconds)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60 END AS "Time Since Opening(Hours)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 END AS "Time Since Opening(Days)",
    
          
    
           /*Time Since Last modification -- only for open tickets*/
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt) END AS "Time Since Last Modification(Seconds)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60 END AS "Time Since Last Modification(Hours)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60/24 END AS "Time Since Last Modification(Days)",
    
          
    
          
    
          /*Time to resolve-- if Ticket is not resolved, time till now*/
    
          case when ( call_req.resolve_date is not null   and call_req.resolve_date >= call_req.open_date) THEN      
    
             (call_req.resolve_date - call_req.open_date)else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) end AS "Time To Resolve(Seconds)",    
    
               
    
          case when ( call_req.resolve_date is not null AND call_req.open_date is not null  and call_req.resolve_date >= call_req.open_date) THEN      
    
             (call_req.resolve_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60  end AS "Time To Resolve(Hours)",
    
             
    
             case when ( call_req.resolve_date is not null AND call_req.open_date is not null  and call_req.resolve_date >= call_req.open_date) THEN      
    
             (call_req.resolve_date - call_req.open_date)/60/60/24  else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 end AS "Time To Resolve(Days)",
    
             
    
             /*Time to close-- if Ticket is not closed, time till now*/
    
             case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
    
             (call_req.close_date - call_req.open_date) else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)  end AS "Time To Close(Seconds)",    
    
               
    
          case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
    
             (call_req.close_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60  end AS "Time To Close(Hours)",
    
             
    
             case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
    
             (call_req.close_date - call_req.open_date)/60/60/24  else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24  end AS "Time To Close (Days)",
    
             
    
             /*Resolve to close, if ticket is not closed time from resolve to Now.*/
    
             case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
    
             (call_req.close_date - call_req.resolve_date)  WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)   end AS "Resolve To Close(Seconds)", 
    
               
    
          case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
    
             (call_req.close_date - call_req.resolve_date)/60/60 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60   end AS "Resolve To Close(Hours)",
    
           
    
             case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
    
             (call_req.close_date - call_req.resolve_date)/60/60/24 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24   end AS "Resolve To Close(Days)", 
    
             
    
            
    
               
    
          
    
                          /*Datediff (hour, call_req.open_date,call_req.resolve_date) as "Time To Resolve (Hours)",      
    
                          Datediff (day, call_req.resolve_date, call_req.close_date ) as "Resolve To Close (Days)",
    
                          Datediff (hour, call_req.resolve_date, call_req.close_date) as "Resolve To Close (Hours)",*/
    
         
    
         
    
          
    
             
    
           srv_desc.sym as "Service Type",
    
           sevrty.sym as "Severity",
    
           urgncy.sym as "Urgency", 
    
           prob_ctg.sym as "Category",
    
           
    
           net_res.resource_name as "Asset Name",
    
           gen_res.name as "Asset Class",
    
           resfam.name  as "Asset Family",
    
           
    
           service_net_res.resource_name as "Affected Service",
    
           service_gen_res.name as "Affected Service Class",
    
           service_resfam.name  as "Affected Service Family",
    
           ci_enterprise_service.business_impact as "Business Impact", 
    
           ci_enterprise_service.business_priority as "Business Priority", 
    
           
    
           impact.sym as "Impact", 
    
           
    
           cr_stat.sym as "Status",
    
           pri.sym as "Priority",
    
           interface.sym as "Created Via", 
    
           
    
           
    
           View_Group.last_name as "Group", 
    
           
    
           
    
           /*customer.last_name as "Customer Lastname",
    
           customer.first_name as "Customer Firstname",
    
           customer.middle_name as "Customer Middlename", */
    
           customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ')  as "Customer", 
    
      --customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ')  as "Supervisior", 
    
           customer_loc.location_name as       "Customer Location", 
    
           customer_org.org_name        as     "Customer Organization", 
    
           customer_admin.org_name      as "Customer Admin Organization",
    
           
    
           /*assignee.last_name as "Assignee Lastname", 
    
           assignee.first_name as "Assignee Firstname", 
    
           assignee.middle_name as "Assignee Middlename",*/
    
           assignee.last_name + coalesce( ', ' + assignee.first_name , ' ')+ coalesce (' ' + assignee.middle_name,' ') as "Assignee",
    
           assignee_loc.location_name as       "Assignee Location", 
    
           assignee_org.org_name        as     "Assignee Organization", 
    
           assignee_admin.org_name as   "Assignee Admin Organization",
    
           
    
           /*log_agent.last_name as "Log Agent Lastname", 
    
           log_agent.first_name as "Log Agent Firstname", 
    
           log_agent.middle_name as "Log Agent Middlename",*/
    
           log_agent.last_name + coalesce( ', ' + log_agent.first_name , ' ')+ coalesce (' ' + log_agent.middle_name,' ') as "Log Agent",          
    
           log_agent_loc.location_name as      "Log Agent Location",        
    
           log_agent_org.org_name       as     "Log Agent Organization", 
    
           log_agent_admin.org_name as  "Log Agent Admin Organization",
    
           
    
           /*last_mod_by.last_name as "Analyst Last Modified Lastname", 
    
           last_mod_by.first_name as "Analyst Last Modified Firstname", 
    
           last_mod_by.middle_name as "Analyst Last Modified Middlename",*/
    
           last_mod_by.last_name + coalesce( ', ' + last_mod_by.first_name , ' ')+ coalesce (' ' + last_mod_by.middle_name,' ') as "Analyst Last Modified",
    
           last_mod_by_loc.location_name as    "Analyst Last Modified Location",    
    
           last_mod_by_org.org_name     as     "Analyst Last Modified Organization", 
    
           last_mod_by_admin.org_name as       "Analyst Last Modified Admin Organization",
    	   f.[Vendor Elapsed Time]
    
    FROM (
    
           (
    
                  (      
    
                          (
    
                                 (
    
                                        (
    
                                               (
    
                                                      (
    
                                                              (      
    
                                                                     (
    
                                                                            (
    
                                                                                   (
    
                                                                                           (
    
                                                                                                  (
    
                                                                                                         (
    
                                                                                                                (
    
                                                                                                                       (
    
                                                                                                                               (
    
                                                                                                                                      (
    
                                                                                                                                             (
    
                                                                                                                                                    (
    
                                                                                                                                                            (
    
                                                                                                                                                                   (
    
                                                                                                                                                                          (
    
                                                                                                                                                                                 (
    
                                                                                                                                                                                        (
    
                                                                                                                                                                                                (
    
    (      
    
           (
    
                  (
    
                          (
    
                                 (
    
                                        (
    
                                               (
    
                                                      (
    
                                                              (
    
                                                                     (
    
                                                                            (
    
                                                                                   (
    
                                                                                           (      
    
                                                                                                                                                                                                
    
    (  call_req 
    
    left join RootCause ON call_req.rootcause = rootcause.id)
    
    left join call_req Problem ON call_req.problem = Problem.persid)
    
    left join call_req Parent ON call_req.problem = Parent.persid)
    
    left join Chg Change ON call_req.change = Change.id)
    
    left join usp_resolution_code res_code ON call_req.resolution_code= res_code.id)
    
    left join usp_resolution_method res_method ON call_req.resolution_method= res_method.id)
    
    left join usp_symptom_code symptom ON call_req.symptom_code= symptom.id)
    
    left join Chg caused_by_chg ON call_req.change = caused_by_chg.id)
    
    inner join  pri ON call_req.priority = pri.enum) 
    
    inner join  ca_contact customer ON call_req.customer =customer.contact_uuid) 
    
    left join ca_resource_department on ca_resource_department.id=customer.department
    
    left join ca_resource_cost_center on ca_resource_cost_center.id=customer.cost_center
    
    left join ca_location on ca_location.location_uuid=customer.location_uuid
    
    left join ca_site on ca_location.site_id=ca_site.id
    
    left join 
    
    (
    
    SELECT *,ROW_NUMBER() OVER (PARTITION BY call_req_id ORDER BY last_mod_dt DESC) AS Seq
    
    FROM act_log 
    
    where (act_log.type like 'ST' or act_log.type like 'INIT' or act_log.type like 'Log' or act_log.type like 'RE') and act_log.description not like '%Status changed%'
    
    and act_log.description not like '%Workflow task%' and  act_log.description not like '%AHD%' and  act_log.description not like '%create%'
    
       )al on al.call_req_id= call_req.persid and al.Seq = 1
    
    left join Final f
    on f.call_req_id = call_req.persid
    
    left join  srv_desc ON call_req.support_lev = srv_desc.code) 
    
    left join  sevrty ON call_req.severity = sevrty.enum) 
    
    left join  urgncy ON call_req.urgency = urgncy.enum) 
    
    left join  impact ON call_req.impact = impact.enum)
    
    left join  ca_owned_resource net_res ON call_req.affected_rc = net_res.own_resource_uuid)
    
    left join  ca_resource_class gen_res ON net_res.resource_class = gen_res.id) 
    
    left join  ca_resource_family resfam ON gen_res.family_id = resfam.id)
    
    left join  ca_owned_resource service_net_res ON call_req.affected_service = service_net_res.own_resource_uuid)
    
    left join  ci_enterprise_service ON call_req.affected_service = ci_enterprise_service.own_resource_uuid)
    
    left join  ca_resource_class service_gen_res ON service_net_res.resource_class = service_gen_res.id) 
    
    left join  ca_resource_family service_resfam ON service_gen_res.family_id = service_resfam.id)
    
           
    
    left join  prob_ctg ON call_req.category = prob_ctg.persid) 
    
    left join  prob_ctg init_cat ON call_req.zcategory_init = init_cat.persid) 
    
    left join  View_Group ON call_req.group_id = View_Group.contact_uuid) 
    
    left join  ca_contact assignee ON call_req.assignee = assignee.contact_uuid)
    
    left join  ca_contact log_agent ON call_req.log_agent = log_agent.contact_uuid)
    
    left join  ca_contact last_mod_by ON call_req.log_agent = last_mod_by.contact_uuid)
    
    left join  cr_stat ON call_req.status = cr_stat.code)
    
    left join  Interface ON call_req.created_via = Interface.id)
    
           left join  ca_organization   customer_org ON  customer.organization_uuid = customer_org.organization_uuid) 
    
           left join  ca_organization   customer_admin ON customer.admin_organization_uuid = customer_admin.organization_uuid)
    
           left join  ca_location              customer_loc ON customer.location_uuid =     customer_loc.location_uuid) 
    
           
    
           
    
           left join  ca_organization   assignee_org ON  assignee.organization_uuid = assignee_org.organization_uuid) 
    
           left join  ca_organization   assignee_admin ON assignee.admin_organization_uuid = assignee_admin.organization_uuid)
    
           left join  ca_location              assignee_loc ON assignee.location_uuid =     assignee_loc.location_uuid) 
    
           
    
           left join  ca_organization   log_agent_org ON  log_agent.organization_uuid = log_agent_org.organization_uuid) 
    
           left join  ca_organization   log_agent_admin ON log_agent.admin_organization_uuid =        log_agent_admin.organization_uuid)
    
           left join  ca_location              log_agent_loc ON log_agent.location_uuid =    log_agent_loc.location_uuid) 
    
           
    
           left join  ca_organization   last_mod_by_org ON  last_mod_by.organization_uuid = last_mod_by_org.organization_uuid) 
    
           left join  ca_organization   last_mod_by_admin ON last_mod_by.admin_organization_uuid =        last_mod_by_admin.organization_uuid)
    
           left join  ca_location              last_mod_by_loc ON last_mod_by.location_uuid = last_mod_by_loc.location_uuid) 
    
           
    
           where call_req.template_name is null 
    
                     --and call_req.ref_num='RE5054'
    
                      order by al.last_mod_dt


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, March 5, 2019 1:17 PM
  • Thanks

    • Edited by du00805 Wednesday, March 6, 2019 6:16 AM
    Tuesday, March 5, 2019 1:32 PM
  • HI Visakh,

    Do let me know which step I am missing on the above query shared by me

    Tuesday, March 5, 2019 2:07 PM
  • Hi Visakh,

    can you let me know about how to resolve the error of "Invalid Object Name Final" Or I have to merge the query in other way...

    Tuesday, March 5, 2019 4:11 PM
  • Hi Visakh,

    Thanks for the above query.

    I have another criteria where want to calculate the time difference only for "Awaiting Vendor" status to Updated Status. Where "Awaiting Vendor" status is storing in string format under description column. And next Updated status is also store in string format under description format. As An Example below : 

    Status Date type description call_req_id
    3/6/2019 6:53 INIT Create a new request/incident/problem/change/issue cr:416284
    3/6/2019 6:54 ADDITPAMWF New CA IT PAM Workflow Process attached for category 'Hardware' cr:416284
    3/6/2019 6:54 ST_ATTACH Attached/Updated service type into request/incident/problem/change/issue. cr:416284
    3/6/2019 6:54 HEAT_CHANGED Heat has changed from '' to 'Low' cr:416284
    3/6/2019 6:54 NF Manual notification
    3/6/2019 6:54 EVT AHD63034:event 'priority 3 cr resolution initial' triggered by condition 'Req. Status = Open'. cr:416284
    3/6/2019 6:54 ST Status changed from 'Open' to 'Acknowledged' cr:416284
    3/6/2019 6:54 ST Status changed from 'Acknowledged' to 'Awaiting Vendor' cr:416284
    3/6/2019 6:54 SLADELAY The Service Type(s) on a ticket were delayed cr:416284
    3/6/2019 6:59 ST Status changed from 'Awaiting Vendor' to 'In Progress' cr:416284
    3/6/2019 6:59 SLARESUME The Service Type(s) on the ticket were resumed cr:416284
    3/6/2019 7:05 ST Status changed from 'In Progress' to 'Awaiting Vendor' cr:416284
    3/6/2019 7:05 SLADELAY The Service Type(s) on a ticket were delayed cr:416284
    3/6/2019 7:05 HEAT_CHANGED Heat has changed from 'Low' to 'Medium' cr:416284
    3/6/2019 7:12 RE Status changed from 'Awaiting Vendor' to 'Resolved' cr:416284
    3/6/2019 7:12 SLARESUME The Service Type(s) on the ticket were resumed cr:416284
    3/6/2019 7:12 HEAT_CHANGED Heat has changed from 'Medium' to 'Low' cr:416284

    In the above example under description format need to pick difference when :

    from 'Awaiting Vendor' - from 'Awaiting Vendor'

    and if it is repeating multiple times then calculate the sum based on Business hours and business days set earlier. 

    Looking forward to your response. 

    Wednesday, March 6, 2019 6:33 AM
  • Thanks Sharat
    Wednesday, March 6, 2019 9:03 AM
  • HI Visakh,

    I was trying this but giving me error Invalid Object name 'Final'. Can you advise on this.

    Wednesday, March 6, 2019 10:40 AM
  • HI Visakh,

    I was trying this but giving me error Invalid Object name 'Final'. Can you advise on this.

    had some unwanted bits in between

    please try like below and see

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      --where call_req_id = 'cr:410416'
    ),Final
    AS
    (
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.BusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'07:00','18:00') AS BusinessTime
    FROM CTE
    WHERE type IN ('SLADELAY','SLARESUME')
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )
    
    SELECT 
    
                  /*call_req.id*/
    
          call_req.persid,
    
          call_req.ref_num as "Ticket Num"
    
          ,call_req.summary as "Summary",
    
             call_req.description as "Description"
    
          /*,call_req.status*/
    
          ,call_req.active_flag as "Active Flag"
    
      ,DATEADD(ss, al.last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Last Status Date"
    
      ,al.description as StatusComments
    
          ,call_req.open_date
    
          ,call_req.time_spent_sum as  "Time Spent Sum"
    
          ,call_req.last_mod_dt      
    
          ,call_req.close_date
    
             ,call_req.resolve_date
    
          /*,call_req.rootcause as  "Root Cause"*/
    
          ,Rootcause.sym as "Root Cause"
    
          ,call_req.charge_back_id as "Charge_Back id"
    
          ,call_req.support_lev as "Support Level"
    
          /*,call_req.category*/
    
          /*,call_req.impact*/
    
          /*,call_req.priority*/
    
          /*,call_req.urgency*/
    
          /*,call_req.severity*/
    
          ,call_req.extern_ref as "External Reference"
    
      ,ca_resource_department.name as DepartmentName
    
      ,ca_resource_cost_center.name as CostCenter
    
      ,ca_location.location_name as LocationName
    
      ,ca_site.name as SiteName
    
      
    
            ,call_req.last_act_id as "Last Action Id"
    
          /*,call_req.cr_tticket as  "Cr tticket"*/
    
          /*,call_req.parent as "Parent"*/
    
          ,Parent.ref_num as "Parent"
    
          ,Parent.status as "Parent Status"
    
          /*,call_req.template_name as "Template Name"*/
    
          ,call_req.sla_violation as "SLA Violation"
    
          ,call_req.predicted_sla_viol as "Predicted SLA Violation"
    
          ,call_req.macro_predict_viol as "Macro Predicted Violation"
    
          /*,call_req.created_via*/
    
          /*,call_req.call_back_date*/
    
          ,call_req.call_back_flag as "Call Back Flag"
    
          ,call_req.event_token as "Event Token"
    
          ,call_req.sched_token as "Sched Token"
    
          ,call_req.type as "Ticket Type"
    
          /*,call_req.string1 as "string1"
    
          ,call_req.string2 as "string2"
    
          ,call_req.string3 as "string3"
    
          ,call_req.string4 as "string4"
    
          ,call_req.string5 as "string5"
    
          ,call_req.string6 as "string6"*/
    
          /*,call_req.problem as "Problem"*/
    
          ,Problem.ref_num as "Problem"
    
          ,Problem.status as "Problem Status"
    
          
    
          ,call_req.incident_priority as "Incident Priority"
    
          /*,call_req.change as "Change"*/
    
          ,Change.chg_ref_num as "Change"
    
          ,Change.status as "Change Status"
    
          ,call_req.ticket_avoided as "Ticket Avoided"
    
          /*,call_req.caused_by_chg as "Caused By Chg"*/
    
          ,caused_by_chg.chg_ref_num as "Caused By Chg"
    
          ,caused_by_chg.status as "Caused By Chg Status"
    
          /*,call_req.outage_start_time*/
    
          /*,call_req.outage_end_time*/
    
          ,call_req.external_system_ticket as "External System Ticket"
    
          ,call_req.incorrectly_assigned as "Incorrectly Assigned"
    
          ,call_req.major_incident as "Major Incident"
    
          ,call_req.outage_detail_what as "Outage Detail What"
    
          ,call_req.outage_detail_who as "Outage Detail Who"
    
          ,call_req.outage_detail_why as "Outage Detail Why"
    
          ,call_req.outage_reason_desc as "Outage Reason Desc "
    
          ,call_req.outage_type as "Outage Type"
    
          ,call_req.pct_service_restored as "Pct Service Restored"
    
          ,call_req.remote_control_used as "Remote Control Used"
    
          /*,call_req.resolution_code as "Resolution Code"*/
    
          ,res_code.sym as "Resolution Code"  
    
          /*,call_req.resolution_method as "Resolution Method"*/
    
          ,res_method.sym as "Resolution Method"
    
          ,call_req.resolvable_at_lower as "Resolvable At Lower"
    
          ,call_req.return_to_service as "Return to Service"
    
          /*,call_req.symptom_code as "Symptom Code"*/
    
          ,symptom.sym as "Symptom Code"
    
          ,call_req.target_closed_count as "Target Closed Count"
    
          ,call_req.target_closed_last as "Target Closed Last"
    
          ,call_req.target_hold_count as "Target Hold Count"
    
          ,call_req.target_hold_last as "Target Hold Last"
    
          ,call_req.target_resolved_count as "Target Resolved Count"
    
          ,call_req.target_resolved_last as "Target Resolved Last"
    
          ,call_req.target_start_last as "Target Start Last"
    
          ,call_req.fcr as "fcr"
    
          ,call_req.zusmrequestid as "USM Request ID"
    
          ,call_req.zusmrequestitemid as "USM Request Item ID"
    
          /*,call_req.zcategory_init as "Initial category"*/
    
          ,init_cat.sym as "Initial Category",
    
             /*call_req.heat as "Heat",*/
    
          
    
          DATEADD(ss, call_req.open_date +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Open Date",
    
          DATEADD(ss, call_req.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Last Modification Date",
    
          DATEADD(ss, call_req.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Close Date",
    
          DATEADD(ss, call_req.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Resolve Date",
    
          DATEADD(ss, call_req.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Call_back Date",
    
          DATEADD(ss, call_req.outage_start_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage Start Time",
    
          DATEADD(ss, call_req.outage_end_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage End Time",
    
          
    
          DATEADD(ss, parent.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Open Date",
    
          DATEADD(ss, parent.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Last Modification Date",
    
          DATEADD(ss, parent.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Close Date",
    
          DATEADD(ss, parent.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Resolve Date",
    
          DATEADD(ss, parent.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Call_back Date",
    
          
    
          DATEADD(ss, problem.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Open Date",
    
          DATEADD(ss, problem.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Last Modification Date",
    
          DATEADD(ss, problem.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Close Date",
    
          DATEADD(ss, problem.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Resolve Date",
    
          DATEADD(ss, problem.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Call_back Date",
    
          
    
          DATEADD(ss, change.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Open Date",
    
          DATEADD(ss, change.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Last Modification Date",
    
          DATEADD(ss, change.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Close Date",
    
          DATEADD(ss, change.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Resolve Date",
    
          DATEADD(ss, change.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Call_back Date",
    
          
    
          /*Time Since opening-- only for open tickets*/
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) END AS "Time Since Opening(Seconds)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60 END AS "Time Since Opening(Hours)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 END AS "Time Since Opening(Days)",
    
          
    
           /*Time Since Last modification -- only for open tickets*/
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt) END AS "Time Since Last Modification(Seconds)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60 END AS "Time Since Last Modification(Hours)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60/24 END AS "Time Since Last Modification(Days)",
    
          
    
          
    
          /*Time to resolve-- if Ticket is not resolved, time till now*/
    
          case when ( call_req.resolve_date is not null   and call_req.resolve_date >= call_req.open_date) THEN      
    
             (call_req.resolve_date - call_req.open_date)else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) end AS "Time To Resolve(Seconds)",    
    
               
    
          case when ( call_req.resolve_date is not null AND call_req.open_date is not null  and call_req.resolve_date >= call_req.open_date) THEN      
    
             (call_req.resolve_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60  end AS "Time To Resolve(Hours)",
    
             
    
             case when ( call_req.resolve_date is not null AND call_req.open_date is not null  and call_req.resolve_date >= call_req.open_date) THEN      
    
             (call_req.resolve_date - call_req.open_date)/60/60/24  else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 end AS "Time To Resolve(Days)",
    
             
    
             /*Time to close-- if Ticket is not closed, time till now*/
    
             case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
    
             (call_req.close_date - call_req.open_date) else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)  end AS "Time To Close(Seconds)",    
    
               
    
          case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
    
             (call_req.close_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60  end AS "Time To Close(Hours)",
    
             
    
             case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
    
             (call_req.close_date - call_req.open_date)/60/60/24  else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24  end AS "Time To Close (Days)",
    
             
    
             /*Resolve to close, if ticket is not closed time from resolve to Now.*/
    
             case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
    
             (call_req.close_date - call_req.resolve_date)  WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)   end AS "Resolve To Close(Seconds)", 
    
               
    
          case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
    
             (call_req.close_date - call_req.resolve_date)/60/60 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60   end AS "Resolve To Close(Hours)",
    
           
    
             case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
    
             (call_req.close_date - call_req.resolve_date)/60/60/24 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24   end AS "Resolve To Close(Days)", 
    
             
    
            
    
               
    
          
    
                          /*Datediff (hour, call_req.open_date,call_req.resolve_date) as "Time To Resolve (Hours)",      
    
                          Datediff (day, call_req.resolve_date, call_req.close_date ) as "Resolve To Close (Days)",
    
                          Datediff (hour, call_req.resolve_date, call_req.close_date) as "Resolve To Close (Hours)",*/
    
         
    
         
    
          
    
             
    
           srv_desc.sym as "Service Type",
    
           sevrty.sym as "Severity",
    
           urgncy.sym as "Urgency", 
    
           prob_ctg.sym as "Category",
    
           
    
           net_res.resource_name as "Asset Name",
    
           gen_res.name as "Asset Class",
    
           resfam.name  as "Asset Family",
    
           
    
           service_net_res.resource_name as "Affected Service",
    
           service_gen_res.name as "Affected Service Class",
    
           service_resfam.name  as "Affected Service Family",
    
           ci_enterprise_service.business_impact as "Business Impact", 
    
           ci_enterprise_service.business_priority as "Business Priority", 
    
           
    
           impact.sym as "Impact", 
    
           
    
           cr_stat.sym as "Status",
    
           pri.sym as "Priority",
    
           interface.sym as "Created Via", 
    
           
    
           
    
           View_Group.last_name as "Group", 
    
           
    
           
    
           /*customer.last_name as "Customer Lastname",
    
           customer.first_name as "Customer Firstname",
    
           customer.middle_name as "Customer Middlename", */
    
           customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ')  as "Customer", 
    
      --customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ')  as "Supervisior", 
    
           customer_loc.location_name as       "Customer Location", 
    
           customer_org.org_name        as     "Customer Organization", 
    
           customer_admin.org_name      as "Customer Admin Organization",
    
           
    
           /*assignee.last_name as "Assignee Lastname", 
    
           assignee.first_name as "Assignee Firstname", 
    
           assignee.middle_name as "Assignee Middlename",*/
    
           assignee.last_name + coalesce( ', ' + assignee.first_name , ' ')+ coalesce (' ' + assignee.middle_name,' ') as "Assignee",
    
           assignee_loc.location_name as       "Assignee Location", 
    
           assignee_org.org_name        as     "Assignee Organization", 
    
           assignee_admin.org_name as   "Assignee Admin Organization",
    
           
    
           /*log_agent.last_name as "Log Agent Lastname", 
    
           log_agent.first_name as "Log Agent Firstname", 
    
           log_agent.middle_name as "Log Agent Middlename",*/
    
           log_agent.last_name + coalesce( ', ' + log_agent.first_name , ' ')+ coalesce (' ' + log_agent.middle_name,' ') as "Log Agent",          
    
           log_agent_loc.location_name as      "Log Agent Location",        
    
           log_agent_org.org_name       as     "Log Agent Organization", 
    
           log_agent_admin.org_name as  "Log Agent Admin Organization",
    
           
    
           /*last_mod_by.last_name as "Analyst Last Modified Lastname", 
    
           last_mod_by.first_name as "Analyst Last Modified Firstname", 
    
           last_mod_by.middle_name as "Analyst Last Modified Middlename",*/
    
           last_mod_by.last_name + coalesce( ', ' + last_mod_by.first_name , ' ')+ coalesce (' ' + last_mod_by.middle_name,' ') as "Analyst Last Modified",
    
           last_mod_by_loc.location_name as    "Analyst Last Modified Location",    
    
           last_mod_by_org.org_name     as     "Analyst Last Modified Organization", 
    
           last_mod_by_admin.org_name as       "Analyst Last Modified Admin Organization",
    	   f.[Vendor Elapsed Time]
    
    FROM (
    
           (
    
                  (      
    
                          (
    
                                 (
    
                                        (
    
                                               (
    
                                                      (
    
                                                              (      
    
                                                                     (
    
                                                                            (
    
                                                                                   (
    
                                                                                           (
    
                                                                                                  (
    
                                                                                                         (
    
                                                                                                                (
    
                                                                                                                       (
    
                                                                                                                               (
    
                                                                                                                                      (
    
                                                                                                                                             (
    
                                                                                                                                                    (
    
                                                                                                                                                            (
    
                                                                                                                                                                   (
    
                                                                                                                                                                          (
    
                                                                                                                                                                                 (
    
                                                                                                                                                                                        (
    
                                                                                                                                                                                                (
    
    (      
    
           (
    
                  (
    
                          (
    
                                 (
    
                                        (
    
                                               (
    
                                                      (
    
                                                              (
    
                                                                     (
    
                                                                            (
    
                                                                                   (
    
                                                                                           (      
    
                                                                                                                                                                                                
    
    (  call_req 
    
    left join RootCause ON call_req.rootcause = rootcause.id)
    
    left join call_req Problem ON call_req.problem = Problem.persid)
    
    left join call_req Parent ON call_req.problem = Parent.persid)
    
    left join Chg Change ON call_req.change = Change.id)
    
    left join usp_resolution_code res_code ON call_req.resolution_code= res_code.id)
    
    left join usp_resolution_method res_method ON call_req.resolution_method= res_method.id)
    
    left join usp_symptom_code symptom ON call_req.symptom_code= symptom.id)
    
    left join Chg caused_by_chg ON call_req.change = caused_by_chg.id)
    
    inner join  pri ON call_req.priority = pri.enum) 
    
    inner join  ca_contact customer ON call_req.customer =customer.contact_uuid) 
    
    left join ca_resource_department on ca_resource_department.id=customer.department
    
    left join ca_resource_cost_center on ca_resource_cost_center.id=customer.cost_center
    
    left join ca_location on ca_location.location_uuid=customer.location_uuid
    
    left join ca_site on ca_location.site_id=ca_site.id
    
    left join 
    
    (
    
    SELECT *,ROW_NUMBER() OVER (PARTITION BY call_req_id ORDER BY last_mod_dt DESC) AS Seq
    
    FROM act_log 
    
    where (act_log.type like 'ST' or act_log.type like 'INIT' or act_log.type like 'Log' or act_log.type like 'RE') and act_log.description not like '%Status changed%'
    
    and act_log.description not like '%Workflow task%' and  act_log.description not like '%AHD%' and  act_log.description not like '%create%'
    
       )al on al.call_req_id= call_req.persid and al.Seq = 1
    
    left join Final f
    on f.call_req_id = call_req.persid
    
    left join  srv_desc ON call_req.support_lev = srv_desc.code) 
    
    left join  sevrty ON call_req.severity = sevrty.enum) 
    
    left join  urgncy ON call_req.urgency = urgncy.enum) 
    
    left join  impact ON call_req.impact = impact.enum)
    
    left join  ca_owned_resource net_res ON call_req.affected_rc = net_res.own_resource_uuid)
    
    left join  ca_resource_class gen_res ON net_res.resource_class = gen_res.id) 
    
    left join  ca_resource_family resfam ON gen_res.family_id = resfam.id)
    
    left join  ca_owned_resource service_net_res ON call_req.affected_service = service_net_res.own_resource_uuid)
    
    left join  ci_enterprise_service ON call_req.affected_service = ci_enterprise_service.own_resource_uuid)
    
    left join  ca_resource_class service_gen_res ON service_net_res.resource_class = service_gen_res.id) 
    
    left join  ca_resource_family service_resfam ON service_gen_res.family_id = service_resfam.id)
    
           
    
    left join  prob_ctg ON call_req.category = prob_ctg.persid) 
    
    left join  prob_ctg init_cat ON call_req.zcategory_init = init_cat.persid) 
    
    left join  View_Group ON call_req.group_id = View_Group.contact_uuid) 
    
    left join  ca_contact assignee ON call_req.assignee = assignee.contact_uuid)
    
    left join  ca_contact log_agent ON call_req.log_agent = log_agent.contact_uuid)
    
    left join  ca_contact last_mod_by ON call_req.log_agent = last_mod_by.contact_uuid)
    
    left join  cr_stat ON call_req.status = cr_stat.code)
    
    left join  Interface ON call_req.created_via = Interface.id)
    
           left join  ca_organization   customer_org ON  customer.organization_uuid = customer_org.organization_uuid) 
    
           left join  ca_organization   customer_admin ON customer.admin_organization_uuid = customer_admin.organization_uuid)
    
           left join  ca_location              customer_loc ON customer.location_uuid =     customer_loc.location_uuid) 
    
           
    
           
    
           left join  ca_organization   assignee_org ON  assignee.organization_uuid = assignee_org.organization_uuid) 
    
           left join  ca_organization   assignee_admin ON assignee.admin_organization_uuid = assignee_admin.organization_uuid)
    
           left join  ca_location              assignee_loc ON assignee.location_uuid =     assignee_loc.location_uuid) 
    
           
    
           left join  ca_organization   log_agent_org ON  log_agent.organization_uuid = log_agent_org.organization_uuid) 
    
           left join  ca_organization   log_agent_admin ON log_agent.admin_organization_uuid =        log_agent_admin.organization_uuid)
    
           left join  ca_location              log_agent_loc ON log_agent.location_uuid =    log_agent_loc.location_uuid) 
    
           
    
           left join  ca_organization   last_mod_by_org ON  last_mod_by.organization_uuid = last_mod_by_org.organization_uuid) 
    
           left join  ca_organization   last_mod_by_admin ON last_mod_by.admin_organization_uuid =        last_mod_by_admin.organization_uuid)
    
           left join  ca_location              last_mod_by_loc ON last_mod_by.location_uuid = last_mod_by_loc.location_uuid) 
    
           
    
           where call_req.template_name is null 
    
                     --and call_req.ref_num='RE5054'
    
                      order by al.last_mod_dt


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, March 6, 2019 10:49 AM
  • Hi Visakh,

    Thanks for the above query.

    I have another criteria where want to calculate the time difference only for "Awaiting Vendor" status to Updated Status. Where "Awaiting Vendor" status is storing in string format under description column. And next Updated status is also store in string format under description format. As An Example below : 

    Status Date type description call_req_id
    3/6/2019 6:53 INIT Create a new request/incident/problem/change/issue cr:416284
    3/6/2019 6:54 ADDITPAMWF New CA IT PAM Workflow Process attached for category 'Hardware' cr:416284
    3/6/2019 6:54 ST_ATTACH Attached/Updated service type into request/incident/problem/change/issue. cr:416284
    3/6/2019 6:54 HEAT_CHANGED Heat has changed from '' to 'Low' cr:416284
    3/6/2019 6:54 NF Manual notification
    3/6/2019 6:54 EVT AHD63034:event 'priority 3 cr resolution initial' triggered by condition 'Req. Status = Open'. cr:416284
    3/6/2019 6:54 ST Status changed from 'Open' to 'Acknowledged' cr:416284
    3/6/2019 6:54 ST Status changed from 'Acknowledged' to 'Awaiting Vendor' cr:416284
    3/6/2019 6:54 SLADELAY The Service Type(s) on a ticket were delayed cr:416284
    3/6/2019 6:59 ST Status changed from 'Awaiting Vendor' to 'In Progress' cr:416284
    3/6/2019 6:59 SLARESUME The Service Type(s) on the ticket were resumed cr:416284
    3/6/2019 7:05 ST Status changed from 'In Progress' to 'Awaiting Vendor' cr:416284
    3/6/2019 7:05 SLADELAY The Service Type(s) on a ticket were delayed cr:416284
    3/6/2019 7:05 HEAT_CHANGED Heat has changed from 'Low' to 'Medium' cr:416284
    3/6/2019 7:12 RE Status changed from 'Awaiting Vendor' to 'Resolved' cr:416284
    3/6/2019 7:12 SLARESUME The Service Type(s) on the ticket were resumed cr:416284
    3/6/2019 7:12 HEAT_CHANGED Heat has changed from 'Medium' to 'Low' cr:416284

    In the above example under description format need to pick difference when :

    from 'Awaiting Vendor' - from 'Awaiting Vendor'

    and if it is repeating multiple times then calculate the sum based on Business hours and business days set earlier. 

    Looking forward to your response. 

    This wont be easy

    Are you telling that always you will have even occurrences of Awaiting Vendor entries for each call_req_id value? Otherwise we wont be able to identify endtime for slots

    Taking above example what will be your final result?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, March 6, 2019 10:54 AM
  • Thanks Visakh it is still the same error Invalid Object name Final. Here I want to see as an output  [Vendor Elapsed Time] + All the fields in the other query.
    Wednesday, March 6, 2019 11:22 AM
  • Thanks Visakh it is still the same error Invalid Object name Final. Here I want to see as an output  [Vendor Elapsed Time] + All the fields in the other query.

    Are you sure you included the full code?

    Including the CTE definition i gave (code from With....)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, March 6, 2019 11:31 AM
  • Hi Visakh,

    Scenario is like that :

    3/6/2019 6:54 ST Status changed from 'Acknowledged' to 'Awaiting Vendor' cr:416284
    3/6/2019 6:54 SLADELAY The Service Type(s) on a ticket were delayed cr:416284
    3/6/2019 6:59 ST Status changed from 'Awaiting Vendor' to 'In Progress'

    Status changed from 'Acknowledged' to 'Awaiting Vendor' - here status of the ticket is changing from Acknowledge to awaiting vendor by which clock stops

    Status changed from 'Awaiting Vendor' to 'In Progress' - here status of the ticket is changing from Awaiting vendor to In Progress by which clock resume. 

    So require to pick "to 'Awaiting vendor' " and  "from 'Awaiting vendor' " always from the column description to calculate the last_mod_dt time difference

    Do let me know if you have any other query

    Status changed from 'Acknowledged' to 'Awaiting Vendor'
    Wednesday, March 6, 2019 11:41 AM
  • yes I did. Below is the query and error : (2 row(s) affected) Msg 208, Level 16, State 1, Line 34 Invalid object name 'Final'. ----------------------------- ;With CTE AS ( select call_req_id, DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date", description, type, row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq from act_log --where call_req_id = 'cr:410416' ),Final AS ( SELECT call_req_id, SUM(BusinessTime) AS TotalBusinessTime FROM ( SELECT call_req_id, dbo.BusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'07:00','18:00') AS BusinessTime FROM CTE WHERE type IN ('SLADELAY','SLARESUME') GROUP BY call_req_id,Seq )t GROUP BY call_req_id ) SELECT CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' , SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ', SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds') as [Vendor Elapsed Time] FROM Final SELECT /*call_req.id*/ call_req.persid, call_req.ref_num as "Ticket Num" ,call_req.summary as "Summary", call_req.description as "Description" /*,call_req.status*/ ,call_req.active_flag as "Active Flag" ,DATEADD(ss, al.last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Last Status Date" ,al.description as StatusComments ,call_req.open_date ,call_req.time_spent_sum as "Time Spent Sum" ,call_req.last_mod_dt ,call_req.close_date ,call_req.resolve_date /*,call_req.rootcause as "Root Cause"*/ ,Rootcause.sym as "Root Cause" ,call_req.charge_back_id as "Charge_Back id" ,call_req.support_lev as "Support Level" /*,call_req.category*/ /*,call_req.impact*/ /*,call_req.priority*/ /*,call_req.urgency*/ /*,call_req.severity*/ ,call_req.extern_ref as "External Reference" ,ca_resource_department.name as DepartmentName ,ca_resource_cost_center.name as CostCenter ,ca_location.location_name as LocationName ,ca_site.name as SiteName ,call_req.last_act_id as "Last Action Id" /*,call_req.cr_tticket as "Cr tticket"*/ /*,call_req.parent as "Parent"*/ ,Parent.ref_num as "Parent" ,Parent.status as "Parent Status" /*,call_req.template_name as "Template Name"*/ ,call_req.sla_violation as "SLA Violation" ,call_req.predicted_sla_viol as "Predicted SLA Violation" ,call_req.macro_predict_viol as "Macro Predicted Violation" /*,call_req.created_via*/ /*,call_req.call_back_date*/ ,call_req.call_back_flag as "Call Back Flag" ,call_req.event_token as "Event Token" ,call_req.sched_token as "Sched Token" ,call_req.type as "Ticket Type" /*,call_req.string1 as "string1" ,call_req.string2 as "string2" ,call_req.string3 as "string3" ,call_req.string4 as "string4" ,call_req.string5 as "string5" ,call_req.string6 as "string6"*/ /*,call_req.problem as "Problem"*/ ,Problem.ref_num as "Problem" ,Problem.status as "Problem Status" ,call_req.incident_priority as "Incident Priority" /*,call_req.change as "Change"*/ ,Change.chg_ref_num as "Change" ,Change.status as "Change Status" ,call_req.ticket_avoided as "Ticket Avoided" /*,call_req.caused_by_chg as "Caused By Chg"*/ ,caused_by_chg.chg_ref_num as "Caused By Chg" ,caused_by_chg.status as "Caused By Chg Status" /*,call_req.outage_start_time*/ /*,call_req.outage_end_time*/ ,call_req.external_system_ticket as "External System Ticket" ,call_req.incorrectly_assigned as "Incorrectly Assigned" ,call_req.major_incident as "Major Incident" ,call_req.outage_detail_what as "Outage Detail What" ,call_req.outage_detail_who as "Outage Detail Who" ,call_req.outage_detail_why as "Outage Detail Why" ,call_req.outage_reason_desc as "Outage Reason Desc " ,call_req.outage_type as "Outage Type" ,call_req.pct_service_restored as "Pct Service Restored" ,call_req.remote_control_used as "Remote Control Used" /*,call_req.resolution_code as "Resolution Code"*/ ,res_code.sym as "Resolution Code" /*,call_req.resolution_method as "Resolution Method"*/ ,res_method.sym as "Resolution Method" ,call_req.resolvable_at_lower as "Resolvable At Lower" ,call_req.return_to_service as "Return to Service" /*,call_req.symptom_code as "Symptom Code"*/ ,symptom.sym as "Symptom Code" ,call_req.target_closed_count as "Target Closed Count" ,call_req.target_closed_last as "Target Closed Last" ,call_req.target_hold_count as "Target Hold Count" ,call_req.target_hold_last as "Target Hold Last" ,call_req.target_resolved_count as "Target Resolved Count" ,call_req.target_resolved_last as "Target Resolved Last" ,call_req.target_start_last as "Target Start Last" ,call_req.fcr as "fcr" ,call_req.zusmrequestid as "USM Request ID" ,call_req.zusmrequestitemid as "USM Request Item ID" /*,call_req.zcategory_init as "Initial category"*/ ,init_cat.sym as "Initial Category", /*call_req.heat as "Heat",*/ DATEADD(ss, call_req.open_date +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Open Date", DATEADD(ss, call_req.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Last Modification Date", DATEADD(ss, call_req.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Close Date", DATEADD(ss, call_req.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Resolve Date", DATEADD(ss, call_req.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Call_back Date", DATEADD(ss, call_req.outage_start_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage Start Time", DATEADD(ss, call_req.outage_end_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage End Time", DATEADD(ss, parent.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Open Date", DATEADD(ss, parent.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Last Modification Date", DATEADD(ss, parent.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Close Date", DATEADD(ss, parent.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Resolve Date", DATEADD(ss, parent.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Call_back Date", DATEADD(ss, problem.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Open Date", DATEADD(ss, problem.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Last Modification Date", DATEADD(ss, problem.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Close Date", DATEADD(ss, problem.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Resolve Date", DATEADD(ss, problem.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Call_back Date", DATEADD(ss, change.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Open Date", DATEADD(ss, change.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Last Modification Date", DATEADD(ss, change.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Close Date", DATEADD(ss, change.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Resolve Date", DATEADD(ss, change.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Call_back Date", /*Time Since opening-- only for open tickets*/ case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) END AS "Time Since Opening(Seconds)", case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60 END AS "Time Since Opening(Hours)", case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 END AS "Time Since Opening(Days)", /*Time Since Last modification -- only for open tickets*/ case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt) END AS "Time Since Last Modification(Seconds)", case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60 END AS "Time Since Last Modification(Hours)", case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60/24 END AS "Time Since Last Modification(Days)", /*Time to resolve-- if Ticket is not resolved, time till now*/ case when ( call_req.resolve_date is not null and call_req.resolve_date >= call_req.open_date) THEN (call_req.resolve_date - call_req.open_date)else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) end AS "Time To Resolve(Seconds)", case when ( call_req.resolve_date is not null AND call_req.open_date is not null and call_req.resolve_date >= call_req.open_date) THEN (call_req.resolve_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60 end AS "Time To Resolve(Hours)", case when ( call_req.resolve_date is not null AND call_req.open_date is not null and call_req.resolve_date >= call_req.open_date) THEN (call_req.resolve_date - call_req.open_date)/60/60/24 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 end AS "Time To Resolve(Days)", /*Time to close-- if Ticket is not closed, time till now*/ case when ( call_req.close_date is not null AND call_req.open_date is not null and call_req.close_date >= call_req.open_date) THEN (call_req.close_date - call_req.open_date) else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) end AS "Time To Close(Seconds)", case when ( call_req.close_date is not null AND call_req.open_date is not null and call_req.close_date >= call_req.open_date) THEN (call_req.close_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60 end AS "Time To Close(Hours)", case when ( call_req.close_date is not null AND call_req.open_date is not null and call_req.close_date >= call_req.open_date) THEN (call_req.close_date - call_req.open_date)/60/60/24 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 end AS "Time To Close (Days)", /*Resolve to close, if ticket is not closed time from resolve to Now.*/ case when ( call_req.close_date is not null AND call_req.resolve_date is not null and call_req.close_date >= call_req.resolve_date) THEN (call_req.close_date - call_req.resolve_date) WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) end AS "Resolve To Close(Seconds)", case when ( call_req.close_date is not null AND call_req.resolve_date is not null and call_req.close_date >= call_req.resolve_date) THEN (call_req.close_date - call_req.resolve_date)/60/60 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60 end AS "Resolve To Close(Hours)", case when ( call_req.close_date is not null AND call_req.resolve_date is not null and call_req.close_date >= call_req.resolve_date) THEN (call_req.close_date - call_req.resolve_date)/60/60/24 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 end AS "Resolve To Close(Days)", /*Datediff (hour, call_req.open_date,call_req.resolve_date) as "Time To Resolve (Hours)", Datediff (day, call_req.resolve_date, call_req.close_date ) as "Resolve To Close (Days)", Datediff (hour, call_req.resolve_date, call_req.close_date) as "Resolve To Close (Hours)",*/ srv_desc.sym as "Service Type", sevrty.sym as "Severity", urgncy.sym as "Urgency", prob_ctg.sym as "Category", net_res.resource_name as "Asset Name", gen_res.name as "Asset Class", resfam.name as "Asset Family", service_net_res.resource_name as "Affected Service", service_gen_res.name as "Affected Service Class", service_resfam.name as "Affected Service Family", ci_enterprise_service.business_impact as "Business Impact", ci_enterprise_service.business_priority as "Business Priority", impact.sym as "Impact", cr_stat.sym as "Status", pri.sym as "Priority", interface.sym as "Created Via", View_Group.last_name as "Group", /*customer.last_name as "Customer Lastname", customer.first_name as "Customer Firstname", customer.middle_name as "Customer Middlename", */ customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ') as "Customer", --customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ') as "Supervisior", customer_loc.location_name as "Customer Location", customer_org.org_name as "Customer Organization", customer_admin.org_name as "Customer Admin Organization", /*assignee.last_name as "Assignee Lastname", assignee.first_name as "Assignee Firstname", assignee.middle_name as "Assignee Middlename",*/ assignee.last_name + coalesce( ', ' + assignee.first_name , ' ')+ coalesce (' ' + assignee.middle_name,' ') as "Assignee", assignee_loc.location_name as "Assignee Location", assignee_org.org_name as "Assignee Organization", assignee_admin.org_name as "Assignee Admin Organization", /*log_agent.last_name as "Log Agent Lastname", log_agent.first_name as "Log Agent Firstname", log_agent.middle_name as "Log Agent Middlename",*/ log_agent.last_name + coalesce( ', ' + log_agent.first_name , ' ')+ coalesce (' ' + log_agent.middle_name,' ') as "Log Agent", log_agent_loc.location_name as "Log Agent Location", log_agent_org.org_name as "Log Agent Organization", log_agent_admin.org_name as "Log Agent Admin Organization", /*last_mod_by.last_name as "Analyst Last Modified Lastname", last_mod_by.first_name as "Analyst Last Modified Firstname", last_mod_by.middle_name as "Analyst Last Modified Middlename",*/ last_mod_by.last_name + coalesce( ', ' + last_mod_by.first_name , ' ')+ coalesce (' ' + last_mod_by.middle_name,' ') as "Analyst Last Modified", last_mod_by_loc.location_name as "Analyst Last Modified Location", last_mod_by_org.org_name as "Analyst Last Modified Organization", last_mod_by_admin.org_name as "Analyst Last Modified Admin Organization", f.[Vendor Elapsed Time] FROM ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( call_req left join RootCause ON call_req.rootcause = rootcause.id) left join call_req Problem ON call_req.problem = Problem.persid) left join call_req Parent ON call_req.problem = Parent.persid) left join Chg Change ON call_req.change = Change.id) left join usp_resolution_code res_code ON call_req.resolution_code= res_code.id) left join usp_resolution_method res_method ON call_req.resolution_method= res_method.id) left join usp_symptom_code symptom ON call_req.symptom_code= symptom.id) left join Chg caused_by_chg ON call_req.change = caused_by_chg.id) inner join pri ON call_req.priority = pri.enum) inner join ca_contact customer ON call_req.customer =customer.contact_uuid) left join ca_resource_department on ca_resource_department.id=customer.department left join ca_resource_cost_center on ca_resource_cost_center.id=customer.cost_center left join ca_location on ca_location.location_uuid=customer.location_uuid left join ca_site on ca_location.site_id=ca_site.id left join ( SELECT *,ROW_NUMBER() OVER (PARTITION BY call_req_id ORDER BY last_mod_dt DESC) AS Seq FROM act_log where (act_log.type like 'ST' or act_log.type like 'INIT' or act_log.type like 'Log' or act_log.type like 'RE') and act_log.description not like '%Status changed%' and act_log.description not like '%Workflow task%' and act_log.description not like '%AHD%' and act_log.description not like '%create%' )al on al.call_req_id= call_req.persid and al.Seq = 1 left join Final f on f.call_req_id = call_req.persid left join srv_desc ON call_req.support_lev = srv_desc.code) left join sevrty ON call_req.severity = sevrty.enum) left join urgncy ON call_req.urgency = urgncy.enum) left join impact ON call_req.impact = impact.enum) left join ca_owned_resource net_res ON call_req.affected_rc = net_res.own_resource_uuid) left join ca_resource_class gen_res ON net_res.resource_class = gen_res.id) left join ca_resource_family resfam ON gen_res.family_id = resfam.id) left join ca_owned_resource service_net_res ON call_req.affected_service = service_net_res.own_resource_uuid) left join ci_enterprise_service ON call_req.affected_service = ci_enterprise_service.own_resource_uuid) left join ca_resource_class service_gen_res ON service_net_res.resource_class = service_gen_res.id) left join ca_resource_family service_resfam ON service_gen_res.family_id = service_resfam.id) left join prob_ctg ON call_req.category = prob_ctg.persid) left join prob_ctg init_cat ON call_req.zcategory_init = init_cat.persid) left join View_Group ON call_req.group_id = View_Group.contact_uuid) left join ca_contact assignee ON call_req.assignee = assignee.contact_uuid) left join ca_contact log_agent ON call_req.log_agent = log_agent.contact_uuid) left join ca_contact last_mod_by ON call_req.log_agent = last_mod_by.contact_uuid) left join cr_stat ON call_req.status = cr_stat.code) left join Interface ON call_req.created_via = Interface.id) left join ca_organization customer_org ON customer.organization_uuid = customer_org.organization_uuid) left join ca_organization customer_admin ON customer.admin_organization_uuid = customer_admin.organization_uuid) left join ca_location customer_loc ON customer.location_uuid = customer_loc.location_uuid) left join ca_organization assignee_org ON assignee.organization_uuid = assignee_org.organization_uuid) left join ca_organization assignee_admin ON assignee.admin_organization_uuid = assignee_admin.organization_uuid) left join ca_location assignee_loc ON assignee.location_uuid = assignee_loc.location_uuid) left join ca_organization log_agent_org ON log_agent.organization_uuid = log_agent_org.organization_uuid) left join ca_organization log_agent_admin ON log_agent.admin_organization_uuid = log_agent_admin.organization_uuid) left join ca_location log_agent_loc ON log_agent.location_uuid = log_agent_loc.location_uuid) left join ca_organization last_mod_by_org ON last_mod_by.organization_uuid = last_mod_by_org.organization_uuid) left join ca_organization last_mod_by_admin ON last_mod_by.admin_organization_uuid = last_mod_by_admin.organization_uuid) left join ca_location last_mod_by_loc ON last_mod_by.location_uuid = last_mod_by_loc.location_uuid) where call_req.template_name is null --and call_req.ref_num='RE5054' order by al.last_mod_dt
    Wednesday, March 6, 2019 11:52 AM
  • Nope this is not my latest suggestion

    You still have th SELECT ... FROM Final statement in between which I removed

    please copy my last posted suggestion and use it without any change

    Also when posting code please post it with code block otherwise its hard to read through due to no formatting


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, March 6, 2019 11:59 AM
  • Yes, I did but now giving error :

    Msg 207, Level 16, State 1, Line 489
    Invalid column name 'Vendor Elapsed Time'.

    Wednesday, March 6, 2019 12:07 PM
  • Yes, I did but now giving error :

    Msg 207, Level 16, State 1, Line 489
    Invalid column name 'Vendor Elapsed Time'.

    try like

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      --where call_req_id = 'cr:410416'
    ),Final
    AS
    (
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.BusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'07:00','18:00') AS BusinessTime
    FROM CTE
    WHERE type IN ('SLADELAY','SLARESUME')
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )
    
    
    SELECT 
    
                  /*call_req.id*/
    
          call_req.persid,
    
          call_req.ref_num as "Ticket Num"
    
          ,call_req.summary as "Summary",
    
             call_req.description as "Description"
    
          /*,call_req.status*/
    
          ,call_req.active_flag as "Active Flag"
    
      ,DATEADD(ss, al.last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Last Status Date"
    
      ,al.description as StatusComments
    
          ,call_req.open_date
    
          ,call_req.time_spent_sum as  "Time Spent Sum"
    
          ,call_req.last_mod_dt      
    
          ,call_req.close_date
    
             ,call_req.resolve_date
    
          /*,call_req.rootcause as  "Root Cause"*/
    
          ,Rootcause.sym as "Root Cause"
    
          ,call_req.charge_back_id as "Charge_Back id"
    
          ,call_req.support_lev as "Support Level"
    
          /*,call_req.category*/
    
          /*,call_req.impact*/
    
          /*,call_req.priority*/
    
          /*,call_req.urgency*/
    
          /*,call_req.severity*/
    
          ,call_req.extern_ref as "External Reference"
    
      ,ca_resource_department.name as DepartmentName
    
      ,ca_resource_cost_center.name as CostCenter
    
      ,ca_location.location_name as LocationName
    
      ,ca_site.name as SiteName
    
      
    
            ,call_req.last_act_id as "Last Action Id"
    
          /*,call_req.cr_tticket as  "Cr tticket"*/
    
          /*,call_req.parent as "Parent"*/
    
          ,Parent.ref_num as "Parent"
    
          ,Parent.status as "Parent Status"
    
          /*,call_req.template_name as "Template Name"*/
    
          ,call_req.sla_violation as "SLA Violation"
    
          ,call_req.predicted_sla_viol as "Predicted SLA Violation"
    
          ,call_req.macro_predict_viol as "Macro Predicted Violation"
    
          /*,call_req.created_via*/
    
          /*,call_req.call_back_date*/
    
          ,call_req.call_back_flag as "Call Back Flag"
    
          ,call_req.event_token as "Event Token"
    
          ,call_req.sched_token as "Sched Token"
    
          ,call_req.type as "Ticket Type"
    
          /*,call_req.string1 as "string1"
    
          ,call_req.string2 as "string2"
    
          ,call_req.string3 as "string3"
    
          ,call_req.string4 as "string4"
    
          ,call_req.string5 as "string5"
    
          ,call_req.string6 as "string6"*/
    
          /*,call_req.problem as "Problem"*/
    
          ,Problem.ref_num as "Problem"
    
          ,Problem.status as "Problem Status"
    
          
    
          ,call_req.incident_priority as "Incident Priority"
    
          /*,call_req.change as "Change"*/
    
          ,Change.chg_ref_num as "Change"
    
          ,Change.status as "Change Status"
    
          ,call_req.ticket_avoided as "Ticket Avoided"
    
          /*,call_req.caused_by_chg as "Caused By Chg"*/
    
          ,caused_by_chg.chg_ref_num as "Caused By Chg"
    
          ,caused_by_chg.status as "Caused By Chg Status"
    
          /*,call_req.outage_start_time*/
    
          /*,call_req.outage_end_time*/
    
          ,call_req.external_system_ticket as "External System Ticket"
    
          ,call_req.incorrectly_assigned as "Incorrectly Assigned"
    
          ,call_req.major_incident as "Major Incident"
    
          ,call_req.outage_detail_what as "Outage Detail What"
    
          ,call_req.outage_detail_who as "Outage Detail Who"
    
          ,call_req.outage_detail_why as "Outage Detail Why"
    
          ,call_req.outage_reason_desc as "Outage Reason Desc "
    
          ,call_req.outage_type as "Outage Type"
    
          ,call_req.pct_service_restored as "Pct Service Restored"
    
          ,call_req.remote_control_used as "Remote Control Used"
    
          /*,call_req.resolution_code as "Resolution Code"*/
    
          ,res_code.sym as "Resolution Code"  
    
          /*,call_req.resolution_method as "Resolution Method"*/
    
          ,res_method.sym as "Resolution Method"
    
          ,call_req.resolvable_at_lower as "Resolvable At Lower"
    
          ,call_req.return_to_service as "Return to Service"
    
          /*,call_req.symptom_code as "Symptom Code"*/
    
          ,symptom.sym as "Symptom Code"
    
          ,call_req.target_closed_count as "Target Closed Count"
    
          ,call_req.target_closed_last as "Target Closed Last"
    
          ,call_req.target_hold_count as "Target Hold Count"
    
          ,call_req.target_hold_last as "Target Hold Last"
    
          ,call_req.target_resolved_count as "Target Resolved Count"
    
          ,call_req.target_resolved_last as "Target Resolved Last"
    
          ,call_req.target_start_last as "Target Start Last"
    
          ,call_req.fcr as "fcr"
    
          ,call_req.zusmrequestid as "USM Request ID"
    
          ,call_req.zusmrequestitemid as "USM Request Item ID"
    
          /*,call_req.zcategory_init as "Initial category"*/
    
          ,init_cat.sym as "Initial Category",
    
             /*call_req.heat as "Heat",*/
    
          
    
          DATEADD(ss, call_req.open_date +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Open Date",
    
          DATEADD(ss, call_req.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Last Modification Date",
    
          DATEADD(ss, call_req.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Close Date",
    
          DATEADD(ss, call_req.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Resolve Date",
    
          DATEADD(ss, call_req.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Call_back Date",
    
          DATEADD(ss, call_req.outage_start_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage Start Time",
    
          DATEADD(ss, call_req.outage_end_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage End Time",
    
          
    
          DATEADD(ss, parent.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Open Date",
    
          DATEADD(ss, parent.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Last Modification Date",
    
          DATEADD(ss, parent.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Close Date",
    
          DATEADD(ss, parent.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Resolve Date",
    
          DATEADD(ss, parent.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Call_back Date",
    
          
    
          DATEADD(ss, problem.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Open Date",
    
          DATEADD(ss, problem.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Last Modification Date",
    
          DATEADD(ss, problem.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Close Date",
    
          DATEADD(ss, problem.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Resolve Date",
    
          DATEADD(ss, problem.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Call_back Date",
    
          
    
          DATEADD(ss, change.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Open Date",
    
          DATEADD(ss, change.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Last Modification Date",
    
          DATEADD(ss, change.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Close Date",
    
          DATEADD(ss, change.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Resolve Date",
    
          DATEADD(ss, change.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Call_back Date",
    
          
    
          /*Time Since opening-- only for open tickets*/
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) END AS "Time Since Opening(Seconds)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60 END AS "Time Since Opening(Hours)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 END AS "Time Since Opening(Days)",
    
          
    
           /*Time Since Last modification -- only for open tickets*/
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt) END AS "Time Since Last Modification(Seconds)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60 END AS "Time Since Last Modification(Hours)",
    
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60/24 END AS "Time Since Last Modification(Days)",
    
          
    
          
    
          /*Time to resolve-- if Ticket is not resolved, time till now*/
    
          case when ( call_req.resolve_date is not null   and call_req.resolve_date >= call_req.open_date) THEN      
    
             (call_req.resolve_date - call_req.open_date)else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) end AS "Time To Resolve(Seconds)",    
    
               
    
          case when ( call_req.resolve_date is not null AND call_req.open_date is not null  and call_req.resolve_date >= call_req.open_date) THEN      
    
             (call_req.resolve_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60  end AS "Time To Resolve(Hours)",
    
             
    
             case when ( call_req.resolve_date is not null AND call_req.open_date is not null  and call_req.resolve_date >= call_req.open_date) THEN      
    
             (call_req.resolve_date - call_req.open_date)/60/60/24  else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 end AS "Time To Resolve(Days)",
    
             
    
             /*Time to close-- if Ticket is not closed, time till now*/
    
             case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
    
             (call_req.close_date - call_req.open_date) else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)  end AS "Time To Close(Seconds)",    
    
               
    
          case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
    
             (call_req.close_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60  end AS "Time To Close(Hours)",
    
             
    
             case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
    
             (call_req.close_date - call_req.open_date)/60/60/24  else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24  end AS "Time To Close (Days)",
    
             
    
             /*Resolve to close, if ticket is not closed time from resolve to Now.*/
    
             case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
    
             (call_req.close_date - call_req.resolve_date)  WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)   end AS "Resolve To Close(Seconds)", 
    
               
    
          case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
    
             (call_req.close_date - call_req.resolve_date)/60/60 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60   end AS "Resolve To Close(Hours)",
    
           
    
             case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
    
             (call_req.close_date - call_req.resolve_date)/60/60/24 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24   end AS "Resolve To Close(Days)", 
    
             
    
            
    
               
    
          
    
                          /*Datediff (hour, call_req.open_date,call_req.resolve_date) as "Time To Resolve (Hours)",      
    
                          Datediff (day, call_req.resolve_date, call_req.close_date ) as "Resolve To Close (Days)",
    
                          Datediff (hour, call_req.resolve_date, call_req.close_date) as "Resolve To Close (Hours)",*/
    
         
    
         
    
          
    
             
    
           srv_desc.sym as "Service Type",
    
           sevrty.sym as "Severity",
    
           urgncy.sym as "Urgency", 
    
           prob_ctg.sym as "Category",
    
           
    
           net_res.resource_name as "Asset Name",
    
           gen_res.name as "Asset Class",
    
           resfam.name  as "Asset Family",
    
           
    
           service_net_res.resource_name as "Affected Service",
    
           service_gen_res.name as "Affected Service Class",
    
           service_resfam.name  as "Affected Service Family",
    
           ci_enterprise_service.business_impact as "Business Impact", 
    
           ci_enterprise_service.business_priority as "Business Priority", 
    
           
    
           impact.sym as "Impact", 
    
           
    
           cr_stat.sym as "Status",
    
           pri.sym as "Priority",
    
           interface.sym as "Created Via", 
    
           
    
           
    
           View_Group.last_name as "Group", 
    
           
    
           
    
           /*customer.last_name as "Customer Lastname",
    
           customer.first_name as "Customer Firstname",
    
           customer.middle_name as "Customer Middlename", */
    
           customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ')  as "Customer", 
    
      --customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ')  as "Supervisior", 
    
           customer_loc.location_name as       "Customer Location", 
    
           customer_org.org_name        as     "Customer Organization", 
    
           customer_admin.org_name      as "Customer Admin Organization",
    
           
    
           /*assignee.last_name as "Assignee Lastname", 
    
           assignee.first_name as "Assignee Firstname", 
    
           assignee.middle_name as "Assignee Middlename",*/
    
           assignee.last_name + coalesce( ', ' + assignee.first_name , ' ')+ coalesce (' ' + assignee.middle_name,' ') as "Assignee",
    
           assignee_loc.location_name as       "Assignee Location", 
    
           assignee_org.org_name        as     "Assignee Organization", 
    
           assignee_admin.org_name as   "Assignee Admin Organization",
    
           
    
           /*log_agent.last_name as "Log Agent Lastname", 
    
           log_agent.first_name as "Log Agent Firstname", 
    
           log_agent.middle_name as "Log Agent Middlename",*/
    
           log_agent.last_name + coalesce( ', ' + log_agent.first_name , ' ')+ coalesce (' ' + log_agent.middle_name,' ') as "Log Agent",          
    
           log_agent_loc.location_name as      "Log Agent Location",        
    
           log_agent_org.org_name       as     "Log Agent Organization", 
    
           log_agent_admin.org_name as  "Log Agent Admin Organization",
    
           
    
           /*last_mod_by.last_name as "Analyst Last Modified Lastname", 
    
           last_mod_by.first_name as "Analyst Last Modified Firstname", 
    
           last_mod_by.middle_name as "Analyst Last Modified Middlename",*/
    
           last_mod_by.last_name + coalesce( ', ' + last_mod_by.first_name , ' ')+ coalesce (' ' + last_mod_by.middle_name,' ') as "Analyst Last Modified",
    
           last_mod_by_loc.location_name as    "Analyst Last Modified Location",    
    
           last_mod_by_org.org_name     as     "Analyst Last Modified Organization", 
    
           last_mod_by_admin.org_name as       "Analyst Last Modified Admin Organization",
    	   CONCAT(DATEDIFF(dd,0,DATEADD(ss,f.TotalBusinessTime,0)), ' Days ', 
    		LEFT(CONVERT(varchar(8),DATEADD(ss,f.TotalBusinessTime,0),108),2),' Hours ' ,
    		SUBSTRING(CONVERT(varchar(8),DATEADD(ss,f.TotalBusinessTime,0),108),4,2),' Minutes ',
    		SUBSTRING(CONVERT(varchar(8),DATEADD(ss,f.TotalBusinessTime,0),108),7,2),' Seconds') as  [Vendor Elapsed Time]
    
    FROM (
    
           (
    
                  (      
    
                          (
    
                                 (
    
                                        (
    
                                               (
    
                                                      (
    
                                                              (      
    
                                                                     (
    
                                                                            (
    
                                                                                   (
    
                                                                                           (
    
                                                                                                  (
    
                                                                                                         (
    
                                                                                                                (
    
                                                                                                                       (
    
                                                                                                                               (
    
                                                                                                                                      (
    
                                                                                                                                             (
    
                                                                                                                                                    (
    
                                                                                                                                                            (
    
                                                                                                                                                                   (
    
                                                                                                                                                                          (
    
                                                                                                                                                                                 (
    
                                                                                                                                                                                        (
    
                                                                                                                                                                                                (
    
    (      
    
           (
    
                  (
    
                          (
    
                                 (
    
                                        (
    
                                               (
    
                                                      (
    
                                                              (
    
                                                                     (
    
                                                                            (
    
                                                                                   (
    
                                                                                           (      
    
                                                                                                                                                                                                
    
    (  call_req 
    
    left join RootCause ON call_req.rootcause = rootcause.id)
    
    left join call_req Problem ON call_req.problem = Problem.persid)
    
    left join call_req Parent ON call_req.problem = Parent.persid)
    
    left join Chg Change ON call_req.change = Change.id)
    
    left join usp_resolution_code res_code ON call_req.resolution_code= res_code.id)
    
    left join usp_resolution_method res_method ON call_req.resolution_method= res_method.id)
    
    left join usp_symptom_code symptom ON call_req.symptom_code= symptom.id)
    
    left join Chg caused_by_chg ON call_req.change = caused_by_chg.id)
    
    inner join  pri ON call_req.priority = pri.enum) 
    
    inner join  ca_contact customer ON call_req.customer =customer.contact_uuid) 
    
    left join ca_resource_department on ca_resource_department.id=customer.department
    
    left join ca_resource_cost_center on ca_resource_cost_center.id=customer.cost_center
    
    left join ca_location on ca_location.location_uuid=customer.location_uuid
    
    left join ca_site on ca_location.site_id=ca_site.id
    
    left join 
    
    (
    
    SELECT *,ROW_NUMBER() OVER (PARTITION BY call_req_id ORDER BY last_mod_dt DESC) AS Seq
    
    FROM act_log 
    
    where (act_log.type like 'ST' or act_log.type like 'INIT' or act_log.type like 'Log' or act_log.type like 'RE') and act_log.description not like '%Status changed%'
    
    and act_log.description not like '%Workflow task%' and  act_log.description not like '%AHD%' and  act_log.description not like '%create%'
    
       )al on al.call_req_id= call_req.persid and al.Seq = 1
    
    left join Final f
    on f.call_req_id = call_req.persid
    
    left join  srv_desc ON call_req.support_lev = srv_desc.code) 
    
    left join  sevrty ON call_req.severity = sevrty.enum) 
    
    left join  urgncy ON call_req.urgency = urgncy.enum) 
    
    left join  impact ON call_req.impact = impact.enum)
    
    left join  ca_owned_resource net_res ON call_req.affected_rc = net_res.own_resource_uuid)
    
    left join  ca_resource_class gen_res ON net_res.resource_class = gen_res.id) 
    
    left join  ca_resource_family resfam ON gen_res.family_id = resfam.id)
    
    left join  ca_owned_resource service_net_res ON call_req.affected_service = service_net_res.own_resource_uuid)
    
    left join  ci_enterprise_service ON call_req.affected_service = ci_enterprise_service.own_resource_uuid)
    
    left join  ca_resource_class service_gen_res ON service_net_res.resource_class = service_gen_res.id) 
    
    left join  ca_resource_family service_resfam ON service_gen_res.family_id = service_resfam.id)
    
           
    
    left join  prob_ctg ON call_req.category = prob_ctg.persid) 
    
    left join  prob_ctg init_cat ON call_req.zcategory_init = init_cat.persid) 
    
    left join  View_Group ON call_req.group_id = View_Group.contact_uuid) 
    
    left join  ca_contact assignee ON call_req.assignee = assignee.contact_uuid)
    
    left join  ca_contact log_agent ON call_req.log_agent = log_agent.contact_uuid)
    
    left join  ca_contact last_mod_by ON call_req.log_agent = last_mod_by.contact_uuid)
    
    left join  cr_stat ON call_req.status = cr_stat.code)
    
    left join  Interface ON call_req.created_via = Interface.id)
    
           left join  ca_organization   customer_org ON  customer.organization_uuid = customer_org.organization_uuid) 
    
           left join  ca_organization   customer_admin ON customer.admin_organization_uuid = customer_admin.organization_uuid)
    
           left join  ca_location              customer_loc ON customer.location_uuid =     customer_loc.location_uuid) 
    
           
    
           
    
           left join  ca_organization   assignee_org ON  assignee.organization_uuid = assignee_org.organization_uuid) 
    
           left join  ca_organization   assignee_admin ON assignee.admin_organization_uuid = assignee_admin.organization_uuid)
    
           left join  ca_location              assignee_loc ON assignee.location_uuid =     assignee_loc.location_uuid) 
    
           
    
           left join  ca_organization   log_agent_org ON  log_agent.organization_uuid = log_agent_org.organization_uuid) 
    
           left join  ca_organization   log_agent_admin ON log_agent.admin_organization_uuid =        log_agent_admin.organization_uuid)
    
           left join  ca_location              log_agent_loc ON log_agent.location_uuid =    log_agent_loc.location_uuid) 
    
           
    
           left join  ca_organization   last_mod_by_org ON  last_mod_by.organization_uuid = last_mod_by_org.organization_uuid) 
    
           left join  ca_organization   last_mod_by_admin ON last_mod_by.admin_organization_uuid =        last_mod_by_admin.organization_uuid)
    
           left join  ca_location              last_mod_by_loc ON last_mod_by.location_uuid = last_mod_by_loc.location_uuid) 
    
           
    
           where call_req.template_name is null 
    
                     --and call_req.ref_num='RE5054'
    
                      order by al.last_mod_dt

    Please copy and use the above as is


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by du00805 Wednesday, March 6, 2019 1:23 PM
    Wednesday, March 6, 2019 12:22 PM
  • Thanks Visakh it worked out.

    For the other query I am thinking of using charindex  like I did below but my results are not accurate so advise me where I am doing wrong. Here I am using CHARINDEX('to ''Waiting Vendor''', action_desc) >1 to get the correct string where action_desc is the column name. 

    Do you have any other questions let me know. 

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    action_desc,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      where CHARINDEX('to ''Waiting Vendor''', action_desc) >1 or CHARINDEX('from ''Waiting Vendor''', action_desc) >1
    ),Final
    AS
    (

    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.BusinessHours(MAX(CASE WHEN CHARINDEX('to ''Waiting Vendor''', action_desc) >1  THEN [Status Date] END),MAX(CASE WHEN CHARINDEX('from ''Waiting Vendor''', action_desc) >1
    THEN [Status Date] END),'09:00','17:00') AS BusinessTime
    FROM CTE
    WHERE  CHARINDEX('to ''Waiting Vendor''', action_desc) >1 
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )

    SELECT call_req_id,
    CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds') as  [Total Elapsed Time]
    FROM Final

    Wednesday, March 6, 2019 12:57 PM
  • Your posted data had 

    from 'Awaiting Vendor'

    as in example above

    Status changed from 'Awaiting Vendor' to 'Resolved'

    In CHARINDEX logic you're checking for from 'Waiting Vendor'

    which is wrong and will not match the pattern within the actual string


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, March 6, 2019 1:30 PM
  • sorry I put wrong status. Here is the correct code which I wrote but giving incorrect results. The code which I put in bold may be giving wrong information but I guess charindex is the correct way to calculate last_mod_dt difference between "to Awaiting vendor" and "from Awaiting Vendor"


    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    action_desc,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      --where call_req_id = 'cr:410416'
    ),Final
    AS
    (

    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.BusinessHours(MAX(CASE WHEN CHARINDEX('to ''Awaiting Vendor''', action_desc) >1  THEN [Status Date] END),MAX(CASE WHEN CHARINDEX('from ''Awaiting Vendor''', action_desc) >1
    THEN [Status Date] END),'09:00','17:00') AS BusinessTime

    FROM CTE
    WHERE CHARINDEX('to ''Awaiting Vendor''', action_desc) >1 or CHARINDEX('from ''Awaiting Vendor''', action_desc) >1
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )

    SELECT
    CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds') as [Total Elapsed Time]
    FROM Final

    Wednesday, March 6, 2019 1:42 PM
  • hi visakh,

    do let me know is this the correct way I have to do using charindex or by this way I cannot calculate the last_mod_dt time difference in days hrs min sec ?

    Thursday, March 7, 2019 5:40 AM
  • hi visakh,

    do let me know is this the correct way I have to do using charindex or by this way I cannot calculate the last_mod_dt time difference in days hrs min sec ?

    Are you sure you'll have only one instance of to Awaiting Vendor and from Awaiting Vendor rows with each call_req_id, type group?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 7, 2019 5:52 AM
  • here is the all field of act_log table

    I made this query to get all data

    select 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Last Modify Date",
    * from act_log where call_req_id = 'cr:416284'

    Last Modify Date id persid call_req_id last_mod_dt time_spent time_stamp system_time analyst description action_desc type knowledge_session knowledge_tool internal tenant rel_ticket_type zClarityLastSync
    3/6/2019 6:53 2347931 alg:2347931 cr:416284 1551851635 37 1551851597 1551851598 0x62944896E596AC4CB5F1677FBAC2D7A8 Create a new request/incident/problem/change/issue Create a new request/incident/problem/change/issue INIT NULL PDM 0 NULL NULL NULL
    3/6/2019 6:54 2347932 alg:2347932 cr:416284 1551851643 NULL 1551851643 1551851643 0x62944896E596AC4CB5F1677FBAC2D7A8 New CA IT PAM Workflow Process attached for category 'Hardware' CA IT PAM Workflow process attached ADDITPAMWF NULL PDM 1 NULL NULL NULL
    3/6/2019 6:54 2347933 alg:2347933 cr:416284 1551851643 0 1551851643 1551851643 0x62944896E596AC4CB5F1677FBAC2D7A8 Attached/Updated service type into request/incident/problem/change/issue. Service Type Attached/Updated ST_ATTACH NULL PDM 1 NULL NULL NULL
    3/6/2019 6:54 2347934 alg:2347934 cr:416284 1551851644 NULL 1551851644 1551851644 0x793ED69B4E87A545BD8E911834D829FC Heat has changed from '' to 'Low' Heat of the ticket has changed HEAT_CHANGED NULL PDM 1 NULL NULL NULL
    3/6/2019 6:54 2347935 alg:2347935 cr:416284 1551851646 NULL 1551851646 1551851646 0x793ED69B4E87A545BD8E911834D829FC Manual notification
    NF NULL PDM 0 NULL NULL NULL
    3/6/2019 6:54 2347936 alg:2347936 cr:416284 1551851649 0 1551851649 1551851649 0x793ED69B4E87A545BD8E911834D829FC AHD63034:event 'priority 3 cr resolution initial' triggered by condition 'Req. Status = Open'. log an event occurrence EVT NULL PDM 0 NULL NULL NULL
    3/6/2019 6:54 2347937 alg:2347937 cr:416284 1551851682 18 1551851664 1551851664 0x62944896E596AC4CB5F1677FBAC2D7A8 Status changed from 'Open' to 'Acknowledged' Status changed from 'Open' to 'Acknowledged' ST NULL PDM 0 NULL NULL NULL
    3/6/2019 6:54 2347938 alg:2347938 cr:416284 1551851696 9 1551851687 1551851687 0x62944896E596AC4CB5F1677FBAC2D7A8 Status changed from 'Acknowledged' to 'Awaiting Vendor' Status changed from 'Acknowledged' to 'Awaiting Vendor' ST NULL PDM 0 NULL NULL NULL
    3/6/2019 6:54 2347939 alg:2347939 cr:416284 1551851697 0 1551851697 1551851697 0x62944896E596AC4CB5F1677FBAC2D7A8 The Service Type(s) on a ticket were delayed The Service Type(s) on a ticket were delayed SLADELAY NULL PDM 1 NULL NULL NULL
    3/6/2019 6:59 2347940 alg:2347940 cr:416284 1551851985 9 1551851976 1551851976 0x62944896E596AC4CB5F1677FBAC2D7A8 Status changed from 'Awaiting Vendor' to 'In Progress' Status changed from 'Awaiting Vendor' to 'In Progress' ST NULL PDM 0 NULL NULL NULL
    3/6/2019 6:59 2347941 alg:2347941 cr:416284 1551851986 0 1551851986 1551851986 0x62944896E596AC4CB5F1677FBAC2D7A8 The Service Type(s) on the ticket were resumed The Service Type(s) on the ticket were resumed SLARESUME NULL PDM 1 NULL NULL NULL
    3/6/2019 7:05 2347942 alg:2347942 cr:416284 1551852330 6 1551852324 1551852324 0x62944896E596AC4CB5F1677FBAC2D7A8 Status changed from 'In Progress' to 'Awaiting Vendor' Status changed from 'In Progress' to 'Awaiting Vendor' ST NULL PDM 0 NULL NULL NULL
    3/6/2019 7:05 2347943 alg:2347943 cr:416284 1551852331 0 1551852331 1551852331 0x62944896E596AC4CB5F1677FBAC2D7A8 The Service Type(s) on a ticket were delayed The Service Type(s) on a ticket were delayed SLADELAY NULL PDM 1 NULL NULL NULL
    3/6/2019 7:05 2347944 alg:2347944 cr:416284 1551852331 NULL 1551852331 1551852331 0x793ED69B4E87A545BD8E911834D829FC Heat has changed from 'Low' to 'Medium' Heat of the ticket has changed HEAT_CHANGED NULL PDM 1 NULL NULL NULL
    3/6/2019 7:12 2347946 alg:2347946 cr:416284 1551852735 6 1551852729 1551852729 0x62944896E596AC4CB5F1677FBAC2D7A8 Status changed from 'Awaiting Vendor' to 'Resolved' Status changed from 'Awaiting Vendor' to 'Resolved' RE NULL PDM 0 NULL NULL NULL
    3/6/2019 7:12 2347947 alg:2347947 cr:416284 1551852736 0 1551852736 1551852736 0x62944896E596AC4CB5F1677FBAC2D7A8 The Service Type(s) on the ticket were resumed The Service Type(s) on the ticket were resumed SLARESUME NULL PDM 1 NULL NULL NULL
    3/6/2019 7:12 2347948 alg:2347948 cr:416284 1551852736 NULL 1551852736 1551852736 0x793ED69B4E87A545BD8E911834D829FC Heat has changed from 'Medium' to 'Low' Heat of the ticket has changed HEAT_CHANGED NULL PDM 1 NULL NULL NULL
    3/6/2019 13:00 2349016 alg:2349016 cr:416284 1551873600 0 1551873600 1551873600 0x793ED69B4E87A545BD8E911834D829FC AHD63034:event 'priority 3 cr resolution alert' triggered by condition 'Req. Status <> Work In Progress'. log an event occurrence EVT NULL PDM 0 NULL NULL NULL
    3/6/2019 13:00 2349017 alg:2349017 cr:416284 1551873600 0 1551873600 1551873600 0x62944896E596AC4CB5F1677FBAC2D7A8 FIELD='Severity' OLD='' NEW='1-Escalated'
    FIELD='Severity' OLD='' NEW='1-Escalated'
    FLD NULL PDM 0 NULL NULL NULL
    3/6/2019 14:00 2349212 alg:2349212 cr:416284 1551877200 0 1551877200 1551877200 0x793ED69B4E87A545BD8E911834D829FC AHD63034:event 'priority 3 cr resolution alert' triggered by condition 'Req. Status <> Work In Progress'. log an event occurrence EVT NULL PDM 0 NULL NULL NULL
    3/6/2019 14:00 2349213 alg:2349213 cr:416284 1551877200 0 1551877200 1551877200 0x62944896E596AC4CB5F1677FBAC2D7A8 FIELD='Severity' OLD='1-Escalated' NEW='2-Supervisor Escal.'
    FIELD='Severity' OLD='1-Escalated' NEW='2-Supervisor Escal.'
    FLD NULL PDM 0 NULL NULL NULL
    3/6/2019 15:00 2349408 alg:2349408 cr:416284 1551880800 0 1551880800 1551880800 0x793ED69B4E87A545BD8E911834D829FC AHD63034:event 'priority 3 cr resolution alert' triggered by condition 'Req. Status <> Work In Progress'. log an event occurrence EVT NULL PDM 0 NULL NULL NULL
    3/6/2019 15:00 2349409 alg:2349409 cr:416284 1551880800 0 1551880800 1551880800 0x62944896E596AC4CB5F1677FBAC2D7A8 FIELD='Severity' OLD='2-Supervisor Escal.' NEW='3-Mgr Escal.'
    FIELD='Severity' OLD='2-Supervisor Escal.' NEW='3-Mgr Escal.'
    FLD NULL PDM 0 NULL NULL NULL
    3/6/2019 16:00 2349652 alg:2349652 cr:416284 1551884400 0 1551884400 1551884400 0x793ED69B4E87A545BD8E911834D829FC AHD63034:event 'priority 3 cr resolution alert' triggered by condition 'Req. Status <> Work In Progress'. log an event occurrence EVT NULL PDM 0 NULL NULL NULL
    3/6/2019 16:00 2349653 alg:2349653 cr:416284 1551884400 0 1551884400 1551884400 0x62944896E596AC4CB5F1677FBAC2D7A8 FIELD='Severity' OLD='3-Mgr Escal.' NEW='4-HD Mgr Escalation'
    FIELD='Severity' OLD='3-Mgr Escal.' NEW='4-HD Mgr Escalation'
    FLD NULL PDM 0 NULL NULL NULL
    3/6/2019 17:00 2349848 alg:2349848 cr:416284 1551888000 0 1551888000 1551888000 0x793ED69B4E87A545BD8E911834D829FC AHD63034:event 'priority 3 cr resolution alert' triggered by condition 'Req. Status <> Work In Progress'. log an event occurrence EVT NULL PDM 0 NULL NULL NULL
    3/6/2019 17:00 2349849 alg:2349849 cr:416284 1551888000 0 1551888000 1551888000 0x62944896E596AC4CB5F1677FBAC2D7A8 FIELD='Severity' OLD='4-HD Mgr Escalation' NEW='5-All Hands Escalation'


    • Edited by du00805 Thursday, March 7, 2019 6:17 AM
    Thursday, March 7, 2019 6:04 AM
  • HI Visakh,

    Do let me know if you require more information. Yes the status I can only fetch from action_desc column using to Awaiting Vendor and from Awaiting Vendor by charindex. Is this possible to calculate last_mod_dt time difference ?

    Thursday, March 7, 2019 8:13 AM
  • HI Visakh,

    Do let me know if you require more information. Yes the status I can only fetch from action_desc column using to Awaiting Vendor and from Awaiting Vendor by charindex. Is this possible to calculate last_mod_dt time difference ?

    its possible only if you've the data in description column coming in a consistent format

    Even then, it will have a performance hit, especially for large amount of data as you're relying upon pattern search


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 7, 2019 8:26 AM
  • yes, ofcourse the data coming under action_desc column is always in the format of "Status changed from 'Awaiting Vendor' to 'In Progress'" like as shown in below screenshot. So from this format we can use charindex 

    Thursday, March 7, 2019 8:32 AM
  • yes, ofcourse the data coming under action_desc column is always in the format of "Status changed from 'Awaiting Vendor' to 'In Progress'" like as shown in below screenshot. So from this format we can use charindex 

    Then try like this and see if it works

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    action_desc,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      --where call_req_id = 'cr:410416'
    ),Final
    AS
    (
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.BusinessHours(MAX(CASE WHEN PATINDEX('%from ''Awaiting Vendor''%', action_desc) >  0  THEN [Status Date] END),MAX(CASE WHEN PATINDEX('%to ''Awaiting Vendor''%', action_desc) > 0
    THEN [Status Date] END),'09:00','17:00') AS BusinessTime
    
    FROM CTE
    WHERE PATINDEX('%from ''Awaiting Vendor''%', action_desc) >  0 or PATINDEX('%to ''Awaiting Vendor''%', action_desc) > 0 
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )
    
    SELECT
    CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds') as [Total Elapsed Time]
    FROM Final

    Hopefully you will get an acceptable level of performance as pattern search can be quite costly


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 7, 2019 8:38 AM
  • Thanks query executed but giving wrong calculation as below. 

    But when calculated previously based in SLADELYA and SLARESUME. Calculation appearing like below

    • Edited by du00805 Thursday, March 7, 2019 8:51 AM
    Thursday, March 7, 2019 8:49 AM
  • Thanks query executed but giving wrong calculation as below. 

    But when calculated previously based in SLADELYA and SLARESUME. Calculation appearing like below

    How can we understand what is going wrong without knowing on how data appears for you

    As suggested previously, unless the format of description doesnt come consistent, solution wont work well. Also As specified earlier there should not be multiple occurances of any one type coming or cases of missing rows with particular category

    If you can post some sample data as CREATE TABLE..INSERT statements for which issue happens we can see what exactly is causing the issue


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 7, 2019 9:01 AM
  • Below is the act_log table and sample date. I have provided the few fields sample date.

    Do let me know if you require more fields data from the provided table

    CREATE TABLE [dbo].[act_log](
    [id] [int] NOT NULL,
    [persid] [nvarchar](30) NULL,
    [call_req_id] [nvarchar](30) NULL,
    [last_mod_dt] [int] NULL,
    [time_spent] [int] NULL,
    [time_stamp] [int] NULL,
    [system_time] [int] NULL,
    [analyst] [binary](16) NULL,
    [description] [ntext] NULL,
    [action_desc] [ntext] NULL,
    [type] [nvarchar](30) NULL,
    [knowledge_session] [nvarchar](80) NULL,
    [knowledge_tool] [nvarchar](12) NULL,
    [internal] [int] NULL,
    [tenant] [binary](16) NULL,
    [rel_ticket_type] [nvarchar](12) NULL,
    [zClarityLastSync] [int] NULL,

    Last Modify Date type action_desc last_mod_dt call_req_id system_time id persid
    3/6/19 6:53 AM INIT Create a new request/incident/problem/change/issue 1551851635 cr:416284 1551851598 2347931 alg:2347931
    3/6/19 6:54 AM ADDITPAMWF CA IT PAM Workflow process attached 1551851643 cr:416284 1551851643 2347932 alg:2347932
    3/6/19 6:54 AM ST_ATTACH Service Type Attached/Updated 1551851643 cr:416284 1551851643 2347933 alg:2347933
    3/6/19 6:54 AM HEAT_CHANGED Heat of the ticket has changed 1551851644 cr:416284 1551851644 2347934 alg:2347934
    3/6/19 6:54 AM NF Manual notification Notify  'Smith, Kevin ' Message title: Problem Pain/Value Analysis Survey: Please Complete 1551851646 cr:416284 1551851646 2347935 alg:2347935
    3/6/19 6:54 AM EVT log an event occurrence 1551851649 cr:416284 1551851649 2347936 alg:2347936
    3/6/19 6:54 AM ST Status changed from 'Open' to 'Acknowledged' 1551851682 cr:416284 1551851664 2347937 alg:2347937
    3/6/19 6:54 AM ST Status changed from 'Acknowledged' to 'Awaiting Vendor' 1551851696 cr:416284 1551851687 2347938 alg:2347938
    3/6/19 6:54 AM SLADELAY The Service Type(s) on a ticket were delayed 1551851697 cr:416284 1551851697 2347939 alg:2347939
    3/6/19 6:59 AM ST Status changed from 'Awaiting Vendor' to 'In Progress' 1551851985 cr:416284 1551851976 2347940 alg:2347940
    3/6/19 6:59 AM SLARESUME The Service Type(s) on the ticket were resumed 1551851986 cr:416284 1551851986 2347941 alg:2347941
    3/6/19 7:05 AM ST Status changed from 'In Progress' to 'Awaiting Vendor' 1551852330 cr:416284 1551852324 2347942 alg:2347942
    3/6/19 7:05 AM SLADELAY The Service Type(s) on a ticket were delayed 1551852331 cr:416284 1551852331 2347943 alg:2347943
    3/6/19 7:05 AM HEAT_CHANGED Heat of the ticket has changed 1551852331 cr:416284 1551852331 2347944 alg:2347944
    3/6/19 7:12 AM RE Status changed from 'Awaiting Vendor' to 'Resolved' 1551852735 cr:416284 1551852729 2347946 alg:2347946
    3/6/19 7:12 AM SLARESUME The Service Type(s) on the ticket were resumed 1551852736 cr:416284 1551852736 2347947 alg:2347947
    3/6/19 7:12 AM HEAT_CHANGED Heat of the ticket has changed 1551852736 cr:416284 1551852736 2347948 alg:2347948
    3/6/19 1:00 PM EVT log an event occurrence 1551873600 cr:416284 1551873600 2349016 alg:2349016
    1551873600 cr:416284 1551873600 2349017 alg:2349017
    3/6/19 2:00 PM EVT log an event occurrence 1551877200 cr:416284 1551877200 2349212 alg:2349212
    3/6/19 2:00 PM FLD FIELD='Severity' OLD='1-Escalated' NEW='2-Supervisor Escal.'
    1551877200 cr:416284 1551877200 2349213 alg:2349213
    3/6/19 3:00 PM EVT log an event occurrence 1551880800 cr:416284 1551880800 2349408 alg:2349408
    3/6/19 3:00 PM FLD FIELD='Severity' OLD='2-Supervisor Escal.' NEW='3-Mgr Escal.'
    1551880800 cr:416284 1551880800 2349409 alg:2349409
    3/6/19 4:00 PM EVT log an event occurrence 1551884400 cr:416284 1551884400 2349652 alg:2349652
    3/6/19 4:00 PM FLD FIELD='Severity' OLD='3-Mgr Escal.' NEW='4-HD Mgr Escalation'
    1551884400 cr:416284 1551884400 2349653 alg:2349653
    3/6/19 5:00 PM EVT log an event occurrence 1551888000 cr:416284 1551888000 2349848 alg:2349848
    1551888000 cr:416284 1551888000 2349849 alg:2349849
    3/7/19 9:00 AM EVT log an event occurrence 1551945600 cr:416284 1551945600 2350044 alg:2350044
    3/7/19 10:00 AM EVT log an event occurrence 1551949200 cr:416284 1551949200 2350286 alg:2350286

    Thursday, March 7, 2019 9:45 AM
  • please provide data as insert statements. That would make it easier for someone to create table with data and reproduce the scenario

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 7, 2019 10:05 AM
  • sure will share that. Shall I share the all database so that you can download online ?
    Thursday, March 7, 2019 11:05 AM
  • sure will share that. Shall I share the all database so that you can download online ?

    nope

    just give table creation script and some sample data for scenario as insert statements

    see how to post data guidelines here

    https://weblogs.sqlteam.com/brettk/2005/05/25/5276/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 7, 2019 11:37 AM
  • sure will do. Visakh can you check what modification I can do while creating the view for the query modified by you.

    https://social.msdn.microsoft.com/Forums/en-US/30a71ec3-5ebb-400a-82c7-486db85c3528/create-view-error?forum=transactsql

    Thursday, March 7, 2019 12:40 PM
  • sure will do. Visakh can you check what modification I can do while creating the view for the query modified by you.

    https://social.msdn.microsoft.com/Forums/en-US/30a71ec3-5ebb-400a-82c7-486db85c3528/create-view-error?forum=transactsql

    ORDER BY should be removed as it is meaningless inside view code unless you had a TOP x critieria

    so do like this

    create view test as 
    With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    description,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from act_log
      --where call_req_id = 'cr:401219'
    ),Final
    AS
    (
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.BusinessHours(MAX(CASE WHEN type = 'SLADELAY' THEN [Status Date] END),MAX(CASE WHEN type = 'SLARESUME' THEN [Status Date] END),'09:00','17:00') AS BusinessTime
    FROM CTE
    WHERE type IN ('SLADELAY','SLARESUME')
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )
    
    SELECT 
                  /*call_req.id*/
          call_req.persid,
          call_req.ref_num as "Ticket Num"
          ,call_req.summary as "Summary",
             call_req.description as "Description"
          /*,call_req.status*/
          ,call_req.active_flag as "Active Flag"
      ,DATEADD(ss, al.last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Last Status Date"
      ,al.description as StatusComments
          ,call_req.open_date
          ,call_req.time_spent_sum as  "Time Spent Sum"
          ,call_req.last_mod_dt      
          ,call_req.close_date
             ,call_req.resolve_date
          /*,call_req.rootcause as  "Root Cause"*/
          ,Rootcause.sym as "Root Cause"
          ,call_req.charge_back_id as "Charge_Back id"
          ,call_req.support_lev as "Support Level"
          /*,call_req.category*/
          /*,call_req.impact*/
          /*,call_req.priority*/
          /*,call_req.urgency*/
          /*,call_req.severity*/
          ,call_req.extern_ref as "External Reference"
      ,ca_resource_department.name as DepartmentName
      ,ca_resource_cost_center.name as CostCenter
      ,ca_location.location_name as LocationName
      ,ca_site.name as SiteName
      
            ,call_req.last_act_id as "Last Action Id"
          /*,call_req.cr_tticket as  "Cr tticket"*/
          /*,call_req.parent as "Parent"*/
          ,Parent.ref_num as "Parent"
          ,Parent.status as "Parent Status"
          /*,call_req.template_name as "Template Name"*/
          ,call_req.sla_violation as "SLA Violation"
          ,call_req.predicted_sla_viol as "Predicted SLA Violation"
          ,call_req.macro_predict_viol as "Macro Predicted Violation"
          /*,call_req.created_via*/
          /*,call_req.call_back_date*/
          ,call_req.call_back_flag as "Call Back Flag"
          ,call_req.event_token as "Event Token"
          ,call_req.sched_token as "Sched Token"
          ,call_req.type as "Ticket Type"
          /*,call_req.string1 as "string1"
          ,call_req.string2 as "string2"
          ,call_req.string3 as "string3"
          ,call_req.string4 as "string4"
          ,call_req.string5 as "string5"
          ,call_req.string6 as "string6"*/
          /*,call_req.problem as "Problem"*/
          ,Problem.ref_num as "Problem"
          ,Problem.status as "Problem Status"
          
          ,call_req.incident_priority as "Incident Priority"
          /*,call_req.change as "Change"*/
          ,Change.chg_ref_num as "Change"
          ,Change.status as "Change Status"
          ,call_req.ticket_avoided as "Ticket Avoided"
          /*,call_req.caused_by_chg as "Caused By Chg"*/
          ,caused_by_chg.chg_ref_num as "Caused By Chg"
          ,caused_by_chg.status as "Caused By Chg Status"
          /*,call_req.outage_start_time*/
          /*,call_req.outage_end_time*/
          ,call_req.external_system_ticket as "External System Ticket"
          ,call_req.incorrectly_assigned as "Incorrectly Assigned"
          ,call_req.major_incident as "Major Incident"
          ,call_req.outage_detail_what as "Outage Detail What"
          ,call_req.outage_detail_who as "Outage Detail Who"
          ,call_req.outage_detail_why as "Outage Detail Why"
          ,call_req.outage_reason_desc as "Outage Reason Desc "
          ,call_req.outage_type as "Outage Type"
          ,call_req.pct_service_restored as "Pct Service Restored"
          ,call_req.remote_control_used as "Remote Control Used"
          /*,call_req.resolution_code as "Resolution Code"*/
          ,res_code.sym as "Resolution Code"  
          /*,call_req.resolution_method as "Resolution Method"*/
          ,res_method.sym as "Resolution Method"
          ,call_req.resolvable_at_lower as "Resolvable At Lower"
          ,call_req.return_to_service as "Return to Service"
          /*,call_req.symptom_code as "Symptom Code"*/
          ,symptom.sym as "Symptom Code"
          ,call_req.target_closed_count as "Target Closed Count"
          ,call_req.target_closed_last as "Target Closed Last"
          ,call_req.target_hold_count as "Target Hold Count"
          ,call_req.target_hold_last as "Target Hold Last"
          ,call_req.target_resolved_count as "Target Resolved Count"
          ,call_req.target_resolved_last as "Target Resolved Last"
          ,call_req.target_start_last as "Target Start Last"
          ,call_req.fcr as "fcr"
          ,call_req.zusmrequestid as "USM Request ID"
          ,call_req.zusmrequestitemid as "USM Request Item ID"
          /*,call_req.zcategory_init as "Initial category"*/
          ,init_cat.sym as "Initial Category",
             /*call_req.heat as "Heat",*/
          
          DATEADD(ss, call_req.open_date +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Open Date",
          DATEADD(ss, call_req.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Last Modification Date",
          DATEADD(ss, call_req.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Close Date",
          DATEADD(ss, call_req.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Resolve Date",
          DATEADD(ss, call_req.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Call_back Date",
          DATEADD(ss, call_req.outage_start_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage Start Time",
          DATEADD(ss, call_req.outage_end_time+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Outage End Time",
          
          DATEADD(ss, parent.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Open Date",
          DATEADD(ss, parent.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Last Modification Date",
          DATEADD(ss, parent.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Close Date",
          DATEADD(ss, parent.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Resolve Date",
          DATEADD(ss, parent.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Parent Call_back Date",
          
          DATEADD(ss, problem.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Open Date",
          DATEADD(ss, problem.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Last Modification Date",
          DATEADD(ss, problem.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Close Date",
          DATEADD(ss, problem.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Resolve Date",
          DATEADD(ss, problem.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Problem Call_back Date",
          
          DATEADD(ss, change.open_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Open Date",
          DATEADD(ss, change.last_mod_dt+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Last Modification Date",
          DATEADD(ss, change.close_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Close Date",
          DATEADD(ss, change.resolve_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Resolve Date",
          DATEADD(ss, change.call_back_date+DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')as "Change Call_back Date",
          
          /*Time Since opening-- only for open tickets*/
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) END AS "Time Since Opening(Seconds)",
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60 END AS "Time Since Opening(Hours)",
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 END AS "Time Since Opening(Days)",
      case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24/7 END AS "Time Since Opening(Weeks)",
      case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24/30 END AS "Time Since Opening(Months)",
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24/365 END AS "Time Since Opening(Years)",
          
           /*Time Since Last modification -- only for open tickets*/
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt) END AS "Time Since Last Modification(Seconds)",
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60 END AS "Time Since Last Modification(Hours)",
          case when (call_req.active_flag =1) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.last_mod_dt)/60/60/24 END AS "Time Since Last Modification(Days)",
          
          
          /*Time to resolve-- if Ticket is not resolved, time till now*/
          case when ( call_req.resolve_date is not null   and call_req.resolve_date >= call_req.open_date) THEN      
             (call_req.resolve_date - call_req.open_date)else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date) end AS "Time To Resolve(Seconds)",    
               
          case when ( call_req.resolve_date is not null AND call_req.open_date is not null  and call_req.resolve_date >= call_req.open_date) THEN      
             (call_req.resolve_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60  end AS "Time To Resolve(Hours)",
             
             case when ( call_req.resolve_date is not null AND call_req.open_date is not null  and call_req.resolve_date >= call_req.open_date) THEN      
             (call_req.resolve_date - call_req.open_date)/60/60/24  else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24 end AS "Time To Resolve(Days)",
             
             /*Time to close-- if Ticket is not closed, time till now*/
             case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
             (call_req.close_date - call_req.open_date) else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)  end AS "Time To Close(Seconds)",    
               
          case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
             (call_req.close_date - call_req.open_date)/60/60 else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60  end AS "Time To Close(Hours)",
             
             case when ( call_req.close_date is not null AND call_req.open_date is not null  and call_req.close_date >= call_req.open_date) THEN      
             (call_req.close_date - call_req.open_date)/60/60/24  else (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24  end AS "Time To Close (Days)",
             
             /*Resolve to close, if ticket is not closed time from resolve to Now.*/
             case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
             (call_req.close_date - call_req.resolve_date)  WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)   end AS "Resolve To Close(Seconds)", 
               
          case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
             (call_req.close_date - call_req.resolve_date)/60/60 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60   end AS "Resolve To Close(Hours)",
           
             case when ( call_req.close_date is not null AND call_req.resolve_date is not null  and call_req.close_date >= call_req.resolve_date) THEN      
             (call_req.close_date - call_req.resolve_date)/60/60/24 WHEN (call_req.close_date is null and call_req.resolve_date is not null) THEN (datediff(ss, '01/01/1970 00:00:00', GETUTCDATE())- call_req.open_date)/60/60/24   end AS "Resolve To Close(Days)", 
             
            
               
          
                          /*Datediff (hour, call_req.open_date,call_req.resolve_date) as "Time To Resolve (Hours)",      
                          Datediff (day, call_req.resolve_date, call_req.close_date ) as "Resolve To Close (Days)",
                          Datediff (hour, call_req.resolve_date, call_req.close_date) as "Resolve To Close (Hours)",*/
         
         
          
             
           srv_desc.sym as "Service Type",
           sevrty.sym as "Severity",
           urgncy.sym as "Urgency", 
           prob_ctg.sym as "Category",
           
           net_res.resource_name as "Asset Name",
           gen_res.name as "Asset Class",
           resfam.name  as "Asset Family",
           
           service_net_res.resource_name as "Affected Service",
           service_gen_res.name as "Affected Service Class",
           service_resfam.name  as "Affected Service Family",
           ci_enterprise_service.business_impact as "Business Impact", 
           ci_enterprise_service.business_priority as "Business Priority", 
           
           impact.sym as "Impact", 
           
           cr_stat.sym as "Status",
           pri.sym as "Priority",
           interface.sym as "Created Via", 
           
           
           View_Group.last_name as "Group", 
           
           
           /*customer.last_name as "Customer Lastname",
           customer.first_name as "Customer Firstname",
           customer.middle_name as "Customer Middlename", */
           customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ')  as "Customer", 
         --  customer.last_name + coalesce( ', ' +customer.first_name ,' ')+ coalesce(' ' + customer.middle_name, ' ')  as "Supervisior", 
          sup.last_name + coalesce( ', ' +sup.first_name ,' ')+ coalesce(' ' + sup.middle_name, ' ')  as "supervisior",
           customer_loc.location_name as       "Customer Location", 
           customer_org.org_name        as     "Customer Organization", 
           customer_admin.org_name      as "Customer Admin Organization",
           
           /*assignee.last_name as "Assignee Lastname", 
           assignee.first_name as "Assignee Firstname", 
           assignee.middle_name as "Assignee Middlename",*/
             assignee.last_name + coalesce( ', ' + assignee.first_name , ' ')+ coalesce (' ' + assignee.middle_name,' ') as "Assignee",
           assignee_loc.location_name as       "Assignee Location", 
           assignee_org.org_name        as     "Assignee Organization", 
           assignee_admin.org_name as   "Assignee Admin Organization",
           
           /*log_agent.last_name as "Log Agent Lastname", 
           log_agent.first_name as "Log Agent Firstname", 
           log_agent.middle_name as "Log Agent Middlename",*/
           log_agent.last_name + coalesce( ', ' + log_agent.first_name , ' ')+ coalesce (' ' + log_agent.middle_name,' ') as "Log Agent",          
           log_agent_loc.location_name as      "Log Agent Location",        
           log_agent_org.org_name       as     "Log Agent Organization", 
           log_agent_admin.org_name as  "Log Agent Admin Organization",
           
           /*last_mod_by.last_name as "Analyst Last Modified Lastname", 
           last_mod_by.first_name as "Analyst Last Modified Firstname", 
           last_mod_by.middle_name as "Analyst Last Modified Middlename",*/
           last_mod_by.last_name + coalesce( ', ' + last_mod_by.first_name , ' ')+ coalesce (' ' + last_mod_by.middle_name,' ') as "Analyst Last Modified",
           last_mod_by_loc.location_name as    "Analyst Last Modified Location",    
           last_mod_by_org.org_name     as     "Analyst Last Modified Organization", 
           last_mod_by_admin.org_name as       "Analyst Last Modified Admin Organization",
       CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds') as  [Total Elapsed Time]
    
    FROM (
           (
                  (      
                          (
                                 (
                                        (
                                               (
                                                      (
                                                              (      
                                                                     (
                                                                            (
                                                                                   (
                                                                                           (
                                                                                                  (
                                                                                                         (
                                                                                                                (
                                                                                                                       (
                                                                                                                               (
                                                                                                                                      (
                                                                                                                                             (
                                                                                                                                                    (
                                                                                                                                                            (
                                                                                                                                                                   (
                                                                                                                                                                          (
                                                                                                                                                                                 (
                                                                                                                                                                                        (
                                                                                                                                                                                                (
    (      
           (
                  (
                          (
                                 (
                                        (
                                               (
                                                      (
                                                              (
                                                                     (
                                                                            (
                                                                                   (
                                                                                           (      
                                                                                                                                                                                                
    (  call_req 
    left join RootCause ON call_req.rootcause = rootcause.id)
    left join call_req Problem ON call_req.problem = Problem.persid)
    left join call_req Parent ON call_req.problem = Parent.persid)
    left join Chg Change ON call_req.change = Change.id)
    left join usp_resolution_code res_code ON call_req.resolution_code= res_code.id)
    left join usp_resolution_method res_method ON call_req.resolution_method= res_method.id)
    left join usp_symptom_code symptom ON call_req.symptom_code= symptom.id)
    left join Chg caused_by_chg ON call_req.change = caused_by_chg.id)
    inner join  pri ON call_req.priority = pri.enum) 
    inner join  ca_contact customer ON call_req.customer =customer.contact_uuid) 
    left join ca_resource_department on ca_resource_department.id=customer.department
    left join ca_resource_cost_center on ca_resource_cost_center.id=customer.cost_center
    left join ca_location on ca_location.location_uuid=customer.location_uuid
    left join ca_site on ca_location.site_id=ca_site.id
    left join  ca_contact sup on sup.contact_uuid=customer.supervisor_contact_uuid
    left join 
    (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY call_req_id ORDER BY last_mod_dt DESC) AS Seq
    FROM act_log 
    where (act_log.type like 'ST' or act_log.type like 'INIT' or act_log.type like 'Log' or act_log.type like 'RE') and act_log.description not like '%Status changed%'
    and act_log.description not like '%Workflow task%' and  act_log.description not like '%AHD%' and  act_log.description not like '%create%'
       )al on al.call_req_id= call_req.persid and al.Seq = 1
    
    
    left join Final f on f.call_req_id=call_req.persid
    left join  srv_desc ON call_req.support_lev = srv_desc.code) 
    left join  sevrty ON call_req.severity = sevrty.enum) 
    left join  urgncy ON call_req.urgency = urgncy.enum) 
    left join  impact ON call_req.impact = impact.enum)
    
    left join  ca_owned_resource net_res ON call_req.affected_rc = net_res.own_resource_uuid)
    left join  ca_resource_class gen_res ON net_res.resource_class = gen_res.id) 
    left join  ca_resource_family resfam ON gen_res.family_id = resfam.id)
    
    left join  ca_owned_resource service_net_res ON call_req.affected_service = service_net_res.own_resource_uuid)
    left join  ci_enterprise_service ON call_req.affected_service = ci_enterprise_service.own_resource_uuid)
    left join  ca_resource_class service_gen_res ON service_net_res.resource_class = service_gen_res.id) 
    left join  ca_resource_family service_resfam ON service_gen_res.family_id = service_resfam.id)
    
           
    left join  prob_ctg ON call_req.category = prob_ctg.persid) 
    left join  prob_ctg init_cat ON call_req.zcategory_init = init_cat.persid) 
    
    left join  View_Group ON call_req.group_id = View_Group.contact_uuid) 
    left join  ca_contact assignee ON call_req.assignee = assignee.contact_uuid)
    left join  ca_contact log_agent ON call_req.log_agent = log_agent.contact_uuid)
    left join  ca_contact last_mod_by ON call_req.log_agent = last_mod_by.contact_uuid)
    left join  cr_stat ON call_req.status = cr_stat.code)
    
    left join  Interface ON call_req.created_via = Interface.id)
    
           left join  ca_organization   customer_org ON  customer.organization_uuid = customer_org.organization_uuid) 
           left join  ca_organization   customer_admin ON customer.admin_organization_uuid = customer_admin.organization_uuid)
           left join  ca_location              customer_loc ON customer.location_uuid =     customer_loc.location_uuid) 
           
           
           left join  ca_organization   assignee_org ON  assignee.organization_uuid = assignee_org.organization_uuid) 
           left join  ca_organization   assignee_admin ON assignee.admin_organization_uuid = assignee_admin.organization_uuid)
           left join  ca_location              assignee_loc ON assignee.location_uuid =     assignee_loc.location_uuid) 
           
           left join  ca_organization   log_agent_org ON  log_agent.organization_uuid = log_agent_org.organization_uuid) 
           left join  ca_organization   log_agent_admin ON log_agent.admin_organization_uuid =        log_agent_admin.organization_uuid)
           left join  ca_location              log_agent_loc ON log_agent.location_uuid =    log_agent_loc.location_uuid) 
           
           left join  ca_organization   last_mod_by_org ON  last_mod_by.organization_uuid = last_mod_by_org.organization_uuid) 
           left join  ca_organization   last_mod_by_admin ON last_mod_by.admin_organization_uuid =        last_mod_by_admin.organization_uuid)
           left join  ca_location              last_mod_by_loc ON last_mod_by.location_uuid = last_mod_by_loc.location_uuid) 
           
           where call_req.template_name is null 


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by du00805 Thursday, March 7, 2019 1:01 PM
    Thursday, March 7, 2019 12:50 PM
  • Thanks Visakh It worked out
    Thursday, March 7, 2019 1:01 PM
  • Hi Visakh,

    Below is the detail for creating table and inserting samples values to get the result. Hope it will be ok if not revert me back.

    create table test
    (
    [id] [int] NOT NULL,
    [persid] [nvarchar](30) NULL,
    [call_req_id] [nvarchar](30) NULL,
    [last_mod_dt] [int] NULL,[action_desc] [ntext] NULL,
    [type] [nvarchar](30) NULL,)

    insert into test
    (
    [id] ,
    [persid] ,
    [call_req_id] ,
    [last_mod_dt] ,[action_desc] ,
    [type] )

    values
    (2347937, 'alg:2347937', 'cr:416284', 1551851682, 'Status changed from ''Open'' to ''Acknowledged','ST'),
    (2347938, 'alg:2347938', 'cr:416284', 1551851696, 'Status changed from ''Acknowledged'' to ''Awaiting Vendor','ST'),
    (2347939, 'alg:2347939', 'cr:416284', 1551851697, 'The Service Type(s) on a ticket were delayed','SLADELAY'),
    (2347940, 'alg:2347940', 'cr:416284', 1551851985, 'Status changed from ''Awaiting Vendor'' to ''In Progress','ST'),
    (2347941, 'alg:2347941', 'cr:416284', 1551851986, 'The Service Type(s) on the ticket were resumed','SLARESUME'),
    (2347942, 'alg:2347942', 'cr:416284', 1551852330, 'Status changed from ''In Progress'' to ''Awaiting Vendor','ST'),
    (2347943, 'alg:2347943', 'cr:416284', 1551852331, 'The Service Type(s) on a ticket were delayed','SLADELAY'),
    (2347944, 'alg:2347944', 'cr:416284', 1551852331, 'Heat of the ticket has changed','HEAT_CHANGED'),
    (2347946, 'alg:2347946', 'cr:416284', 1551852735, 'Status changed from ''Awaiting Vendor'' to ''Resolved','RE'),
    (2347947, 'alg:2347947', 'cr:416284', 1551852736, 'The Service Type(s) on the ticket were resumed','SLARESUME'),
    (2347948, 'alg:2347948', 'cr:416284', 1551852736, 'Heat of the ticket has changed','HEAT_CHANGED')

    Thursday, March 7, 2019 4:31 PM
  • Hi Visakh,

    I have provided the create table and insert values data in my last comments. Also you can convert the last_mod_dt using DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as 'Status Date'


    Friday, March 8, 2019 10:17 AM
  • HI Visakh,

    Are you able to create to table and find auto the time difference between last_mod_dt.

    Sunday, March 10, 2019 10:08 AM
  • Hi Visakh,

    I have provided the create table and insert values data in my last comments. Also you can convert the last_mod_dt using DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as 'Status Date'


    so what should be your final result for the data above?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, March 10, 2019 10:32 AM
  • Final Result will be around 7 mins. As it will only count from 7 AM to 6 PM Business hours excluding Friday and Saturday. 

    Where :

    [Days Hrs Mins Sec] : from 'Awaiting Vendor'  - to 'Awaiting Vendor'  whereas if there are multiple status of from 'Awaiting Vendor' and to 'Awaiting Vendor' then it will take the sum. 

    Sunday, March 10, 2019 11:47 AM
  • Final Result will be around 7 mins. As it will only count from 7 AM to 6 PM Business hours excluding Friday and Saturday. 

    Where :

    [Days Hrs Mins Sec] : from 'Awaiting Vendor'  - to 'Awaiting Vendor'  whereas if there are multiple status of from 'Awaiting Vendor' and to 'Awaiting Vendor' then it will take the sum. 

    do you mean like this?

    create table test_time
    (
    [id] [int] NOT NULL,
    [persid] [nvarchar](30) NULL,
    [call_req_id] [nvarchar](30) NULL,
    [last_mod_dt] datetime NULL,[action_desc] [ntext] NULL,
    [type] [nvarchar](30) NULL,)
    
    insert into test_time
    (
    [id] ,
    [persid] ,
    [call_req_id] ,
    [last_mod_dt] ,[action_desc] ,
    [type] )
    
    values
    (2347937, 'alg:2347937', 'cr:416284', 1551851682, 'Status changed from ''Open'' to ''Acknowledged','ST'),
    (2347938, 'alg:2347938', 'cr:416284', 1551851696, 'Status changed from ''Acknowledged'' to ''Awaiting Vendor','ST'),
    (2347939, 'alg:2347939', 'cr:416284', 1551851697, 'The Service Type(s) on a ticket were delayed','SLADELAY'),
    (2347940, 'alg:2347940', 'cr:416284', 1551851985, 'Status changed from ''Awaiting Vendor'' to ''In Progress','ST'),
    (2347941, 'alg:2347941', 'cr:416284', 1551851986, 'The Service Type(s) on the ticket were resumed','SLARESUME'),
    (2347942, 'alg:2347942', 'cr:416284', 1551852330, 'Status changed from ''In Progress'' to ''Awaiting Vendor','ST'),
    (2347943, 'alg:2347943', 'cr:416284', 1551852331, 'The Service Type(s) on a ticket were delayed','SLADELAY'),
    (2347944, 'alg:2347944', 'cr:416284', 1551852331, 'Heat of the ticket has changed','HEAT_CHANGED'),
    (2347946, 'alg:2347946', 'cr:416284', 1551852735, 'Status changed from ''Awaiting Vendor'' to ''Resolved','RE'),
    (2347947, 'alg:2347947', 'cr:416284', 1551852736, 'The Service Type(s) on the ticket were resumed','SLARESUME'),
    (2347948, 'alg:2347948', 'cr:416284', 1551852736, 'Heat of the ticket has changed','HEAT_CHANGED')
    
    select call_req_id,
    sum(case when cat = 1 then -last_mod_dt when cat = 2 then last_mod_dt  end) as diff
    from
    (
    select *,row_number() over (partition by call_req_id,case when [action_desc] LIKE '%from ''Awaiting Vendor%' then 1 else 2 end order by last_mod_dt) as seq,case when [action_desc] LIKE '%from ''Awaiting Vendor%' then 1 else 2 end as cat
    from test_time
    where [action_desc] LIKE '%from ''Awaiting Vendor%'
    or [action_desc] LIKE '%to ''Awaiting Vendor%'
    )t
    group by call_req_id,seq
    
    
    

    In your test data the value for last_mod_dt  for to awaiting vendor is always before from awaiting vendor so value would be -ive when you do to - from


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, March 10, 2019 12:54 PM
  • I am not able to understand the query you have changed.

    Can you do modification in the below query provided by your earlier :

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    action_desc,
    type,
    row_number() over (partition by call_req_id,type order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101')) AS Seq
     from test1
      --where call_req_id = 'cr:410416'
    ),Final
    AS
    (

    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.BusinessHours(MAX(CASE WHEN PATINDEX('%from ''Awaiting Vendor''%', action_desc) >  0  THEN [Status Date] END),MAX(CASE WHEN PATINDEX('%to ''Awaiting Vendor''%', action_desc) > 0
    THEN [Status Date] END),'09:00','17:00') AS BusinessTime

    FROM CTE
    WHERE PATINDEX('%from ''Awaiting Vendor''%', action_desc) >  0 or PATINDEX('%to ''Awaiting Vendor''%', action_desc) > 0 
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )

    SELECT
    CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds') as [Total Elapsed Time]
    FROM Final
    • Edited by du00805 Sunday, March 10, 2019 1:49 PM
    Sunday, March 10, 2019 1:39 PM
  • Hi Visakh,

    Have you checked my requirement ?

    Monday, March 11, 2019 6:00 AM
  • Hi Visakh,

    Have you checked my requirement ?

    do you mean this?

    ;With CTE
    AS
    (
    select call_req_id, 
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    action_desc,
    type,
    row_number() over (partition by call_req_id,
    case when [action_desc] LIKE '%from ''Awaiting Vendor%' then 1 else 2 end order by DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') ) as seq,
    case when [action_desc] LIKE '%from ''Awaiting Vendor%' then 1 else 2 end as cat
    from test1
      --where call_req_id = 'cr:410416'
    ),Final
    AS
    (
    
    SELECT call_req_id,
    SUM(BusinessTime)  AS TotalBusinessTime
    FROM
    (
    SELECT call_req_id,
    dbo.BusinessHours(MAX(CASE WHEN cat = 1 THEN [Status Date] END),MAX(CASE WHEN cat = 2 THEN [Status Date] END),'09:00','17:00') AS BusinessTime
    FROM CTE
    where [action_desc] LIKE '%from ''Awaiting Vendor%'
    or [action_desc] LIKE '%to ''Awaiting Vendor%'
    GROUP BY call_req_id,Seq
    )t
    GROUP BY call_req_id
    )
    SELECT
    CONCAT(DATEDIFF(dd,0,DATEADD(ss,TotalBusinessTime,0)), ' Days ', 
    LEFT(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),2),' Hours ' ,
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),4,2),' Minutes ',
    SUBSTRING(CONVERT(varchar(8),DATEADD(ss,TotalBusinessTime,0),108),7,2),' Seconds') as [Total Elapsed Time]
    FROM Final


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, March 11, 2019 6:53 AM
  • Hi Visakh,

    Yes but if you execute the query like :

    select
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    * from test1

    you can see the difference between time 3/6/19 7:12 AM - 3/6/19 7:05 AM is in minutes. 

    Status Date id persid call_req_id last_mod_dt action_desc
    3/6/19 7:05 AM 2347942 alg:2347942 cr:416284 1551852330 Status changed from 'In Progress' to 'Awaiting Vendor
    3/6/19 7:05 AM 2347943 alg:2347943 cr:416284 1551852331 The Service Type(s) on a ticket were delayed
    3/6/19 7:05 AM 2347944 alg:2347944 cr:416284 1551852331 Heat of the ticket has changed
    3/6/19 7:12 AM 2347946 alg:2347946 cr:416284 1551852735 Status changed from 'Awaiting Vendor' to 'Resolved

    But the  query shared by you giving difference in  hours  where you can select the time from 07:00 to 18:00 Like below but your above query executing calculating the time which is from 09:00 to 17:00. By this time there should be no data to be calculated. 

    Do let me know if you have any doubt. 

    Monday, March 11, 2019 7:28 AM
  • Hi Visakh,

    Yes but if you execute the query like :

    select
    DATEADD(ss, last_mod_dt +DATEPART(TZ,SYSDATETIMEOFFSET())*60, '19700101') as "Status Date",
    * from test1

    you can see the difference between time 3/6/19 7:12 AM - 3/6/19 7:05 AM is in minutes. 

    Status Date id persid call_req_id last_mod_dt action_desc
    3/6/19 7:05 AM 2347942 alg:2347942 cr:416284 1551852330 Status changed from 'In Progress' to 'Awaiting Vendor
    3/6/19 7:05 AM 2347943 alg:2347943 cr:416284 1551852331 The Service Type(s) on a ticket were delayed
    3/6/19 7:05 AM 2347944 alg:2347944 cr:416284 1551852331 Heat of the ticket has changed
    3/6/19 7:12 AM 2347946 alg:2347946 cr:416284 1551852735 Status changed from 'Awaiting Vendor' to 'Resolved

    But the  query shared by you giving difference in  hours  where you can select the time from 07:00 to 18:00 Like below but your above query executing calculating the time which is from 09:00 to 17:00. By this time there should be no data to be calculated. 

    Do let me know if you have any doubt. 

    Did you create the UDF as per my modified version above or from the link?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, March 11, 2019 7:33 AM
  • I only created UDF as provided by you not from the link shared by you. 
    Monday, March 11, 2019 7:36 AM
  • Here is the Function I am using shared by you :

    Function

    -------------

    CREATE FUNCTION [dbo].[BusinessHours]
    (
    @StartDate datetime, --start of period of consideration for calculation of business hours
    @EndDate datetime, --end of period of consideration for calculation of business hours
    @BusinessStart datetime, --start of business hours
    @BusinessEnd datetime --end of business hours
    )
    RETURNS bigint
    AS
    BEGIN
    DECLARE @TotalSeconds bigint
    IF DATEDIFF(dd,0,@StartDate)=DATEDIFF(dd,0,@EndDate)
    BEGIN
     IF @BusinessStart>@BusinessEnd
     BEGIN
      SET @TotalSeconds= -1
     END
     ELSE
    BEGIN
    SELECT @BusinessStart=CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessEnd
    THEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) ELSE @BusinessStart END,
    @BusinessEnd =CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessStart AND DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessEnd
    THEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) ELSE @BusinessEnd END
      SET @TotalSeconds= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd OR DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart
          THEN 0
    ELSE DATEDIFF(ss,@BusinessStart,@BusinessEnd)
    END
    END
    END
    ELSE
    BEGIN
     SELECT @StartDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),1))
     WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessStart),DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))
       ELSE @StartDate
       END,
      @EndDate= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),-1))
     WHEN DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) > @BusinessEnd THEN DATEADD(ss,DATEDIFF(ss,0,@BusinessEnd),DATEADD(dd,DATEDIFF(dd,0,@EndDate),0))
       ELSE @EndDate
       END
      ;With Calendar_CTE (Date,Day,WeekDay)
     AS
     (
     SELECT DATEADD(dd,DATEDIFF(dd,0,@StartDate),0),DATENAME(dw,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)), CASE WHEN DATEDIFF(dd,0,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))%7  NOT IN (4,5) THEN 1 ELSE 0 END
      UNION ALL
      SELECT DATEADD(dd,1,Date),DATENAME(dw,DATEADD(dd,1,Date)), CASE WHEN DATEDIFF(dd,0,DATEADD(dd,1,Date))%7 NOT IN (4,5) THEN 1 ELSE 0 END
     FROM Calendar_CTE
     WHERE DATEADD(dd,1,Date) <= @EndDate
     )

      SELECT @TotalSeconds=CEILING(SUM(
       CASE WHEN DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(ss,@StartDate,@EndDate)
    WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(ss,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
       WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(ss,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
       ELSE DATEDIFF(ss,@BusinessStart,@BusinessEnd)
       END
       ))
     FROM Calendar_CTE c
     LEFT JOIN Holiday h
     ON h.[date]= c.Date
     WHERE WeekDay=1
     AND h.[date] IS NULL
     OPTION (MAXRECURSION 0)
    END
    RETURN @TotalSeconds

    ------------------------

    Monday, March 11, 2019 7:50 AM
  • hi visakh,

    Do you have any other question on my requirement???

    Monday, March 11, 2019 9:25 AM
  • HI Visakh,

    are you able to check it ???


    • Edited by du00805 Monday, March 11, 2019 12:36 PM
    Monday, March 11, 2019 11:12 AM
  • Hi Visakh,

    I am waiting for your response. 

    Monday, March 11, 2019 12:38 PM
  • Hi Viskah,

    Are you able to look into the query ?

    Tuesday, March 12, 2019 5:31 AM