InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 20:22:21 GMT20http://www.sqlservercentral.com/Forums/Topic1385028-392-1.aspxThanks Mark, This seems to do the trickGavThu, 15 Nov 2012 05:10:38 GMTgavin.beardhttp://www.sqlservercentral.com/Forums/Topic1385028-392-1.aspxLooks like a gaps and islands problem.[code="sql"]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);[/code]Thu, 15 Nov 2012 03:20:28 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1385028-392-1.aspxMorning 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 thanksGavinThu, 15 Nov 2012 03:04:28 GMTgavin.beard