Datediff (minutes) excluding weekends counting the specified time interval-CodePudding

How to add to the following script to additionally count the time only in the hours 10:00 <> 18:00. If the time @StartDate is 09:00 then we treat it as 10:00

Example:

SET @StartDate = '2021/12/10 09:00:00'

SET @EndDate = '2021/12/11 18:30:00'

The expected minutes result is the sum of minutes from 10:00 to 18:00 so 8*60 = 480 minutes.

Thanks for the tips

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2013/03/15 23:30:00'
SET @EndDate = '2013/03/17 00:30:00'


SELECT
(   DATEDIFF(MINUTE, @StartDate, @EndDate)
    - ( DATEDIFF(wk, @StartDate,@EndDate)*(2*24*60)
        -- End on Sunday
        -(CASE WHEN DATEPART(dw, @EndDate)  = 1 THEN 24.0*60-DATEDIFF(minute,CONVERT(date,@EndDate),@EndDate) ELSE 0 END)
        -- Start on Saturday
        -(CASE WHEN DATEPART(dw, @StartDate) = 7 THEN DATEDIFF(minute,CONVERT(date,@StartDate),@StartDate) ELSE 0 END)
        -- End on Saturday
         (CASE WHEN DATEPART(dw, @EndDate)  = 7 THEN DATEDIFF(minute,CONVERT(date,@EndDate),@EndDate) ELSE 0 END)
        -- Start on Saturday
         (CASE WHEN DATEPART(dw, @StartDate) = 1 THEN 24.0*60-DATEDIFF(minute,CONVERT(date,@StartDate),@StartDate) ELSE 0 END)
    )
)

CodePudding user response：

Try this.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @MinDateTime DATETIME
DECLARE @MaxDateTime DATETIME
SET @StartDate = '2021/12/10 09:00:00'
SET @EndDate = '2021/12/10 18:30:00'
SET @MinDateTime = CONVERT(datetime, CONCAT(CONVERT(DATE, @StartDate), ' 10:00:00'))
SET @MaxDateTime = CONVERT(datetime, CONCAT(CONVERT(DATE, @EndDate), ' 18:00:00'))

 SELECT
(   DATEDIFF(MINUTE, @StartDate, @EndDate)
    - ( DATEDIFF(wk, @StartDate,@EndDate)*(2*24*60)
        -- End on Sunday
        -(CASE WHEN DATEPART(dw, @EndDate)  = 1 THEN 24.0*60-DATEDIFF(minute,CONVERT(date,@EndDate),@EndDate) ELSE 0 END)
        -- Start on Saturday
        -(CASE WHEN DATEPART(dw, @StartDate) = 7 THEN DATEDIFF(minute,CONVERT(date,@StartDate),@StartDate) ELSE 0 END)
        -- End on Saturday
         (CASE WHEN DATEPART(dw, @EndDate)  = 7 THEN DATEDIFF(minute,CONVERT(date,@EndDate),@EndDate) ELSE 0 END)
        -- Start on Saturday
         (CASE WHEN DATEPART(dw, @StartDate) = 1 THEN 24.0*60-DATEDIFF(minute,CONVERT(date,@StartDate),@StartDate) ELSE 0 END)
    )
),
DATEDIFF(minute, CASE WHEN @StartDate < @MinDateTime 
                   THEN @MinDateTime
             ELSE @StartDate
        END,
        CASE WHEN @EndDate > @MaxDateTime 
                   THEN @MaxDateTime  
             ELSE @EndDate
        END) AS diff

Test it Demo

CodePudding user response：

Just another Option (Brute Force).

Can span multiple days. Can be expanded to include holidays.

Declare @Date1 datetime = '2021/12/10 10:00:00'
Declare @Date2 datetime = '2021/12/12 18:30:00'

Select Minutes = count(*)
 From  ( Select Top (DateDiff(Minute,@Date1,@Date2) 1) D=DateAdd(MINUTE,-1 Row_Number() Over (Order By (Select Null)),@Date1) 
          From  master..spt_values n1,master..spt_values n2
       ) A
 Where datepart(WEEKDAY,D) not in (1,7)
   and convert(time,D) >= '10:00' 
   and convert(time,D) <  '18:00'
   and convert(date,D) not in ('2021-12-25','2022-01-01')  -- Holidays: Should be a table

Results

Minutes
480