Selecting count of consecutives dates before and after a specified date based on start/end

user_3776430 0
Viewed 70

I'm trying to determine the number of records with consecutive dates (previous record ends on the same date as the start date of the next record) before and after a specified date, and ignore any consecutive records as soon as there is a break in the chain.

If I have the following data:

-- declare vars
DECLARE @dateToCheck    date = '2020-09-20'
DECLARE @numRecsBefore  int = 0 
DECLARE @numRecsAfter   int = 0
DECLARE @tempID         int

-- temp table
CREATE TABLE #dates
    (
    [idx]       INT IDENTITY(1,1),
    [startDate] DATETIME ,
    [endDate]   DATETIME,
    [prevEndDate] DATETIME
    )

-- insert temp table
INSERT INTO #dates 
        ( [startDate], [endDate] )
VALUES  ( '2020-09-01', '2020-09-04' ),
        ( '2020-09-04', '2020-09-10' ),
        ( '2020-09-10', '2020-09-16' ),
        ( '2020-09-17', '2020-09-19' ),
        ( '2020-09-19', '2020-09-20' ),
        --
        ( '2020-09-20', '2020-09-23' ),
        ( '2020-09-25', '2020-09-26' ),
        ( '2020-09-27', '2020-09-28' ),
        ( '2020-09-28', '2020-09-30' ),
        ( '2020-10-01', '2020-09-05' )

-- update with previous records endDate
DECLARE @maxRows int = (SELECT MAX(idx) FROM #dates)
DECLARE @intCount int = 0
WHILE @intCount <= @maxRows
BEGIN
    UPDATE #dates SET prevEndDate = (SELECT endDate FROM #dates WHERE idx = (@intCount - 1) ) WHERE idx=@intCount
    SET @intCount = @intCount + 1 
END

-- clear any breaks in the chain?


-- number of consecutive records before this date 
SET @numRecsBefore = (SELECT COUNT(idx) FROM #dates WHERE startDate = prevEndDate AND endDate <= @dateToCheck)

-- number of consecutive records after this date 
SET @numRecsAfter = (SELECT COUNT(idx) FROM #dates WHERE startDate = prevEndDate AND endDate >= @dateToCheck)


-- return & clean up
SELECT * FROM #dates
SELECT @numRecsBefore AS numBefore, @numRecsAfter AS numAfter
DROP TABLE #dates

With the specified date being '2020-09-20, I would expect @numRecsBefore = 2 and @numRecsAfter = 1. That is not what I am getting, as its counting all the consecutive records.

There has to be a better way to do this. I know the loop isn't optimal, but I couldn't get LAG() or LEAD() to work. I've spend all morning trying different methods and searching, but everything I find doesn't deal with two dates, or breaks in the chain.

gaps-and-islands sql-server-2012 datetime sql-server sql
2 Answers

This reads like a gaps-and-island problem. Islands represents rows whose date ranges are adjacent, and you want to count how many records preceed of follow a current date in the same island.

You could do:

select 
    max(case when @dateToCheck > startdate  and @dateToCheck <= enddate then numRecsBefore end) as numRecsBefore,
    max(case when @dateToCheck >= startdate and @dateToCheck <  enddate then numRecsAfter end) as numRecsAfter
from (
    select d.*, 
        count(*) over(partition by grp order by startdate) as numRecsBefore,
        count(*) over(partition by grp order by startdate desc) as numRecsAfter
    from (
        select d.*,
            sum(case when startdate = lag_enddate then 0 else 1 end) over(order by startdate) as grp
        from (
            select d.*,
                lag(enddate) over(order by startdate) as lag_enddate
            from #dates d
        ) d
    ) d
) d

This uses lag() and a cumulative sum() to define the islands. The a window count gives the number and preceding and following records on the same island. The final step is conditional aggrgation; extra care needs to be taken on the inequalities to take in account various possibilites (typically, the date you search for might not always match a range bound).

Demo on DB Fiddle

user_106767160

I think this is what you are after, however, this does not give the results in your query; I suspect that is because they aren't the expected results? One of the conditional aggregated may also want to be a >= or <=, but I don't know which:

WITH CTE AS(
    SELECT startDate,
           endDate,
           CASE startDate WHEN LAG(endDate) OVER (ORDER BY startDate ASC) THEN 1 END AS IsSame
    FROM #dates d)
SELECT COUNT(CASE WHEN startDate < @dateToCheck THEN IsSame END) AS numBefore,
       COUNT(CASE WHEN startDate > @dateToCheck THEN IsSame END) AS numAfter
FROM CTE;
user_20299830
Related