Calculating absence instances

• gavin.beard

  Newbie

  Points: 6

  More actions

  November 15, 2012 at 3:04 am

  #270303

  Morning all,

  I have an issue and it is driving me crazy, so was helping someone may be able to help 😀

  I have a list of people and a list of dates and leave types such as :

  PERSON | DATE | SHIFTTYPE

  ---------------------------------------------------------

  000001 | 2012-10-01 00:00 | WORKING

  000001 | 2012-10-02 00:00 | SICK

  000001 | 2012-10-03 00:00 | SICK

  000001 | 2012-10-04 00:00 | DAYOFF

  000001 | 2012-10-05 00:00 | SICK

  000001 | 2012-10-06 00:00 | WORKING

  000001 | 2012-10-07 00:00 | WORKING

  000001 | 2012-10-08 00:00 | SICK

  000001 | 2012-10-09 00:00 | SICK

  From 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 thanks

  Gavin

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  November 15, 2012 at 3:20 am

  #1559256

  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 rn2

  FROM @t)

  SELECT PERSON,

  MIN(DT) AS FromDT,

  MAX(DT) AS ToDT

  FROM CTE

  WHERE SHIFTTYPE<>'WORKING'

  GROUP BY PERSON,rn1-rn2

  ORDER BY PERSON,MIN(rn1);

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• gavin.beard

  Newbie

  Points: 6

  More actions

  November 15, 2012 at 5:10 am

  #1559305

  Thanks Mark,

  This seems to do the trick

  Gav