SQL Server: поиск даты с учетом конечной даты и числа дней, исключая дни из определенных диапазонов дат

ProgramBox

SQL Server: поиск даты с учетом конечной даты и числа дней, исключая дни из определенных диапазонов дат

Post author:admin
Запись опубликована:25 марта, 2023
Post category:Вопросы по программированию

#sql #sql-server #date

#sql #sql-server #Дата

Вопрос:

У меня есть TableA в базе данных, аналогичной следующей:

 Id | Status   | Start       | End

1  | Illness  | 2013-04-02  | 2013-04-23
2  | Illness  | 2013-05-05  | 2014-01-01
3  | Vacation | 2014-02-01  | 2014-03-01
4  | Illness  | 2014-03-08  | 2014-03-09
5  | Vacation | 2014-05-05  | NULL

Представьте, что он отслеживает дни «отсутствия» конкретного пользователя. Учитывая следующие входные данные:

SomeEndDate (Дата),
NumDays (Целое число)

Я хочу найти SomeStartDate (дату), которая является Numdays днями, не связанными с болезнью, из EndDate . Другими словами, скажем, мне присвоено SomeEndDate значение ‘2014-03-10’ и NumDays значение 60; соответствующий SomeStartDate будет:

с 2014-03-10 по 2014-03-09 = 1
с 2014-03-08 по 2014-01-01 = 57
с 2013-05-05 по 2013-05-03 = 2

Итак, через 60 дней без болезни мы получаем SomeStartDate значение ‘2013-05-03’. Есть ли какой-либо простой способ выполнить это в SQL? Я полагаю, что мог бы выполнять цикл каждый день, проверять, попадает ли он в один из диапазонов заболеваний, и увеличивать счетчик, если нет (выход из цикла после counter = @numbdays)… но это кажется крайне неэффективным. Ценю любую помощь.

1. Не совсем уверен, что понимаю вашу таблицу, но я думаю, что попробовал бы сделать это в двух отдельных вычислениях. Вычтите количество дней из вашей конечной даты. Вычислите количество дней болезни и добавьте это к вашему предыдущему результату.

2. Будьте очень осторожны в том, как вы интерпретируете диапазоны. Довольно часто диапазоны только для даты являются полностью закрытыми интервалами. Например, с 9 по 10 марта обычно считается два дня, а не один. (Конечно, если речь идет о времени, то это совершенно другое.)

3. Эндрю — Я думал об этом несколько раньше, но не мог убедить себя, что это хороший подход (возможно, я ошибался!). Если я беру 60 дней от конечной даты и добавляю @count дни болезни, которые лежат внутри, чтобы получить StartDate = dateadd(d, -(60 @count), EndDate), моя новая дата начала может попасть в другой диапазон болезни или содержать дополнительные дни болезни. Итак, я бы повторял это до тех пор, пока новая дата начала не попадет в диапазон болезни или не будет содержать дополнительные дни болезни?

4. Мэтт — Я ценю напоминание. Меня больше беспокоил поиск общего подхода, который был бы достаточно эффективным или не выглядел бы супер взломанным вместе, но я буду иметь это в виду, когда я на самом деле напишу это.

Ответ №1:

Создайте таблицу календаря, содержащую список всех дат, которые вас когда-либо интересуют.

 SELECT MIN([date])
FROM (
  SELECT TOP(@NumDays) [date]
  FROM Calendar c
  WHERE c.Date < @SomeEndDate
    AND NOT EXISTS (
      SELECT 1
      FROM TableA a
      WHERE c.Date BETWEEN a.Start AND a.END
        AND Status = 'Illness'
    )
  ORDER BY c.Date
) t

Метод таблицы календаря позволяет также легко исключить праздники, выходные и т.д.

Ответ №2:

SQL Server 2012:

Попробуйте это решение:

 DECLARE @NumDays INT = 70, @SomeEndDate DATE = '2014-03-10';

SELECT  
    [RangeStop],
    CASE 
        WHEN RunningTotal_NumOfDays <= @NumDays THEN [RangeStart]
        WHEN RunningTotal_NumOfDays - Current_NumOfDays <= @NumDays THEN DATEADD(DAY, -(@NumDays - (RunningTotal_NumOfDays - Current_NumOfDays)) 1, [RangeStop])
    END AS [RangeStart]
FROM (
    SELECT  
        y.*, 
        DATEDIFF(DAY, y.RangeStart, y.RangeStop) AS Current_NumOfDays,
        SUM( DATEDIFF(DAY, y.RangeStart, y.RangeStop) ) OVER(ORDER BY y.RangeStart DESC) AS RunningTotal_NumOfDays
    FROM (
        SELECT  LEAD(x.[End]) OVER(ORDER BY x.[End] DESC) AS RangeStart, -- It's previous date because of "ORDER BY x.[End] DESC"
            x.[Start] AS RangeStop
        FROM (
            SELECT   @SomeEndDate AS [Start], '9999-12-31' AS [End]
            UNION ALL
            SELECT  x.[Start], x.[End]
            FROM    @MyTable AS x
            WHERE   x.[Status] = 'Illness'
            AND     x.[End] <= @SomeEndDate
        ) x
    ) y
) z
WHERE RunningTotal_NumOfDays - Current_NumOfDays <= @NumDays;

/*
Output:
RangeStop  RangeStart
---------- ----------
2014-03-10 2014-03-09
2014-03-08 2014-01-01
2013-05-05 2013-05-03
*/

Примечание # 1: LEAD(End) вернет предыдущую конечную дату (предыдущую из-за ПОРЯДКА ПО конечному описанию)

Примечание # 2: DATEDIFF(DAY, RangeStart, RangeStop) вычисляет число. количество дней между текущим началом (псевдоним x.RangeStop ) и «предыдущим» концом (псевдоним x.RangeStar ) => Current_NumOfDays

Примечание # 3: SUM( Current_NumOfDays ) вычисляет текущий итог таким образом: 1 66 (3)

Примечание # 4: я использовал @NumOfDays = 70 (не 60 )