-- Create View SickInstances.sql

/*
-- check
SELECT x.* FROM dbo.SickDays_t AS x (NOLOCK);
SELECT x.* FROM dbo.SickInstances_vw AS x (NOLOCK);
*/

USE [Test]
GO

CREATE OR ALTER VIEW [dbo].[SickInstances_vw]
AS
WITH base_cte (Row_ID, Employee_ID, SickDate)
AS
(
	SELECT
		Row_ID		= row_number() OVER (ORDER BY sd.Employee, sd.[Date]),
		Employee_ID = sd.Employee,
		SickDate	= sd.[Date]
	FROM
		dbo.SickDays_t AS sd
),

interim1_cte (Row_ID, Employee_ID, SickDate, Next_Row_ID, Next_Employee_ID, Next_SickDate, DaysDifference)
AS
(
	SELECT
		curr_row.Row_ID,
		curr_row.Employee_ID,
		curr_row.SickDate,
		next_row.Row_ID,
		next_row.Employee_ID,
		[Next_SickDate] = 
			CASE
				WHEN curr_row.Employee_ID <> next_row.Employee_ID THEN NULL
				ELSE next_row.SickDate
			END,
		[DaysDifference] = 
			CASE
				WHEN curr_row.Employee_ID <> next_row.Employee_ID THEN NULL
				ELSE datediff(d, curr_row.SickDate, next_row.SickDate)
			END
	FROM
		base_cte AS curr_row
		LEFT JOIN base_cte AS next_row
			ON curr_row.Row_ID = next_row.Row_ID - 1

),

interim2_cte (Row_ID, Employee_ID, SickDate, Next_SickDate, DaysDifference)
AS
(
	SELECT
		d.Row_ID,
		d.Employee_ID,
		d.SickDate,
		d.Next_SickDate,
		d.DaysDifference
	FROM
		interim1_cte AS d
	WHERE
		d.DaysDifference IS NOT NULL
		AND d.DaysDifference < 7
)

SELECT * FROM interim2_cte AS x
GO
