none
Convert Days Hours Mins into Business Hours RRS feed

  • Question

  • I have a function of Business Hours. How Can I put that function in my SQL Query below to provide the Days Hours and Mins for

    1. [Time To Complete] 2. [Time Spent With ITSU] 3. [Time Spent With Network] as per dbo.BusinessHours ?

    Business Hours 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
    END

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

    Query

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




    ;WITH cteData
    AS (
    SELECT Request_ID,
    DATEDIFF(SECOND, MIN(CASE WHEN Status = 501 THEN Status_Date ELSE NULL END), MAX(CASE WHEN Status = 902 THEN Status_Date ELSE NULL END)) AS ITSU,
    DATEDIFF(SECOND, MIN(CASE WHEN Status = 508 THEN Status_Date ELSE NULL END), MAX(CASE WHEN Status = 990 THEN Status_Date ELSE NULL END)) AS Network
    FROM usm_request_status where subscription_detail_id is not null  
    GROUP BY Request_ID
    )



    SELECT
      usm_request2.request_id,
      convert(date,usm_request2.created_date) as CreationDate,
      convert(date,usm_request2.completion_date) as CompletionDate,
      case usm_request2.status  when 2 then 'Completed'when 3 then 'Pending Cancellation'when 4 then 'Canceled'
    when 6 then 'Pending Resource Assignment'when 7 then 'Resource Assigned'when 100 then 'Not Submitted'when 101 then 'Not Submitted -Cart' when 102 then 'Not Submitted - Pending Approval' when 103 then 'Not Submitted - Rejected' when 104 then 'Not Submitted - Approved' 
    when 200 then 'Submitted' when 201 then 'Re-Submitted' when 400 then 'Pending Approval'
    when 600 then 'Rejected' 
    when 501 then 'Pending ITSU Review'
    when 502 then 'Pending ITSU Approval'
    when 503 then 'Pending Implementation'
    when 504 then 'Pending FW Rule Implementation'
    when 505 then 'Pending USS Review'
    when 506 then 'Pending USS Approval'
    when 507 then 'Pending USS Assignment'
    when 508 then 'Pending IP Reservation'
    when 509 then 'Pending FSO Approval'
    when 511 then 'Pending DG Approval'
    when 800 then 'Approved'
    when 801 then 'Approval Not Needed'
    when 900 then 'Approve'
    when 901 then 'Reviewed'
    when 902 then 'Approvea'
    when 903 then 'Implemented'
    when 904 then 'Reviewed'
    when 905 then 'Approves'
    when 906 then 'Assigned'
    when 907 then 'Reject Investigation'
    when 908 then 'IP Reservation Status Determined'
    when 909 then 'FSO Approved'
    when 911 then 'Approved'
    when 989 then 'Approve'
    when 990 then 'Completed'
    when 999 then 'Approval Done'
    when 1000 then 'Pending Fulfillment'
    when 1001 then 'Check Availability'
    when 1002 then 'Filled From Inventory'
    when 1003 then 'Not Filled From Inventory'
    when 1004 then 'Ordered'
    when 1005 then 'Backordered'
    when 1006 then 'Shipped'
    when 1007 then 'Received'
    when 1008 then 'Order Canceled'
    when 1012 then 'Pending Procurement'
    when 1013 then 'USD Change Order Opened'
    when 1014 then 'Notified Facilities'
    when 1015 then 'Notified IT Services'
    when 1016 then 'Being Staged'
    when 1017 then 'Staged'
    when 1018 then 'Being Configured'
    when 1019 then 'Configured'
    when 1020 then 'USD Request Opened'
    when 1999 then 'Fulfillment Canceled'
    when 2000 then  'Fulfilled'
    when 1 then  'Pending'
    when 3000 then 'Hold'
    when 4000 then 'Resumed'
    end as Status,
      case when (Requested_By22.first_name='' or Requested_By22.first_name is null) then Requested_By22.last_name else ({fn concat(Requested_By22.last_name,{fn concat(', ',Requested_By22.first_name)})}) end as RequestedBy,
      case when (Requested_For22.first_name='' or Requested_For22.first_name is null) then Requested_For22.last_name else ({fn concat(Requested_For22.last_name,{fn concat(', ',Requested_For22.first_name)})}) end as RequestedFor,
      usm_request2.priority,
      usm_request2.name as OfferingName,
    CAST(FLOOR(DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) / 1440) AS VARCHAR(10))+ 'day'
    + CAST(FLOOR(( DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) % 1440 ) / 60) AS VARCHAR(10))+ 'hour'
    + CAST(FLOOR(( ( DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) )
    - ( FLOOR(DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) / 1440)* 1440 ) 
    - ( FLOOR(( DATEDIFF(MINUTE, usm_request2.created_date,usm_request2.completion_date) % 1440 )
    / 60) * 60 ) ))+ CASE WHEN DATEPART(SECOND, usm_request2.created_date) > DATEPART(SECOND, usm_request2.completion_date) 
    THEN -1
    ELSE 0
    END AS VARCHAR(10)) + 'minute'  [Time To Complete],

    Convert(varchar(5),DateDiff(day,'1900-01-01',ITSU/86400)) + ' day ' +Format(dateadd(second, ITSU,'1970-01-01'),  'H \hour mm \minu\te') AS [Time Spent With ITSU],
    Convert(varchar(5),DateDiff(day,'1900-01-01',Network/86400)) + ' day ' +Format(dateadd(second, Network,'1970-01-01'),  'H \hour mm \minu\te') AS [Time Spent With Network]


     FROM
       ca_contact  Requested_For22 
       INNER JOIN usm_request  usm_request2 ON (usm_request2.req_for_user_id=Requested_For22.userid)
       INNER JOIN ca_contact  Requested_By22 ON (usm_request2.req_by_user_id=Requested_By22.userid)
       inner join cteData  on cteData.request_id=usm_request2.request_id
      
       

    Sunday, April 14, 2019 1:11 PM

Answers

  • Hi du00805,

     

    Please try my newest script above , and it will get a correct result.

     

    declare @created_date datetime ='2019-04-16 11:09:03.000'
    
             
    
    declare @completion_date datetime='2019-04-16 11:17:19.000'
    
    select
    
    CAST(FLOOR(DATEDIFF(MINUTE, @created_date, @completion_date) / 1440) AS VARCHAR(10))+ 'day'
    
    + CAST(FLOOR(( DATEDIFF(MINUTE, @created_date, @completion_date) % 1440 ) / 60) AS VARCHAR(10))+ 'hour'
    
    + CAST(FLOOR(( ( DATEDIFF(MINUTE, @created_date, @completion_date) )
    
    - ( FLOOR(DATEDIFF(MINUTE, @created_date, @completion_date) / 1440)* 1440 )
    
    - ( FLOOR(( DATEDIFF(MINUTE, @created_date,@completion_date) % 1440 )
    
    / 60) * 60 ) ))+ CASE WHEN DATEPART(SECOND, @created_date) > DATEPART(SECOND, @completion_date)
    
    THEN -1
    
    ELSE 0
    
    END AS VARCHAR(10)) + 'minute'  [Time To Complete]
    
    /*
    
    Time To Complete
    
    -------------------------------------------
    
    0day0hour8minute
    
    */
    
     


     

    Hope it can help you.

     

    Best Regards,

    Rachel


    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.

    • Marked as answer by du00805 Thursday, April 18, 2019 10:08 AM
    Thursday, April 18, 2019 9:57 AM

All replies

  • Good day,

    Your question is not fully clear to me (probably it was too long to deeply read). Can you post a simpler case which include ONLY the core question which you have?

    Do you know how to use Scalar Functions as part of queries in general, or do you need help in this?

    Note! Unless you are using SQL Server 2019 with the new feature of INLINE scalar function, you should re-think about your design! In first glance it seems like you want to use scalar function multiple times in order to get several values for each row. You should probably think about using TABLE FUNCTION in this case


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, April 15, 2019 12:36 AM
    Moderator
  • Hi du00805,

     

    Thank you for your posting.

     

    Per your description , I am  afraid that I am not clear about your requirement. What problems do you have in the following code?  Could you  please share us your table structure and some sample data along with your expected result? So that we’ll get a right direction and make some test.

     

    By the way , would you like to convert your result into hours?  If so , please try following script.

     
    ----1day 3hours 30min 
    select cast(cast (1*60*1.0 + 3*1.0+30*1.0/60 as numeric(10,2))as varchar(20))+' hours' as [hours]
    /*
    hours
    --------------------------
    63.50 hours
    */

     

    Best Regards,

    Rachel


    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.

    Monday, April 15, 2019 2:02 AM
  • Thanks Rachel for the response. 

    No I do not want to convert it into hours. I want to exclude the weekend part in my calculation for 1. [Time To Complete] 2. [Time Spent With ITSU] 3. [Time Spent With Network].

    Where the function which I have shared with you exclude the weekend part which is Friday and Saturday. So will not calculate the Days Hours and Mins which fall under Friday and Saturday. 

    But all I want to know how can I call DBO.BusinessHours (Scalar Function) in the Query posted above. So that the Out put of 1. [Time To Complete] 2. [Time Spent With ITSU] 3. [Time Spent With Network] will come according to the dbo.Businesshours function. 

    Monday, April 15, 2019 7:18 AM
  • Thanks Ronen,

    Yes I need help how to call scalar function in the query. Like the above shared scalar function you can use in your exisiting query to calculate Days Hours and Mins. So the shared scalar function will exclude the Friday and Saturday and will count rest of the days in calculation. Similar way in my above posted query how can I call this scalar function nemed dbo.businesshours so that the out put of 1. [Time To Complete] 2. [Time Spent With ITSU] 3. [Time Spent With Network]. will come as per defined scalar function. 

    Monday, April 15, 2019 7:20 AM
  • Hi Rachel,

    I am sharing one more scalar function named "fn_GetTotalWorkingDays" which elaborate to exclude Friday and Saturday. And Only count weekdays. So how can I use this function to get the output of my query posted above for the columns 1. [Time To Complete] 2. [Time Spent With ITSU] 3. [Time Spent With Network].

    CREATE FUNCTION [dbo].[fn_GetTotalWorkingDays]
    (
        @DateFrom Date,
        @DateTo Date
    )
    RETURNS INT
    AS
    BEGIN
        DECLARE @TotDays INT= DATEDIFF(DAY, @DateFrom, @DateTo) + 1;
        DECLARE @TotWeeks INT= DATEDIFF(WEEK, @DateFrom, @DateTo) * 2;
        DECLARE @IsFriday INT= CASE
    WHEN DATENAME(WEEKDAY, @DateFrom) = 'Friday'
    THEN 1
    ELSE 0
      END;
        DECLARE @IsSaturday INT= CASE
       WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday'
       THEN 1
       ELSE 0
        END;
        DECLARE @TotWorkingDays INT= @TotDays - @TotWeeks - @IsFriday + @IsSaturday;
        RETURN @TotWorkingDays;
    END

    Monday, April 15, 2019 10:17 AM
  • All I want to Exclude Friday and Saturday and need to counts Days Hours Mins on fall under working days which is [Sunday,Monday,Tuesday,Wednesday and Thursday) for 1. [Time To Complete] 2. [Time Spent With ITSU] 3. [Time Spent With Network].

    Do let me know how to achieve it either calling scalar function or can do in same below query :

    ;WITH cteData
    AS (
    SELECT  Request_ID,
    DATEDIFF(SECOND, MIN(CASE WHEN Status = 501 THEN Status_Date ELSE NULL END), MAX(CASE WHEN Status = 902 THEN Status_Date ELSE NULL END)) AS ITSU,
    DATEDIFF(SECOND, MIN(CASE WHEN Status = 508 THEN Status_Date ELSE NULL END), MAX(CASE WHEN Status = 990 THEN Status_Date ELSE NULL END)) AS Network
    FROM usm_request_status where subscription_detail_id is not null  
    GROUP BY  Request_ID
    )



    SELECT
      usm_request2.request_id,
      convert(date,usm_request2.created_date) as CreationDate,
      convert(date,usm_request2.completion_date) as CompletionDate,
      case usm_request2.status  when 2 then 'Completed'when 3 then 'Pending Cancellation'when 4 then 'Canceled'
    when 6 then 'Pending Resource Assignment'when 7 then 'Resource Assigned'when 100 then 'Not Submitted'when 101 then 'Not Submitted -Cart' when 102 then 'Not Submitted - Pending Approval' when 103 then 'Not Submitted - Rejected' when 104 then 'Not Submitted - Approved' 
    when 200 then 'Submitted' when 201 then 'Re-Submitted' when 400 then 'Pending Approval'
    when 600 then 'Rejected' 
    when 501 then 'Pending ITSU Review'
    when 502 then 'Pending ITSU Approval'
    when 503 then 'Pending Implementation'
    when 504 then 'Pending FW Rule Implementation'
    when 505 then 'Pending USS Review'
    when 506 then 'Pending USS Approval'
    when 507 then 'Pending USS Assignment'
    when 508 then 'Pending IP Reservation'
    when 509 then 'Pending FSO Approval'
    when 511 then 'Pending DG Approval'
    when 800 then 'Approved'
    when 801 then 'Approval Not Needed'
    when 900 then 'Approve'
    when 901 then 'Reviewed'
    when 902 then 'Approvea'
    when 903 then 'Implemented'
    when 904 then 'Reviewed'
    when 905 then 'Approves'
    when 906 then 'Assigned'
    when 907 then 'Reject Investigation'
    when 908 then 'IP Reservation Status Determined'
    when 909 then 'FSO Approved'
    when 911 then 'Approved'
    when 989 then 'Approve'
    when 990 then 'Completed'
    when 999 then 'Approval Done'
    when 1000 then 'Pending Fulfillment'
    when 1001 then 'Check Availability'
    when 1002 then 'Filled From Inventory'
    when 1003 then 'Not Filled From Inventory'
    when 1004 then 'Ordered'
    when 1005 then 'Backordered'
    when 1006 then 'Shipped'
    when 1007 then 'Received'
    when 1008 then 'Order Canceled'
    when 1012 then 'Pending Procurement'
    when 1013 then 'USD Change Order Opened'
    when 1014 then 'Notified Facilities'
    when 1015 then 'Notified IT Services'
    when 1016 then 'Being Staged'
    when 1017 then 'Staged'
    when 1018 then 'Being Configured'
    when 1019 then 'Configured'
    when 1020 then 'USD Request Opened'
    when 1999 then 'Fulfillment Canceled'
    when 2000 then  'Fulfilled'
    when 1 then  'Pending'
    when 3000 then 'Hold'
    when 4000 then 'Resumed'
    end as Status,
      case when (Requested_By22.first_name='' or Requested_By22.first_name is null) then Requested_By22.last_name else ({fn concat(Requested_By22.last_name,{fn concat(', ',Requested_By22.first_name)})}) end as RequestedBy,
      case when (Requested_For22.first_name='' or Requested_For22.first_name is null) then Requested_For22.last_name else ({fn concat(Requested_For22.last_name,{fn concat(', ',Requested_For22.first_name)})}) end as RequestedFor,
      usm_request2.priority,
      usm_request2.name as OfferingName,
    CAST(FLOOR(DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) / 1440) AS VARCHAR(10))+ 'day'
    + CAST(FLOOR(( DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) % 1440 ) / 60) AS VARCHAR(10))+ 'hour'
    + CAST(FLOOR(( ( DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) )
    - ( FLOOR(DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) / 1440)* 1440 ) 
    - ( FLOOR(( DATEDIFF(MINUTE, usm_request2.created_date,usm_request2.completion_date) % 1440 )
    / 60) * 60 ) ))+ CASE WHEN DATEPART(SECOND, usm_request2.created_date) > DATEPART(SECOND, usm_request2.completion_date) 
    THEN -1
    ELSE 0
    END AS VARCHAR(10)) + 'minute'  [Time To Complete],

    Convert(varchar(5),DateDiff(day,'1900-01-01',ITSU/86400)) + ' day ' +Format(dateadd(second, ITSU,'1970-01-01'),  'H \hour mm \minu\te') AS [Time Spent With ITSU],
    Convert(varchar(5),DateDiff(day,'1900-01-01',Network/86400)) + ' day ' +Format(dateadd(second, Network,'1970-01-01'),  'H \hour mm \minu\te') AS [Time Spent With Network]


     FROM
       ca_contact  Requested_For22 
       INNER JOIN usm_request  usm_request2 ON (usm_request2.req_for_user_id=Requested_For22.userid)
       INNER JOIN ca_contact  Requested_By22 ON (usm_request2.req_by_user_id=Requested_By22.userid)
       inner join cteData  on cteData.request_id=usm_request2.request_id
      
       

    Monday, April 15, 2019 11:18 AM
  • Can anyone advise me on my query ?
    Monday, April 15, 2019 7:53 PM
  • Thanks Phillips. I am new to MS SQL so require more guidance. 
    Monday, April 15, 2019 7:54 PM
  • Hi du00805,

     

    Thank you for your reply.

     

    Did you mean that the values for DateFrom and DateTo are in your original table and you would like to calculate the time ? Could you  please share us your table structure and some sample data along with your expected result? So that we’ll get a right direction and make some test.

     

    Best Regards,

    Rachel


    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, April 16, 2019 5:58 AM
  • Thanks Rachel for the response.

    Well here is the sample data

    CREATE TABLE [dbo].[usm_request_status](
    [request_status_id] [int] NOT NULL,
    [request_id] [int] NOT NULL,
    [subscription_detail_id] [int] NULL,
    [status] [int] NOT NULL,
    [status_date] [datetime] NOT NULL,
    [user_id] [nvarchar](128) NULL,
    [status_old] [int] NULL,
    [login_device] [int] NOT NULL,

    INSERT dbo.[usm_request_status](
        [request_status_id]
        ,[request_id]
        ,[subscription_detail_id]
        ,[status]
        ,[status_date]
        ,[user_id]
        ,[status_old]
        ,[login_device]
    )
    VALUES
    (47522,11986,NULL ,101 ,'1/28/19 10:00 AM','CBKUD9939', -1,1),
    (47523,11986,16027 ,101 ,'1/28/19 10:00 AM','CBKUD9939', -1,1),
    (47524,11986,16027 ,200 ,'1/28/19 10:00 AM','CBKUD9939', 101,1),
    (47525,11986,NULL ,200 ,'1/28/19 10:00 AM','CBKUD9939', 101,1),
    (47526,11986,16027 ,500 ,'1/28/19 10:00 AM','caadmin'  , 200,3),
    (47527,11986,NULL ,500 ,'1/28/19 10:00 AM','caadmin'  , 200,3),
    (47762,11986,16027 ,501 ,'1/30/19 04:05 PM','caadmin'  , 900,3),
    (47763,11986,NULL ,501 ,'1/30/19 04:05 PM','caadmin'  , 900,3),
    (47909,11986,16027 ,502 ,'1/31/19 10:13 AM','caadmin'  , 901,3),
    (47910,11986,NULL ,502 ,'1/31/19 10:13 AM','caadmin'  , 901,3),
    (48134,11986,16027 ,504 ,'2/05/19 04:47 AM','caadmin'  , 908,3),
    (48135,11986,NULL ,504 ,'2/05/19 04:48 AM','caadmin'  , 908,3),
    (48112,11986,16027 ,508 ,'2/04/19 11:36 AM','caadmin'  , 902,3),
    (48113,11986,NULL ,508 ,'2/04/19 11:36 AM','caadmin'  , 902,3),
    (47760,11986,16027 ,900 ,'1/30/19 07:05 AM','CBKUD2006', 500,1),
    (47761,11986,NULL ,900 ,'1/30/19 07:05 AM','CBKUD2006', 500,1),
    (47907,11986,16027 ,901 ,'1/31/19 10:13 AM','CBKUD2210', 501,1),
    (47908,11986,NULL ,901 ,'1/31/19 10:13 AM','CBKUD2210', 501,1),
    (48110,11986,16027 ,902 ,'2/01/19 11:36 AM','CBKUD1845', 502,1),
    (48111,11986,NULL ,902 ,'2/01/19 11:36 AM','CBKUD1845', 502,1),
    (48132,11986,16027 ,908 ,'2/02/19 04:47 AM','CBKUD2375', 508,1),
    (48133,11986,NULL ,908 ,'2/02/19 04:47 AM','CBKUD2375', 508,1),
    (48359,11986,16027 ,990 ,'2/06/19 11:46 AM','CBKUD9939', 504,1),
    (48360,11986,NULL ,990 ,'2/06/19 11:46 AM','CBKUD9939', 504,1)
    GO

    And below is the short query

    WITH cteData
    AS (
    SELECT
     Request_ID,
    DATEDIFF(SECOND, MIN(CASE WHEN Status = 501 THEN Status_Date ELSE NULL END), MAX(CASE WHEN Status = 902 THEN Status_Date ELSE NULL END)) AS ITSU,
    DATEDIFF(SECOND, MIN(CASE WHEN Status = 508 THEN Status_Date ELSE NULL END), MAX(CASE WHEN Status = 990 THEN Status_Date ELSE NULL END)) AS Network
    FROM
    usm_request_status where subscription_detail_id is not null
    GROUP BY
     Request_ID
    )
    SELECT
    Request_ID,

    Convert(varchar(5),DateDiff(day,'1900-01-01',ITSU/86400)) + ' day '+Format(dateadd(second, ITSU,'1970-01-01'),  'H \hour mm \minu\te')  as ITSU,
    Convert(varchar(5),DateDiff(day,'1900-01-01',Network/86400)) + ' day '+Format(dateadd(second, Network,'1970-01-01'),  'H \hour mm \minu\te')  as Network

    FROM
    cteData;

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

    Now you will get the data but I want that status_date will exclude entry of Friday and Saturday highlighted in above table values. Plus it will only calculate time from 7 AM to 3 PM only.

    Do let me know if this will make you clear to work on this. And if this will workout will merge into the main query posted very first by me. 

    Tuesday, April 16, 2019 7:15 AM
  • Hi du00805,

     

    Would you like this one ?

     
    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 int
    AS
    BEGIN
    DECLARE @TotalHours int
    IF DATEDIFF(dd,0,@StartDate)=DATEDIFF(dd,0,@EndDate)
    BEGIN
     IF @BusinessStart>@BusinessEnd
     BEGIN
      SET @TotalHours= -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 @TotalHours= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd OR DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart
          THEN 0
    ELSE DATEDIFF(mi,@BusinessStart,@BusinessEnd)/60
    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 < 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 < 5 THEN 1 ELSE 0 END
     FROM Calendar_CTE
     WHERE DATEADD(dd,1,Date) <= @EndDate
     )
    
      SELECT @TotalHours=CEILING(SUM(
       CASE WHEN DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@StartDate,@EndDate)
            WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(MINUTE,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
            WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
       ELSE DATEDIFF(MINUTE,@BusinessStart,@BusinessEnd)
       END
       )*60.0)
     FROM Calendar_CTE c
     WHERE WeekDay=1
     OPTION (MAXRECURSION 0)
    END
    RETURN @TotalHours
    END
    
    go
    
    
    If Object_ID('usm_request_status','U') Is Not Null Drop Table usm_request_status
    go
    CREATE TABLE [dbo].[usm_request_status](
    [request_status_id] [int] NOT NULL,
    [request_id] [int] NOT NULL,
    [subscription_detail_id] [int] NULL,
    [status] [int] NOT NULL,
    [status_date] [datetime] NOT NULL,
    [user_id] [nvarchar](128) NULL,
    [status_old] [int] NULL,
    [login_device] [int] NOT NULL)
    
    INSERT into dbo.[usm_request_status](
        [request_status_id]
        ,[request_id]
        ,[subscription_detail_id]
        ,[status]
        ,[status_date]
        ,[user_id]
        ,[status_old]
        ,[login_device]
    )
    VALUES
    (47522,11986,NULL ,101,'1/28/19 10:00 AM','CBKUD9939', -1,1),
    (47523,11986,16027 ,101,'1/28/19 10:00 AM','CBKUD9939', -1,1),
    (47524,11986,16027 ,200,'1/28/19 10:00 AM','CBKUD9939', 101,1),
    (47525,11986,NULL ,200,'1/28/19 10:00 AM','CBKUD9939', 101,1),
    (47526,11986,16027 ,500,'1/28/19 10:00 AM','caadmin'  ,  200,3),
    (47527,11986,NULL ,500,'1/28/19 10:00 AM','caadmin'  ,  200,3),
    (47762,11986,16027 ,501,'1/30/19 04:05 PM','caadmin'  ,900,3),
    (47763,11986,NULL ,501,'1/30/19 04:05 PM','caadmin'  ,900,3),
    (47909,11986,16027 ,502,'1/31/19 10:13 AM','caadmin'  ,  901,3),
    (47910,11986,NULL ,502,'1/31/19 10:13 AM','caadmin'  ,  901,3),
    (48134,11986,16027 ,504,'2/05/19 04:47 AM','caadmin'  ,  908,3),
    (48135,11986,NULL ,504,'2/05/19 04:48 AM','caadmin'  ,  908,3),
    (48112,11986,16027 ,508,'2/04/19 11:36 AM','caadmin'  ,  902,3),
    (48113,11986,NULL ,508,'2/04/19 11:36 AM','caadmin'  ,  902,3),
    (47760,11986,16027 ,900,'1/30/19 07:05 AM','CBKUD2006', 500,1),
    (47761,11986,NULL ,900,'1/30/19 07:05 AM','CBKUD2006', 500,1),
    (47907,11986,16027 ,901,'1/31/19 10:13 AM','CBKUD2210', 501,1),
    (47908,11986,NULL ,901,'1/31/19 10:13 AM','CBKUD2210', 501,1),
    (48110,11986,16027 ,902,'2/01/19 11:36 AM','CBKUD1845',502,1),
    (48111,11986,NULL ,902,'2/01/19 11:36 AM','CBKUD1845',502,1),
    (48132,11986,16027 ,908,'2/02/19 04:47 AM','CBKUD2375',508,1),
    (48133,11986,NULL ,908,'2/02/19 04:47 AM','CBKUD2375',508,1),
    (48359,11986,16027 ,990,'2/06/19 11:46 AM','CBKUD9939', 504,1),
    (48360,11986,NULL ,990,'2/06/19 11:46 AM','CBKUD9939', 504,1)
    GO
    
    ;WITH cteData
    AS (
    SELECT
     Request_ID,
    dbo.BusinessHours(MIN(CASE WHEN Status = 501 THEN Status_Date ELSE NULL END),MAX(CASE WHEN Status = 902 THEN Status_Date ELSE NULL END),'07:00','17:00') AS ITSU,
    dbo.BusinessHours(MIN(CASE WHEN Status = 508 THEN Status_Date ELSE NULL END),MAX(CASE WHEN Status = 990 THEN Status_Date ELSE NULL END),'07:00','17:00') AS Network
    FROM
    usm_request_status where subscription_detail_id is not null
    GROUP BY
     Request_ID
    )
    SELECT
    Request_ID,
    Convert(varchar(5),DateDiff(day,'1900-01-01',ITSU/86400)) + ' day '+Format(dateadd(second, ITSU,'1970-01-01'),  'H \hour mm \minu\te')  as ITSU,
    Convert(varchar(5),DateDiff(day,'1900-01-01',Network/86400)) + ' day '+Format(dateadd(second, Network,'1970-01-01'),  'H \hour mm \minu\te')  as Network
    FROM
    cteData;
    /*
    Request_ID  ITSU                             Network
    ----------- -------------------------------- --------------------------------
    11986       0 day 15 hour 31 minute          0 day 20 hour 10 minute
    */
    

    Best Regards,

    Rachel


    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, April 16, 2019 7:50 AM
  • Thanks rachel. But removing Datediff  from the query is giving  inappropriate results.

    Tuesday, April 16, 2019 8:07 AM
  • Hi du00805,

     

    According to my understanding , you would like to calculate the time difference between two date and it just counts working time(from Sunday to Thursday and from 07:00 to 17:00). Diff is used to calculate the time difference and my function  also do that . I am sorry that I am not clear to understand your requirement.

     

    Could you please share us your logic and expected result clearly?

     

    Best Regards,

    Rachel


    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, April 16, 2019 8:32 AM
  • Hi Rachel,

    Thanks for the prompt response. Yes I want time difference but within the scenario of (from Sunday to Thursday and from 07:00 to 17:00). I hope this will clear ?

    Tuesday, April 16, 2019 8:39 AM
  • Hi du00805,

     

    Would you like this one ?

     
    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 int
    AS
    BEGIN
    DECLARE @TotalHours int
    IF DATEDIFF(dd,0,@StartDate)=DATEDIFF(dd,0,@EndDate)
    BEGIN
     IF @BusinessStart>@BusinessEnd
     BEGIN
      SET @TotalHours= -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 @TotalHours= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd OR DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart
          THEN 0
    ELSE DATEDIFF(mi,@BusinessStart,@BusinessEnd)/60
    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 < 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 < 5 THEN 1 ELSE 0 END
     FROM Calendar_CTE
     WHERE DATEADD(dd,1,Date) <= @EndDate
     )
    
      SELECT @TotalHours=CEILING(SUM(
       CASE WHEN DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@StartDate,@EndDate)
            WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(MINUTE,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
            WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
       ELSE DATEDIFF(MINUTE,@BusinessStart,@BusinessEnd)
       END
       )*60.0)
     FROM Calendar_CTE c
     WHERE WeekDay=1
     OPTION (MAXRECURSION 0)
    END
    RETURN @TotalHours
    END
    
    go
    
    
    If Object_ID('usm_request_status','U') Is Not Null Drop Table usm_request_status
    go
    CREATE TABLE [dbo].[usm_request_status](
    [request_status_id] [int] NOT NULL,
    [request_id] [int] NOT NULL,
    [subscription_detail_id] [int] NULL,
    [status] [int] NOT NULL,
    [status_date] [datetime] NOT NULL,
    [user_id] [nvarchar](128) NULL,
    [status_old] [int] NULL,
    [login_device] [int] NOT NULL)
    
    INSERT into dbo.[usm_request_status](
        [request_status_id]
        ,[request_id]
        ,[subscription_detail_id]
        ,[status]
        ,[status_date]
        ,[user_id]
        ,[status_old]
        ,[login_device]
    )
    VALUES
    (47522,11986,NULL ,101,'1/28/19 10:00 AM','CBKUD9939', -1,1),
    (47523,11986,16027 ,101,'1/28/19 10:00 AM','CBKUD9939', -1,1),
    (47524,11986,16027 ,200,'1/28/19 10:00 AM','CBKUD9939', 101,1),
    (47525,11986,NULL ,200,'1/28/19 10:00 AM','CBKUD9939', 101,1),
    (47526,11986,16027 ,500,'1/28/19 10:00 AM','caadmin'  ,  200,3),
    (47527,11986,NULL ,500,'1/28/19 10:00 AM','caadmin'  ,  200,3),
    (47762,11986,16027 ,501,'1/30/19 04:05 PM','caadmin'  ,900,3),
    (47763,11986,NULL ,501,'1/30/19 04:05 PM','caadmin'  ,900,3),
    (47909,11986,16027 ,502,'1/31/19 10:13 AM','caadmin'  ,  901,3),
    (47910,11986,NULL ,502,'1/31/19 10:13 AM','caadmin'  ,  901,3),
    (48134,11986,16027 ,504,'2/05/19 04:47 AM','caadmin'  ,  908,3),
    (48135,11986,NULL ,504,'2/05/19 04:48 AM','caadmin'  ,  908,3),
    (48112,11986,16027 ,508,'2/04/19 11:36 AM','caadmin'  ,  902,3),
    (48113,11986,NULL ,508,'2/04/19 11:36 AM','caadmin'  ,  902,3),
    (47760,11986,16027 ,900,'1/30/19 07:05 AM','CBKUD2006', 500,1),
    (47761,11986,NULL ,900,'1/30/19 07:05 AM','CBKUD2006', 500,1),
    (47907,11986,16027 ,901,'1/31/19 10:13 AM','CBKUD2210', 501,1),
    (47908,11986,NULL ,901,'1/31/19 10:13 AM','CBKUD2210', 501,1),
    (48110,11986,16027 ,902,'2/01/19 11:36 AM','CBKUD1845',502,1),
    (48111,11986,NULL ,902,'2/01/19 11:36 AM','CBKUD1845',502,1),
    (48132,11986,16027 ,908,'2/02/19 04:47 AM','CBKUD2375',508,1),
    (48133,11986,NULL ,908,'2/02/19 04:47 AM','CBKUD2375',508,1),
    (48359,11986,16027 ,990,'2/06/19 11:46 AM','CBKUD9939', 504,1),
    (48360,11986,NULL ,990,'2/06/19 11:46 AM','CBKUD9939', 504,1)
    GO
    
    ;WITH cteData
    AS (
    SELECT
     Request_ID,
    dbo.BusinessHours(MIN(CASE WHEN Status = 501 THEN Status_Date ELSE NULL END),MAX(CASE WHEN Status = 902 THEN Status_Date ELSE NULL END),'07:00','17:00') AS ITSU,
    dbo.BusinessHours(MIN(CASE WHEN Status = 508 THEN Status_Date ELSE NULL END),MAX(CASE WHEN Status = 990 THEN Status_Date ELSE NULL END),'07:00','17:00') AS Network
    FROM
    usm_request_status where subscription_detail_id is not null
    GROUP BY
     Request_ID
    )
    SELECT
    Request_ID,
    Convert(varchar(5),DateDiff(day,'1900-01-01',ITSU/86400)) + ' day '+Format(dateadd(second, ITSU,'1970-01-01'),  'H \hour mm \minu\te')  as ITSU,
    Convert(varchar(5),DateDiff(day,'1900-01-01',Network/86400)) + ' day '+Format(dateadd(second, Network,'1970-01-01'),  'H \hour mm \minu\te')  as Network
    FROM
    cteData;
    /*
    Request_ID  ITSU                             Network
    ----------- -------------------------------- --------------------------------
    11986       0 day 15 hour 31 minute          0 day 20 hour 10 minute
    */

    Best Regards,

    Rachel


    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.

    Hi ,

    Yes, I think I understand currently and I do this in my above script.Now I am not sure which is your expected result? Could you please share us ?

    Best Regards,

    Rachel


    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, April 16, 2019 8:47 AM
  • Thanks Rachel,

    Please delete the old values and insert new values as

    VALUES
    (21107, 10637, 13650, 2, '2019-04-16 11:17:19.000', 'spadmin', 2000, 1),
    (21108, 10637, NULL, 2, '2019-04-16 11:17:19.000', 'spadmin', 2000, 1),
    (21096, 10637, NULL, 101, '2019-04-16 11:09:03.000', 'jarnold', -1, 1)
    (21097, 10637, 13650, 101, '2019-04-16 11:09:03.000', 'jarnold', -1, 1)
    (21098, 10637, 13650, 200, '2019-04-16 11:09:03.000', 'jarnold', 101, 1)
    (21099, 10637, NULL, 200, '2019-04-16 11:09:03.000', 'jarnold', 101, 1)
    (21100, 10637, 13650, 400, '2019-04-16 11:09:03.000', 'jarnold', 200, 1)
    (21101, 10637, NULL, 400, '2019-04-16 11:09:04.000', 'jarnold', 200, 1)
    (21103, 10637, 13650, 400, '2019-04-16 11:14:43.000', 'rjones', 800, 1)
    (21102, 10637, 13650, 800, '2019-04-16 11:14:43.000', 'rjones', 400, 1)
    (21104, 10637, 13650, 2000, '2019-04-16 11:17:19.000', 'spadmin', 999, 1)
    (21105, 10637, 13650, 2000, '2019-04-16 11:17:19.000', 'spadmin', 999, 1)
    (21106, 10637, NULL, 2000, '2019-04-16 11:17:19.000', 'spadmin', 400, 1)

    From the above values I am expecting the result of ITSU as 0 day 0 hour 05 minute

    Tuesday, April 16, 2019 11:35 AM

  • DATEDIFF(SECOND, MIN(CASE WHEN Status = 501 THEN Status_Date ELSE NULL END), MAX(CASE WHEN Status = 902 THEN Status_Date ELSE NULL END)) AS ITSU,
    DATEDIFF(SECOND, MIN(CASE WHEN Status = 508 THEN Status_Date ELSE NULL END), MAX(CASE WHEN Status = 990 THEN Status_Date ELSE NULL END)) AS Network

     

    Hi du00805,

     

    Please check your above script in in your original post . When you calculate the value of the column ITSU and Network, you make the value of the column 'Status' be equal to 508 or 990.  But in your data ,  none of the records met the condition. So the result will show NULL.

     

    Could you please share us the logic about how to calculate  ITSU and Network?

     

    Best Regards,

    Rachel


    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.

    Wednesday, April 17, 2019 7:13 AM
  • Thanks Rachel,

    I know the Values for status is changed. And Curretly I am not able to provide the Values for Network. You can only check for ITSU and lastly logic will be same for ITSU and Network. So,If you get the correct results for ITSU then it will imply same for the Network too. So the above Values provided by me will get the result of ITSU. But the Values provided in status for ITSU is changed so you can update like :

    DATEDIFF(SECOND, MIN(CASE WHEN Status = 400 THEN Status_Date ELSE NULL END), MAX(CASE WHEN Status = 800 THEN Status_Date ELSE NULL END)) AS ITSU,
    DATEDIFF(SECOND, MIN(CASE WHEN Status = 508 THEN Status_Date ELSE NULL END), MAX(CASE WHEN Status = 990 THEN Status_Date ELSE NULL END)) AS Network

     

    Wednesday, April 17, 2019 7:30 AM
  •  Hi du00805,

     

    I am sorry that I have little mistake in my original function . And  I bolded the part I modified, please try following script.

     
    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 int
    AS
    BEGIN
    DECLARE @TotalMin int
    IF DATEDIFF(dd,0,@StartDate)=DATEDIFF(dd,0,@EndDate)
    BEGIN
     IF @BusinessStart>@BusinessEnd
     BEGIN
      SET @TotalMin= -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 @TotalMin= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd OR DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart
          THEN 0
    ELSE DATEDIFF(mi,@BusinessStart,@BusinessEnd)*60
    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 < 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 < 5 THEN 1 ELSE 0 END
     FROM Calendar_CTE
     WHERE DATEADD(dd,1,Date) <= @EndDate
     )
    
      SELECT @TotalMin=CEILING(SUM(
       CASE WHEN DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@StartDate,@EndDate)
            WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(MINUTE,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
            WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
       ELSE DATEDIFF(MINUTE,@BusinessStart,@BusinessEnd)
       END
       )*60.0)
     FROM Calendar_CTE c
     WHERE WeekDay=1
     OPTION (MAXRECURSION 0)
    
    END
    RETURN @TotalMin
    END
    go
    
    
    If Object_ID('usm_request_status','U') Is Not Null Drop Table usm_request_status
    go
    CREATE TABLE [dbo].[usm_request_status](
    [request_status_id] [int] NOT NULL,
    [request_id] [int] NOT NULL,
    [subscription_detail_id] [int] NULL,
    [status] [int] NOT NULL,
    [status_date] [datetime] NOT NULL,
    [user_id] [nvarchar](128) NULL,
    [status_old] [int] NULL,
    [login_device] [int] NOT NULL)
    
    INSERT into dbo.[usm_request_status](
        [request_status_id]
        ,[request_id]
        ,[subscription_detail_id]
        ,[status]
        ,[status_date]
        ,[user_id]
        ,[status_old]
        ,[login_device]
    )
    VALUES
    (21107, 10637,13650, 2,'2019-04-16 11:17:19.000', 'spadmin',2000, 1),
    (21108, 10637,NULL, 2,'2019-04-16 11:17:19.000', 'spadmin',2000, 1),
    (21096, 10637,NULL, 101,'2019-04-16 11:09:03.000', 'jarnold',-1, 1),
    (21097, 10637,13650, 101,'2019-04-16 11:09:03.000', 'jarnold',-1, 1),
    (21098, 10637,13650, 200,'2019-04-16 11:09:03.000', 'jarnold',101, 1),
    (21099, 10637,NULL, 200,'2019-04-16 11:09:03.000', 'jarnold',101, 1),
    (21100, 10637,13650, 400,'2019-04-16 11:09:03.000', 'jarnold',200, 1),
    (21101, 10637,NULL, 400,'2019-04-16 11:09:04.000', 'jarnold',200, 1),
    (21103, 10637,13650, 400,'2019-04-16 11:14:43.000', 'rjones',800, 1),
    (21102, 10637,13650, 800,'2019-04-16 11:14:43.000', 'rjones',400, 1),
    (21104, 10637,13650, 2000,'2019-04-16 11:17:19.000', 'spadmin',999, 1),
    (21105, 10637,13650, 2000,'2019-04-16 11:17:19.000', 'spadmin',999, 1),
    (21106, 10637,NULL, 2000,'2019-04-16 11:17:19.000', 'spadmin',400, 1)
    GO
    
    ;WITH cteData
    AS (
    SELECT
     Request_ID,
    dbo.BusinessHours(MIN(CASE WHEN Status = 400 THEN Status_Date ELSE NULL END),MAX(CASE WHEN Status = 800 THEN Status_Date ELSE NULL END),'07:00','17:00') AS ITSU,
    dbo.BusinessHours(MIN(CASE WHEN Status = 508 THEN Status_Date ELSE NULL END),MAX(CASE WHEN Status = 990 THEN Status_Date ELSE NULL END),'07:00','17:00') AS Network
    FROM
    usm_request_status where subscription_detail_id is not null
    GROUP BY
     Request_ID
    )
    SELECT
    Request_ID,
    Convert(varchar(5),DateDiff(day,'1900-01-01',ITSU/86400)) + ' day '+Format(dateadd(second, ITSU,'1970-01-01'),  'H \hour mm \minu\te')  as ITSU,
    Convert(varchar(5),DateDiff(day,'1900-01-01',Network/86400)) + ' day '+Format(dateadd(second, Network,'1970-01-01'),  'H \hour mm \minu\te')  as Network
    FROM
    cteData;
    /*
    Request_ID  ITSU                                                                                                                                                                                                                                                             Network
    ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    10637       0 day 0 hour 05 minute                                                                                                                                                                                                                                           NULL
    
    */

    Best Regards,

    Rachel


    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.


    Wednesday, April 17, 2019 7:59 AM
  • Thanks Rachel for the prompt response. It worked out thanks. But Now I have updated my actual query by calling business function dbo.BusinessHours. Now I  also have highlighted the Time To Complete. How can I call BusinessHours function for Time To Complete which is similar like you did for  ITSU and Network.




    ;WITH cteData
    AS (
    SELECT Request_ID,
    dbo.BusinessHours(MIN(CASE WHEN Status = 400 THEN Status_Date ELSE NULL END),MAX(CASE WHEN Status = 800 THEN Status_Date ELSE NULL END),'07:00','15:00') AS ITSU,
    dbo.BusinessHours(MIN(CASE WHEN Status = 508 THEN Status_Date ELSE NULL END),MAX(CASE WHEN Status = 990 THEN Status_Date ELSE NULL END),'07:00','15:00') AS Network
    FROM usm_request_status where subscription_detail_id is not null  
    GROUP BY Request_ID
    )



    SELECT
      usm_request2.request_id,
      convert(date,usm_request2.created_date) as CreationDate,
      convert(date,usm_request2.completion_date) as CompletionDate,
      case usm_request2.status  when 2 then 'Completed'when 3 then 'Pending Cancellation'when 4 then 'Canceled'
    when 6 then 'Pending Resource Assignment'when 7 then 'Resource Assigned'when 100 then 'Not Submitted'when 101 then 'Not Submitted -Cart' when 102 then 'Not Submitted - Pending Approval' when 103 then 'Not Submitted - Rejected' when 104 then 'Not Submitted - Approved' 
    when 200 then 'Submitted' when 201 then 'Re-Submitted' when 400 then 'Pending Approval'
    when 600 then 'Rejected' 
    when 501 then 'Pending ITSU Review'
    when 502 then 'Pending ITSU Approval'
    when 503 then 'Pending Implementation'
    when 504 then 'Pending FW Rule Implementation'
    when 505 then 'Pending USS Review'
    when 506 then 'Pending USS Approval'
    when 507 then 'Pending USS Assignment'
    when 508 then 'Pending IP Reservation'
    when 509 then 'Pending FSO Approval'
    when 511 then 'Pending DG Approval'
    when 800 then 'Approved'
    when 801 then 'Approval Not Needed'
    when 900 then 'Approve'
    when 901 then 'Reviewed'
    when 902 then 'Approvea'
    when 903 then 'Implemented'
    when 904 then 'Reviewed'
    when 905 then 'Approves'
    when 906 then 'Assigned'
    when 907 then 'Reject Investigation'
    when 908 then 'IP Reservation Status Determined'
    when 909 then 'FSO Approved'
    when 911 then 'Approved'
    when 989 then 'Approve'
    when 990 then 'Completed'
    when 999 then 'Approval Done'
    when 1000 then 'Pending Fulfillment'
    when 1001 then 'Check Availability'
    when 1002 then 'Filled From Inventory'
    when 1003 then 'Not Filled From Inventory'
    when 1004 then 'Ordered'
    when 1005 then 'Backordered'
    when 1006 then 'Shipped'
    when 1007 then 'Received'
    when 1008 then 'Order Canceled'
    when 1012 then 'Pending Procurement'
    when 1013 then 'USD Change Order Opened'
    when 1014 then 'Notified Facilities'
    when 1015 then 'Notified IT Services'
    when 1016 then 'Being Staged'
    when 1017 then 'Staged'
    when 1018 then 'Being Configured'
    when 1019 then 'Configured'
    when 1020 then 'USD Request Opened'
    when 1999 then 'Fulfillment Canceled'
    when 2000 then  'Fulfilled'
    when 1 then  'Pending'
    when 3000 then 'Hold'
    when 4000 then 'Resumed'
    end as Status,
      case when (Requested_By22.first_name='' or Requested_By22.first_name is null) then Requested_By22.last_name else ({fn concat(Requested_By22.last_name,{fn concat(', ',Requested_By22.first_name)})}) end as RequestedBy,
      case when (Requested_For22.first_name='' or Requested_For22.first_name is null) then Requested_For22.last_name else ({fn concat(Requested_For22.last_name,{fn concat(', ',Requested_For22.first_name)})}) end as RequestedFor,
      usm_request2.priority,
      usm_request2.name as OfferingName,
    CAST(FLOOR(DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) / 1440) AS VARCHAR(10))+ 'day'
    + CAST(FLOOR(( DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) % 1440 ) / 60) AS VARCHAR(10))+ 'hour'
    + CAST(FLOOR(( ( DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) )
    - ( FLOOR(DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) / 1440)* 1440 ) 
    - ( FLOOR(( DATEDIFF(MINUTE, usm_request2.created_date,usm_request2.completion_date) % 1440 )
    / 60) * 60 ) ))+ CASE WHEN DATEPART(SECOND, usm_request2.created_date) > DATEPART(SECOND, usm_request2.completion_date) 
    THEN -1
    ELSE 0
    END AS VARCHAR(10)) + 'minute'  [Time To Complete],

    Convert(varchar(5),DateDiff(day,'1900-01-01',ITSU/86400)) + ' day ' +Format(dateadd(second, ITSU,'1970-01-01'),  'H \hour mm \minu\te') AS [Time Spent With ITSU],
    Convert(varchar(5),DateDiff(day,'1900-01-01',Network/86400)) + ' day ' +Format(dateadd(second, Network,'1970-01-01'),  'H \hour mm \minu\te') AS [Time Spent With Network]


     FROM
       ca_contact  Requested_For22 
       INNER JOIN usm_request  usm_request2 ON (usm_request2.req_for_user_id=Requested_For22.userid)
       INNER JOIN ca_contact  Requested_By22 ON (usm_request2.req_by_user_id=Requested_By22.userid)
       inner join cteData  on cteData.request_id=usm_request2.request_id
      
       

    Wednesday, April 17, 2019 9:38 AM
  • Hi Rachel,

    Can you provide response as per my last comment on the query.

    Thanks.

    Thursday, April 18, 2019 4:23 AM
  • As per my Above post of query.

    How Can I call business function declared above in my post named "BusinessHours" for the below, so that [Time to Complete] will follow the "BusinessHours" function criteria. 

    CAST(FLOOR(DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) / 1440) AS VARCHAR(10))+ 'day'
    + CAST(FLOOR(( DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) % 1440 ) / 60) AS VARCHAR(10))+ 'hour'
    + CAST(FLOOR(( ( DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) )
    - ( FLOOR(DATEDIFF(MINUTE, usm_request2.created_date, usm_request2.completion_date) / 1440)* 1440 ) 
    - ( FLOOR(( DATEDIFF(MINUTE, usm_request2.created_date,usm_request2.completion_date) % 1440 )
    / 60) * 60 ) ))+ CASE WHEN DATEPART(SECOND, usm_request2.created_date) > DATEPART(SECOND, usm_request2.completion_date) 
    THEN -1
    ELSE 0
    END AS VARCHAR(10)) + 'minute'  [Time To Complete],

    Thursday, April 18, 2019 7:23 AM
  • Hi du00805,

     

    Sorry for my late reply, please try following script.

     
    CAST(FLOOR(dbo.BusinessHours(usm_request2.created_date, usm_request2.completion_date,'07:00','15:00')/ 1440) AS VARCHAR(10))+ 'day'
    + CAST(FLOOR(( dbo.BusinessHours(usm_request2.created_date, usm_request2.completion_date,'07:00','15:00') % 1440 ) / 60) AS VARCHAR(10))+ 'hour'
    + CAST(FLOOR(( ( dbo.BusinessHours( usm_request2.created_date, usm_request2.completion_date,'07:00','15:00') )
    - ( FLOOR(dbo.BusinessHours(usm_request2.created_date, usm_request2.completion_dat,'07:00','15:00') / 1440)* 1440 ) 
    - ( FLOOR(( dbo.BusinessHours(usm_request2.created_date,usm_request2.completion_date,'07:00','15:00') % 1440 )
    / 60) * 60 ) ))+ CASE WHEN DATEPART(SECOND, usm_request2.created_date) > DATEPART(SECOND, usm_request2.completion_date) 
    THEN -1
    ELSE 0
    END AS VARCHAR(10)) + 'minute'  [Time To Complete],
    

    Best Regards,

    Rachel


    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, April 18, 2019 7:44 AM
  • Thanks Rachel,

    I have applied the Business function but I am getting inaccurate results for that. I have enclosed the screenshot of my test results. Where first screenshot is without applying function and getting accurate 8 mins result. While Second screenshot is with applied function and getting results of 8 hours. Is there any thing missing ?

    Thursday, April 18, 2019 8:04 AM
  • Hi du00805,

     

    What is this function 'dbo.BusinessHoursup'? Before, we were talking about another function 'dbo.BusinessHours'. Could you please share us more information ?

     

    Best Regards,

    Rachel


    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, April 18, 2019 8:21 AM
  • Thanks Rachel for the prompt response. I just renamed the BusinessHours function name to BusinessHoursup. It is the same as you provided. 
    Thursday, April 18, 2019 8:27 AM
  • Hi du00805,

     

    Sorry for making mistakes and changing little for my function . In following script , I will provide the complete script for your first and second requirement.  Please try it.

       
    ---------dbo.BusinessHours
    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 int
    AS
    BEGIN
    DECLARE @TotalMin int
    IF DATEDIFF(dd,0,@StartDate)=DATEDIFF(dd,0,@EndDate)
    BEGIN
     IF @BusinessStart>@BusinessEnd
     BEGIN
      SET @TotalMin= -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 @TotalMin= CASE WHEN DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate) > @BusinessEnd OR DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate) < @BusinessStart
          THEN 0
    ELSE DATEDIFF(mi,@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 < 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 < 5 THEN 1 ELSE 0 END
     FROM Calendar_CTE
     WHERE DATEADD(dd,1,Date) <= @EndDate
     )
    
      SELECT @TotalMin=CEILING(SUM(
       CASE WHEN DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@StartDate,@EndDate)
            WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@StartDate),0) THEN DATEDIFF(MINUTE,DATEADD(dd,-DATEDIFF(dd,0,@StartDate),@StartDate),@BusinessEnd)
            WHEN c.Date=DATEADD(dd,DATEDIFF(dd,0,@EndDate),0) THEN DATEDIFF(MINUTE,@BusinessStart,DATEADD(dd,-DATEDIFF(dd,0,@EndDate),@EndDate))
       ELSE DATEDIFF(MINUTE,@BusinessStart,@BusinessEnd)
       END
       )*60.0)
     FROM Calendar_CTE c
     WHERE WeekDay=1
     OPTION (MAXRECURSION 0)
    
    END
    RETURN @TotalMin
    END
    go
    
    -------the first requirement
    If Object_ID('usm_request_status','U') Is Not Null Drop Table usm_request_status
    go
    CREATE TABLE [dbo].[usm_request_status](
    [request_status_id] [int] NOT NULL,
    [request_id] [int] NOT NULL,
    [subscription_detail_id] [int] NULL,
    [status] [int] NOT NULL,
    [status_date] [datetime] NOT NULL,
    [user_id] [nvarchar](128) NULL,
    [status_old] [int] NULL,
    [login_device] [int] NOT NULL)
    
    INSERT into dbo.[usm_request_status](
        [request_status_id]
        ,[request_id]
        ,[subscription_detail_id]
        ,[status]
        ,[status_date]
        ,[user_id]
        ,[status_old]
        ,[login_device]
    )
    VALUES
    (21107, 10637,13650, 2,'2019-04-16 11:17:19.000', 'spadmin',2000, 1),
    (21108, 10637,NULL, 2,'2019-04-16 11:17:19.000', 'spadmin',2000, 1),
    (21096, 10637,NULL, 101,'2019-04-16 11:09:03.000', 'jarnold',-1, 1),
    (21097, 10637,13650, 101,'2019-04-16 11:09:03.000', 'jarnold',-1, 1),
    (21098, 10637,13650, 200,'2019-04-16 11:09:03.000', 'jarnold',101, 1),
    (21099, 10637,NULL, 200,'2019-04-16 11:09:03.000', 'jarnold',101, 1),
    (21100, 10637,13650, 400,'2019-04-16 11:09:03.000', 'jarnold',200, 1),
    (21101, 10637,NULL, 400,'2019-04-16 11:09:04.000', 'jarnold',200, 1),
    (21103, 10637,13650, 400,'2019-04-16 11:14:43.000', 'rjones',800, 1),
    (21102, 10637,13650, 800,'2019-04-16 11:14:43.000', 'rjones',400, 1),
    (21104, 10637,13650, 2000,'2019-04-16 11:17:19.000', 'spadmin',999, 1),
    (21105, 10637,13650, 2000,'2019-04-16 11:17:19.000', 'spadmin',999, 1),
    (21106, 10637,NULL, 2000,'2019-04-16 11:17:19.000', 'spadmin',400, 1)
    GO
    
    ;WITH cteData
    AS (
    SELECT
     Request_ID,
    dbo.BusinessHours(MIN(CASE WHEN Status = 400 THEN Status_Date ELSE NULL END),MAX(CASE WHEN Status = 800 THEN Status_Date ELSE NULL END),'07:00','17:00') AS ITSU,
    dbo.BusinessHours(MIN(CASE WHEN Status = 508 THEN Status_Date ELSE NULL END),MAX(CASE WHEN Status = 990 THEN Status_Date ELSE NULL END),'07:00','17:00') AS Network
    FROM
    usm_request_status where subscription_detail_id is not null
    GROUP BY
     Request_ID
    )
    SELECT
    Request_ID,
    Convert(varchar(5),DateDiff(day,'1900-01-01',ITSU/1440)) + ' day '+Format(dateadd(mi, ITSU,'1970-01-01'),  'H \hour mm \minu\te')  as ITSU,
    Convert(varchar(5),DateDiff(day,'1900-01-01',Network/1440)) + ' day '+Format(dateadd(mi, Network,'1970-01-01'),  'H \hour mm \minu\te')  as Network
    FROM
    cteData;
    /*
    Request_ID  ITSU                              Network
    ----------- --------------------------------- -----------------------
    10637       0 day 0 hour 05 minute            NULL
    
    */
    
    
    -------the second requirement
    ;WITH cteData
    AS (
    SELECT  Request_ID,
    dbo.BusinessHours(MIN(CASE WHEN Status = 400 THEN Status_Date ELSE NULL END),MAX(CASE WHEN Status = 800 THEN Status_Date ELSE NULL END),'07:00','15:00') AS ITSU,
    dbo.BusinessHours(MIN(CASE WHEN Status = 508 THEN Status_Date ELSE NULL END),MAX(CASE WHEN Status = 990 THEN Status_Date ELSE NULL END),'07:00','15:00') AS Network
    FROM usm_request_status where subscription_detail_id is not null  
    GROUP BY  Request_ID
    )
    
    SELECT
      usm_request2.request_id,
      convert(date,usm_request2.created_date) as CreationDate,
      convert(date,usm_request2.completion_date) as CompletionDate,
      case usm_request2.status  when 2 then 'Completed'when 3 then 'Pending Cancellation'when 4 then 'Canceled'
    when 6 then 'Pending Resource Assignment'when 7 then 'Resource Assigned'when 100 then 'Not Submitted'when 101 then 'Not Submitted -Cart' when 102 then 'Not Submitted - Pending Approval' when 103 then 'Not Submitted - Rejected' when 104 then 'Not Submitted - Approved' 
    when 200 then 'Submitted' when 201 then 'Re-Submitted' when 400 then 'Pending Approval'
    when 600 then 'Rejected' 
    when 501 then 'Pending ITSU Review'
    when 502 then 'Pending ITSU Approval'
    when 503 then 'Pending Implementation'
    when 504 then 'Pending FW Rule Implementation'
    when 505 then 'Pending USS Review'
    when 506 then 'Pending USS Approval'
    when 507 then 'Pending USS Assignment'
    when 508 then 'Pending IP Reservation'
    when 509 then 'Pending FSO Approval'
    when 511 then 'Pending DG Approval'
    when 800 then 'Approved'
    when 801 then 'Approval Not Needed'
    when 900 then 'Approve'
    when 901 then 'Reviewed'
    when 902 then 'Approvea'
    when 903 then 'Implemented'
    when 904 then 'Reviewed'
    when 905 then 'Approves'
    when 906 then 'Assigned'
    when 907 then 'Reject Investigation'
    when 908 then 'IP Reservation Status Determined'
    when 909 then 'FSO Approved'
    when 911 then 'Approved'
    when 989 then 'Approve'
    when 990 then 'Completed'
    when 999 then 'Approval Done'
    when 1000 then 'Pending Fulfillment'
    when 1001 then 'Check Availability'
    when 1002 then 'Filled From Inventory'
    when 1003 then 'Not Filled From Inventory'
    when 1004 then 'Ordered'
    when 1005 then 'Backordered'
    when 1006 then 'Shipped'
    when 1007 then 'Received'
    when 1008 then 'Order Canceled'
    when 1012 then 'Pending Procurement'
    when 1013 then 'USD Change Order Opened'
    when 1014 then 'Notified Facilities'
    when 1015 then 'Notified IT Services'
    when 1016 then 'Being Staged'
    when 1017 then 'Staged'
    when 1018 then 'Being Configured'
    when 1019 then 'Configured'
    when 1020 then 'USD Request Opened'
    when 1999 then 'Fulfillment Canceled'
    when 2000 then  'Fulfilled'
    when 1 then  'Pending'
    when 3000 then 'Hold'
    when 4000 then 'Resumed'
    end as Status,
      case when (Requested_By22.first_name='' or Requested_By22.first_name is null) then Requested_By22.last_name else ({fn concat(Requested_By22.last_name,{fn concat(', ',Requested_By22.first_name)})}) end as RequestedBy,
      case when (Requested_For22.first_name='' or Requested_For22.first_name is null) then Requested_For22.last_name else ({fn concat(Requested_For22.last_name,{fn concat(', ',Requested_For22.first_name)})}) end as RequestedFor,
      usm_request2.priority,
      usm_request2.name as OfferingName,
    CAST(FLOOR(dbo.BusinessHours(usm_request2.created_date, usm_request2.completion_date,'07:00','15:00')/ 1440) AS VARCHAR(10))+ 'day'
    + CAST(FLOOR(( dbo.BusinessHours(usm_request2.created_date, usm_request2.completion_date,'07:00','15:00') % 1440 ) / 60) AS VARCHAR(10))+ 'hour'
    + CAST(FLOOR(( ( dbo.BusinessHours( usm_request2.created_date, usm_request2.completion_date,'07:00','15:00') )
    - ( FLOOR(dbo.BusinessHours(usm_request2.created_date, usm_request2.completion_dat,'07:00','15:00') / 1440)* 1440 ) 
    - ( FLOOR(( dbo.BusinessHours(usm_request2.created_date,usm_request2.completion_date,'07:00','15:00') % 1440 )
    / 60) * 60 ) ))+ CASE WHEN DATEPART(SECOND, usm_request2.created_date) > DATEPART(SECOND, usm_request2.completion_date) 
    THEN -1
    ELSE 0
    END AS VARCHAR(10)) + 'minute'  [Time To Complete],
    
    Convert(varchar(5),DateDiff(day,'1900-01-01',ITSU/1440)) + ' day ' +Format(dateadd(mi, ITSU,'1970-01-01'),  'H \hour mm \minu\te') AS [Time Spent With ITSU],
    Convert(varchar(5),DateDiff(day,'1900-01-01',Network/1440)) + ' day ' +Format(dateadd(mi, Network,'1970-01-01'),  'H \hour mm \minu\te') AS [Time Spent With Network]
    
    
     FROM
       ca_contact  Requested_For22 
       INNER JOIN usm_request  usm_request2 ON (usm_request2.req_for_user_id=Requested_For22.userid)
       INNER JOIN ca_contact  Requested_By22 ON (usm_request2.req_by_user_id=Requested_By22.userid)
       inner join cteData  on cteData.request_id=usm_request2.request_id
    
    
    
    Hope it can help you.

     

    Best Regards,

    Rachel


    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, April 18, 2019 9:42 AM
  • HI Rachel,

    YOu can test he Total Time Spent from the below

    1, Create table

    2. Insert Value

    3. Execute query to check with BusinessFunction Created.

    CREATE TABLE [dbo].[usm_request](
    [request_id] [int] NOT NULL,
    [name] [nvarchar](128) NOT NULL,
    [status] [int] NOT NULL,
    [created_date] [datetime] NOT NULL CONSTRAINT [DF__usm_reque__creat__222BD200]  DEFAULT ('today'),
    [modified_date] [datetime] NOT NULL CONSTRAINT [DF__usm_reque__modif__231FF639]  DEFAULT ('today'),
    [completion_date] [datetime] NULL,
    [desired_date] [datetime] NULL,
    [comments] [ntext] NULL,
    [priority] [int] NULL,
    [req_for_account_id] [varchar](50) NULL,
    [req_by_account_id] [varchar](50) NULL,
    [req_for_user_id] [nvarchar](100) NULL,
    [req_by_user_id] [nvarchar](100) NULL,
    [code] [nvarchar](64) NULL,
    [domain] [varchar](50) NULL,
    [location_uuid] [binary](16) NULL,
    [context_type] [int] NOT NULL DEFAULT ('0'),
    [source] [tinyint] NULL,
    [locale] [varchar](50) NULL,
    [req_for_uuid] [binary](16) NULL,
    [req_by_uuid] [binary](16) NULL)
    INSERT INTO [dbo].[usm_request]
               ([request_id]
               ,[name]
               ,[status]
               ,[created_date]
               ,[modified_date]
               ,[completion_date]
               ,[desired_date]
               ,[comments]
               ,[priority]
               ,[req_for_account_id]
               ,[req_by_account_id]
               ,[req_for_user_id]
               ,[req_by_user_id]
               ,[code]
               ,[domain]
               ,[location_uuid]
               ,[context_type]
               ,[source]
               ,[locale]
               ,[req_for_uuid]
               ,[req_by_uuid])
         VALUES
               (10637
               ,'Printer'
               ,2
               ,'2019-04-16 11:09:03.000'
               ,'2019-04-16 11:17:19.000'
               ,'2019-04-16 11:17:19.000'
               ,'NULL'
               ,'NULL'
               ,3
               ,'10001'
               ,'10001'
               ,'jarnold'
               ,'jarnold'
               ,'NULL'
               ,'North America Financial'
               ,'NULL'
               ,0
               ,'NULL'
               ,'icusen'
               ,0x6960E7585E874840866044013676FC75
               ,0x6960E7585E874840866044013676FC75)
    GO

    ---

    Query

    --------

    select request_id,created_date, completion_date,
    CAST(FLOOR(DATEDIFF(MINUTE, usm_request.created_date, usm_request.completion_date) / 1440) AS VARCHAR(10))+ 'day'
    + CAST(FLOOR(( DATEDIFF(MINUTE, usm_request.created_date, usm_request.completion_date) % 1440 ) / 60) AS VARCHAR(10))+ 'hour'
    + CAST(FLOOR(( ( DATEDIFF(MINUTE, usm_request.created_date, usm_request.completion_date) )
    - ( FLOOR(DATEDIFF(MINUTE, usm_request.created_date, usm_request.completion_date) / 1440)* 1440 ) 
    - ( FLOOR(( DATEDIFF(MINUTE, usm_request.created_date,usm_request.completion_date) % 1440 )
    / 60) * 60 ) ))+ CASE WHEN DATEPART(SECOND, usm_request.created_date) > DATEPART(SECOND, usm_request.completion_date) 
    THEN -1
    ELSE 0
    END AS VARCHAR(10)) + 'minute'  [Time To Complete]
    from usm_request where request_id = 10637

    Thursday, April 18, 2019 9:44 AM
  • Hi du00805,

     

    Please try my newest script above , and it will get a correct result.

     

    declare @created_date datetime ='2019-04-16 11:09:03.000'
    
             
    
    declare @completion_date datetime='2019-04-16 11:17:19.000'
    
    select
    
    CAST(FLOOR(DATEDIFF(MINUTE, @created_date, @completion_date) / 1440) AS VARCHAR(10))+ 'day'
    
    + CAST(FLOOR(( DATEDIFF(MINUTE, @created_date, @completion_date) % 1440 ) / 60) AS VARCHAR(10))+ 'hour'
    
    + CAST(FLOOR(( ( DATEDIFF(MINUTE, @created_date, @completion_date) )
    
    - ( FLOOR(DATEDIFF(MINUTE, @created_date, @completion_date) / 1440)* 1440 )
    
    - ( FLOOR(( DATEDIFF(MINUTE, @created_date,@completion_date) % 1440 )
    
    / 60) * 60 ) ))+ CASE WHEN DATEPART(SECOND, @created_date) > DATEPART(SECOND, @completion_date)
    
    THEN -1
    
    ELSE 0
    
    END AS VARCHAR(10)) + 'minute'  [Time To Complete]
    
    /*
    
    Time To Complete
    
    -------------------------------------------
    
    0day0hour8minute
    
    */
    
     


     

    Hope it can help you.

     

    Best Regards,

    Rachel


    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.

    • Marked as answer by du00805 Thursday, April 18, 2019 10:08 AM
    Thursday, April 18, 2019 9:57 AM
  • Thanks Rachel, I tried and it was working perfectly for the current scenario. I am trying now different scenarios against Business Working Days and Weekends. Hopefully the above query and business function will not include (Friday and Saturday) and count rest of the days for the two mentioned scenarios. 

    Thanks Once again.  

    Thursday, April 18, 2019 10:07 AM