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.
Best Answer
Assuming hours are added to table in chronological order. If not, sort them before inserting them!
First we need to relate each record to successive records.
You say that is done by matching their hrs1 & hrs2, and making sure the initial record ends one day before the next starts. Your sample data only includes two records that are sequential, so I added a third (startdte='1995-01-01') to show this works in such a case. If we only expected two records to be part of the same sequence, we could just use a join. But if an indefinite number may be part of the same sequence, then we must use recursion. In this case, a CTE. The first part of the UNION gets the parent records, and the second part of the UNION gets all children. To uniquely track which sequence is which, we use the 'chain' field as a sort of bread crumb trail. Finally, we query the root ID (which we tracked as we went along) and the 'chain' field to find the first and last record in each sequence. Then we join it all back to the original data. Last, we clean up null 'ENDDTE's for full time records.