## Calculating absence instances

 Author Message gavin.beard Forum Newbie Group: General Forum Members Points: 2 Visits: 8 Morning all,I have an issue and it is driving me crazy, so was helping someone may be able to help :-DI have a list of people and a list of dates and leave types such as :PERSON | DATE | SHIFTTYPE---------------------------------------------------------000001 | 2012-10-01 00:00 | WORKING000001 | 2012-10-02 00:00 | SICK000001 | 2012-10-03 00:00 | SICK000001 | 2012-10-04 00:00 | DAYOFF000001 | 2012-10-05 00:00 | SICK000001 | 2012-10-06 00:00 | WORKING000001 | 2012-10-07 00:00 | WORKING000001 | 2012-10-08 00:00 | SICK000001 | 2012-10-09 00:00 | SICKFrom this data I need to calculate absence instances, so the first instance would be 2012-10-02 to 2012-10-05 and the second instance would be 2012-10-08 to 2012-10-09.I am using SS2k8 R2, I really am at a loss with this one so any help would be greatly appreciated?Many thanksGavin Mark Cowne SSCrazy Group: General Forum Members Points: 2095 Visits: 22816 Looks like a gaps and islands problem.`DECLARE @t TABLE(PERSON CHAR(6), DT DATE, SHIFTTYPE VARCHAR(8))INSERT INTO @t(PERSON, DT, SHIFTTYPE)VALUES('000001','20121001','WORKING'),('000001','20121002','SICK'),('000001','20121003','SICK'),('000001','20121004','DAYOFF'),('000001','20121005','SICK'),('000001','20121006','WORKING'),('000001','20121007','WORKING'),('000001','20121008','SICK'),('000001','20121009','SICK');WITH CTE AS (SELECT PERSON, DT, SHIFTTYPE, ROW_NUMBER() OVER(PARTITION BY PERSON ORDER BY DT) AS rn1, ROW_NUMBER() OVER(PARTITION BY PERSON, CASE WHEN SHIFTTYPE='WORKING' THEN 1 ELSE 0 END ORDER BY DT) AS rn2FROM @t)SELECT PERSON, MIN(DT) AS FromDT, MAX(DT) AS ToDTFROM CTEWHERE SHIFTTYPE<>'WORKING'GROUP BY PERSON,rn1-rn2ORDER BY PERSON,MIN(rn1);` ____________________________________________________Deja View - The strange feeling that somewhere, sometime you've optimised this query beforeHow to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537 gavin.beard Forum Newbie Group: General Forum Members Points: 2 Visits: 8 Thanks Mark, This seems to do the trickGav