Here's my solution to your problem.
In the CTE "comparison", I join every record with this and all following records for that student. This way, I have a possible starting point (from the left side of the join) of a continuous training block and a possible end of such a block (from the right side of the join).
Using "cross applies", I calculate 2 values:
- the workdays from the start of the possible first to the end of the possible last interval of a chain
- the workdays in just the possible last interval in the chain.
On the latter values, using a windows function, I build a running total of the workdays from the possible start and end intervals.
You tagged the question with "SQL 2012", so using this window function should be possible.
In the next CTE ("sorting"), I restict the previous results to those where the running total equals the workdays between first start date and last end date. This way, only continuous blocks are left. These are then numbered in 2 ways:
- continuous blocks with the same EndDate are numberd by ascending StartDate
- continuous blocks with the same StartDate are numberd by descending EndDate.
For every EndDate, I want the earliest StartDate, and for this StartDate, I want the latest EndDate only, so I filter for 1 in both numberings. Here it is:
WITH
comparison (ID, StartDate, EndDate, TotalDays, SumSingleDays) AS (
SELECT bStart.ID, bStart.StartDate, bEnd.EndDate, Workdays.Total
, SUM(Workdays.Single) OVER (
PARTITION BY bStart.ID, bStart.StartDate
ORDER BY bEnd.StartDate
ROWS UNBOUNDED PRECEDING)
FROM StudentBookings bStart
INNER JOIN StudentBookings bEnd
ON bStart.ID = bEnd.ID AND bStart.StartDate <= bEnd.StartDate
CROSS APPLY (VALUES (
DATEDIFF(day, 0, bStart.StartDate),
DATEDIFF(day, 0, bEnd.StartDate),
1+DATEDIFF(day, 0, bEnd.EndDate))
) d (s1, s2, e2)
CROSS APPLY (VALUES (
(d.e2 - d.s1) - (d.e2/7 - d.s1/7) - ((d.e2+1)/7 - (d.s1+1)/7),
(d.e2 - d.s2) - (d.e2/7 - d.s2/7) - ((d.e2+1)/7 - (d.s2+1)/7))
) Workdays (Total, Single)
),
sorting (ID, StartDate, EndDate, Duration, RowNumStart, RowNumEnd) AS (
SELECT ID, StartDate, EndDate, TotalDays
, ROW_NUMBER() OVER (PARTITION BY ID, EndDate ORDER BY StartDate)
, ROW_NUMBER() OVER (PARTITION BY ID, StartDate ORDER BY EndDate DESC)
FROM comparison
WHERE TotalDays = SumSingleDays
)
SELECT ID, StartDate, EndDate, Duration
, CASE WHEN Duration >= 5 THEN 'Resident' ELSE 'Visitor' END AS [Type]
FROM sorting
WHERE (RowNumStart = 1)
AND (RowNumEnd = 1)
ORDER BY ID, StartDate;
The results:
Maybe there's a more elegant way to solve this using the interval packing solution by Itzik Ben-Gan, I'll post it when I figured that out.
Added:
Additionally, I count the number of bookings of all booking blocks and build a sum by student (ID) to make the "Resident" decision in the end. Bookings are restricted to the last 3 months in the first CTE (comparison):
WITH
comparison (ID, StartDate, EndDate, TotalDays, CountBookings, SumSingleDays) AS (
SELECT bStart.ID, bStart.StartDate, bEnd.EndDate, Workdays.Total
, COUNT(Workdays.Single) OVER (
PARTITION BY bStart.ID, bStart.StartDate
ORDER BY bEnd.StartDate
ROWS UNBOUNDED PRECEDING)
, SUM(Workdays.Single) OVER (
PARTITION BY bStart.ID, bStart.StartDate
ORDER BY bEnd.StartDate
ROWS UNBOUNDED PRECEDING)
FROM StudentBookings bStart
INNER JOIN StudentBookings bEnd
ON bStart.ID = bEnd.ID AND bStart.StartDate <= bEnd.StartDate
CROSS APPLY (VALUES (
DATEDIFF(day, 0, bStart.StartDate),
DATEDIFF(day, 0, bEnd.StartDate),
1+DATEDIFF(day, 0, bEnd.EndDate))
) d (s1, s2, e2)
CROSS APPLY (VALUES (
(d.e2 - d.s1) - (d.e2/7 - d.s1/7) - ((d.e2+1)/7 - (d.s1+1)/7),
(d.e2 - d.s2) - (d.e2/7 - d.s2/7) - ((d.e2+1)/7 - (d.s2+1)/7))
) Workdays (Total, Single)
WHERE bStart.StartDate >= DATEADD(month, -3, GETDATE())
),
sorting (ID, StartDate, EndDate, Duration, CountBookings, RowNumStart, RowNumEnd) AS (
SELECT ID, StartDate, EndDate, TotalDays, CountBookings
, ROW_NUMBER() OVER (PARTITION BY ID, EndDate ORDER BY StartDate)
, ROW_NUMBER() OVER (PARTITION BY ID, StartDate ORDER BY EndDate DESC)
FROM comparison
WHERE TotalDays = SumSingleDays
),
counting (ID, StartDate, EndDate, Duration, Bookings) AS (
SELECT ID, StartDate, EndDate, Duration
, SUM(CountBookings) OVER (PARTITION BY ID)
FROM sorting WHERE (RowNumStart = 1) AND (RowNumEnd = 1)
)
SELECT ID, StartDate, EndDate, Duration, Bookings
, CASE
WHEN Duration >= 5 OR Bookings >= 2 THEN 'Resident' ELSE 'Visitor'
END AS [Type]
FROM counting
ORDER BY ID, StartDate;
Filtering ClasseReferences:
The ClassReference will be taken and filtered from the bStart table reference. To be able to add this field to the final query, it also has to be used to join the bEndtable reference, so only booking intervals with the same ClassReference value will be connected to blocks:
WITH
comparison (ID, ClassReference, StartDate, EndDate, TotalDays, CountBookings, SumSingleDays) AS (
SELECT bStart.ID, bStart.ClassReference, bStart.StartDate, bEnd.EndDate, Workdays.Total
, COUNT(Workdays.Single) OVER (
PARTITION BY bStart.ID, bStart.StartDate
ORDER BY bEnd.StartDate
ROWS UNBOUNDED PRECEDING)
, SUM(Workdays.Single) OVER (
PARTITION BY bStart.ID, bStart.StartDate
ORDER BY bEnd.StartDate
ROWS UNBOUNDED PRECEDING)
FROM StudentBookings bStart
INNER JOIN StudentBookings bEnd
ON bStart.ID = bEnd.ID AND bStart.StartDate <= bEnd.StartDate
AND bStart.ClassReference = bEnd.ClassReference
CROSS APPLY (VALUES (
DATEDIFF(day, 0, bStart.StartDate),
DATEDIFF(day, 0, bEnd.StartDate),
1+DATEDIFF(day, 0, bEnd.EndDate))
) d (s1, s2, e2)
CROSS APPLY (VALUES (
(d.e2 - d.s1) - (d.e2/7 - d.s1/7) - ((d.e2+1)/7 - (d.s1+1)/7),
(d.e2 - d.s2) - (d.e2/7 - d.s2/7) - ((d.e2+1)/7 - (d.s2+1)/7))
) Workdays (Total, Single)
WHERE bStart.StartDate >= DATEADD(month, -3, GETDATE())
AND bStart.ClassReference IN (N'C1', N'C2')
),
sorting (ID, ClassReference, StartDate, EndDate, Duration, CountBookings, RowNumStart, RowNumEnd) AS (
SELECT ID, ClassReference, StartDate, EndDate, TotalDays, CountBookings
, ROW_NUMBER() OVER (PARTITION BY ID, ClassReference, EndDate ORDER BY StartDate)
, ROW_NUMBER() OVER (PARTITION BY ID, ClassReference, StartDate ORDER BY EndDate DESC)
FROM comparison
WHERE TotalDays = SumSingleDays
),
counting (ID, ClassReference, StartDate, EndDate, Duration, Bookings) AS (
SELECT ID, ClassReference, StartDate, EndDate, Duration
, SUM(CountBookings) OVER (PARTITION BY ID, ClassReference)
FROM sorting WHERE (RowNumStart = 1) AND (RowNumEnd = 1)
)
SELECT ID, ClassReference, StartDate, EndDate, Duration, Bookings
, CASE
WHEN Duration >= 5 OR Bookings >= 2 THEN 'Resident' ELSE 'Visitor'
END AS [Type]
FROM counting
ORDER BY ID, StartDate;
Using this data for testing:
With a filter for the last 12 months, the query returns:
So Student 1 is "Resident" in class C2 but Visitor in Class C1.
