Блог

Главная - Вопросы по программированию - Datediff (минуты) без учета выходных, считая указанный интервал времени

Datediff (минуты) без учета выходных, считая указанный интервал времени

#sql #sql-server #tsql

#sql #sql-сервер #tsql

Вопрос:

Как добавить в следующий скрипт дополнительный подсчет времени только в часы 10:00 <> 18:00. Если время @StartDate — 09:00, то мы рассматриваем его как 10:00

Пример:

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

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

Ожидаемый результат по минутам — это сумма минут с 10:00 до 18:00, так что 8 * 60 = 480 минут.

Спасибо за советы

 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)
    )
)

Комментарии:

1. В этом случае таблица календаря или подсчет были бы наиболее полезны.

2. Есть решение? Приведенное выше работает нормально, но перечисление в контексте временного интервала отсутствует, и мне интересно, как его использовать, потому что запрос почти готов 🙂

Ответ №1:

Попробуйте это.

 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

Протестируйте это демо

Комментарии:

1. Хороший ответ содержит объяснение, в дополнение к наличию рабочего кода, так что OP извлекает из него уроки.

Ответ №2:

Просто еще один вариант (перебор).

Может занимать несколько дней. Может быть расширен, чтобы включить праздники.

 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

Результаты

 Minutes
480

Комментарии:

1. В сторону: ( @@DateFirst DatePart( weekday, SampleDate ) - 1 ) % 7 1 всегда возвращает целое число от 1 до 7 с 1 , соответствующее воскресенью, независимо от установки DateFirst или Language .

2. @HABO Достаточно справедливо. На самом деле я предпочитаю использовать DATENAME(ДЕНЬ НЕДЕЛИ, …), но я заметил, что OP использовал 1 и 7

Метки: ,