RE: Find "Consecutive" records based on available dates in another table

SSC Guru

Points: 186127

July 14, 2016 at 2:48 am

jtrask (7/13/2016)
Of course. It all seems obvious once someone spells it out for me.
I like the idea of doing it on the fly. There's no point in having this information just lying about. It will help with clerical errors, aka, not entering dates in order.

Here's another way, which is based on date range algorithms so is well-known:

--The appropriate dates are stored in a table just for this purpose. Like this

IF OBJECT_ID('tempdb..#Holidays') IS NOT NULL DROP TABLE #Holidays;

;WITH Holidays (ADate, Holiday, Description) AS (

SELECT '07-01-2016', 0, NULL UNION ALL

SELECT '07-04-2016', 1, 'July 4th' UNION ALL

SELECT '07-06-2016', 0, NULL UNION ALL

SELECT '07-08-2016', 0, NULL UNION ALL

SELECT '07-11-2016', 0, NULL UNION ALL

SELECT '07-13-2016', 0, NULL UNION ALL

SELECT '07-15-2016', 0, NULL UNION ALL

SELECT '07-18-2016', 0, NULL UNION ALL

SELECT '07-20-2016', 0, NULL UNION ALL

SELECT '07-22-2016', 0, NULL UNION ALL

SELECT '07-25-2016', 0, NULL UNION ALL

SELECT '07-27-2016', 0, NULL UNION ALL

SELECT '07-29-2016', 0, NULL

) SELECT * INTO #Holidays FROM Holidays

--I have another table that tracks employee participation. It has a field for date, and a field for the employee ID.

IF OBJECT_ID('tempdb..#employee_participation') IS NOT NULL DROP TABLE #employee_participation;

;WITH employee_participation (ADate, EmployeeID) AS (

SELECT '07-01-2016', 5678 UNION ALL

SELECT '07-01-2016', 1234 UNION ALL

SELECT '07-06-2016', 1234 UNION ALL

SELECT '07-08-2016', 1234 UNION ALL

SELECT '07-04-2016', 3333 UNION ALL

SELECT '07-06-2016', 3333 UNION ALL

SELECT '07-08-2016', 3333 UNION ALL

SELECT '07-13-2016', 3333 UNION ALL

SELECT '07-18-2016', 3333 UNION ALL

SELECT '05-06-2016', 4444 UNION ALL

SELECT '07-06-2016', 4444 UNION ALL

SELECT '07-11-2016', 4444 UNION ALL

SELECT '07-13-2016', 4444 UNION ALL

SELECT '07-15-2016', 4444 UNION ALL

SELECT '07-18-2016', 4444 UNION ALL

SELECT '08-18-2016', 4444

) SELECT * INTO #employee_participation FROM employee_participation

-- Solution

;WITH

Emps AS (SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY ADate) FROM #employee_participation),

TheDays AS (SELECT *, rn = ROW_NUMBER() OVER (ORDER BY ADate) FROM #Holidays h)

SELECT EmployeeID, RangeStart, RangeEnd, DaysContiguous

FROM (

SELECT e.EmployeeID, RangeStart = MIN(e.ADate), RangeEnd = MAX(e.ADate), DaysContiguous = COUNT(*),

rn = ROW_NUMBER() OVER (PARTITION BY e.EmployeeID ORDER BY COUNT(*) DESC)

FROM Emps e

LEFT JOIN TheDays d ON d.ADate = e.ADate

GROUP BY e.EmployeeID, d.rn-e.rn

) d

WHERE rn = 1

-- Run this bit to see how the solution works

;WITH

Emps AS (SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY ADate) FROM #employee_participation),

TheDays AS (SELECT *, rn = ROW_NUMBER() OVER (ORDER BY ADate) FROM #Holidays h)

SELECT e.EmployeeID, e.ADate, d.rn, e.rn, d.rn-e.rn

FROM Emps e

LEFT JOIN TheDays d ON d.ADate = e.ADate

ORDER BY e.EmployeeID, e.ADate

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden