I have a table containing a part time working pattern consisting of start and end dates together with hours values.
Where there is an gap between an end date and a subsequent start date, this indicates the member was full time.
Where the most recent entry has a NULL end date, this indicates the part time work is still ongoing. A NULL enddte should be a day before the subsequent start date, if one is present.
I'd like to merge the date ranges where there are no gaps (ie, subsequent row start date is 1 day ahead of previous row start date) and where the hrs1 and hrs2 values match. Other solutions for similar problems don't correctly handle the NULL in the final row.
CREATE TABLE #hours( STARTDTE DATETIME, ENDDTE DATETIME, hrs1 DECIMAL(6,2), hrs2 DECIMAL(6,2)) INSERT INTO #hours VALUES ('25 MAY 1990','30 NOV 1994',18,36), ('01 DEC 1994','31 DEC 1994',18,36), ('01 JAN 2000', NULL,20,36), ('16 JUN 2002','20 APR 2007',18,36), ('10 OCT 2008', NULL,20,36)
The expected output would be as follows:
25 MAY 1990 | 31 DEC 1994 | 18 | 36 01 JAN 2000 | 15 JUN 2002 | 20 | 36 16 JUN 2002 | 20 APR 2007 | 18 | 36 10 OCT 2008 | NULL | 20 | 36
This query needs to be compatible with SQL Server 2008 so unfortunately lead/lag aren't an option to fill in any null end dates.