locked
String Based date time calculation RRS feed

  • Question

  • In My past post on Date time calculation I have created the function using the link https://visakhm.blogspot.com/2010/03/calculating-business-hours.html

    And have created the query for Vendor Elapsed Time from my previous post :

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3ea4a4aa-8e46-4da0-9ba8-5b9f075e846d/calculate-date-time-excluding-weekend?forum=transactsql

    ;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

    Now I have  criteria where I 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 column. 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 :

    to 'Awaiting Vendor' is starting point

    from 'Awaiting Vendor' is Ending Point

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

    I have tried to update the below query highlighted in bold but not getting actual results. Please Advise me on 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 description like '%to Awaiting Vendor%' THEN [Status Date] END),MAX(CASE WHEN description like '%from Awaiting Vendor%' 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 [ Only Vendor Elapsed Time]
    FROM Final


    • Edited by du00805 Wednesday, March 6, 2019 8:23 AM
    Wednesday, March 6, 2019 8:22 AM

All replies

  • Is this requirement feasible ?
    Wednesday, March 6, 2019 9:32 AM
  • Is this requirement feasible ?

    The thread is duplicate of https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3ea4a4aa-8e46-4da0-9ba8-5b9f075e846d/calculate-date-time-excluding-weekend?forum=transactsql

    Please not open the thread of the same question again.

    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.

    Thursday, March 7, 2019 8:54 AM
    Moderator
  • sure. Thanks for let me know. 
    Thursday, March 7, 2019 8:57 AM
  • sure. Thanks for let me know. 

    OK. This is the dead line of the thread. Please don't respond again.

    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.

    Friday, March 8, 2019 7:42 AM
    Moderator