CODEWITHSUNDEEP
sqlsql-serverdate
AliceSmith
AliceSmith

Reputation: 371

SQL Server: Finding date given EndDate and # Days, excluding days from specific date ranges

I have a TableA in a database similar to the following: 

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

Imagine it's keeping track of a specific user's "Away" days. Given the following Inputs:

  1. SomeEndDate (Date),
  2. NumDays (Integer)

I want to find the SomeStartDate (Date) that is Numdays non-illness days from EndDate. In other words, say I am given a SomeEndDate value '2014-03-10' and a NumDays value of 60; the matching SomeStartDate would be:

  1. 2014-03-10 to 2014-03-09 = 1
  2. 2014-03-08 to 2014-01-01 = 57
  3. 2013-05-05 to 2013-05-03 = 2

So, at 60 non-illness days, we get a SomeStartDate of '2013-05-03'. IS there any easy way to accomplish this in SQL? I imagine I could loop each day, check whether or not it falls into one of the illness ranges, and increment a counter if not (exiting the loop after counter = @numdays)... but that seems wildly inefficient. Appreciate any help.

Upvotes: 0

Views: 81

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

SQL Server 2012:

Try this solution:

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
*/

Note #1: LEAD(End) will return the previous End date (previous because of ORDER BY End DESC)

Note #2: DATEDIFF(DAY, RangeStart, RangeStop) computes the num. of days between current start (alias x.RangeStop) and "previous" end (alias x.RangeStar) => Current_NumOfDays

Note #3: SUM( Current_NumOfDays ) computes a running total thus: 1 + 66 + (3)

Note #4: I've used @NumOfDays = 70 (not 60)

Upvotes: 0

Anon
Anon

Reputation: 10908

Make a Calendar table that has a list of all the dates you will ever care about.

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

The Calendar table method lets you also easily exclude holidays, weekends, etc.

Upvotes: 1

Related Questions