Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculating absence instances Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 3:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 2:47 AM
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

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

Post #1385028
Posted Thursday, November 15, 2012 3:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:53 AM
Points: 1,678, Visits: 19,555

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);



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1385038
Posted Thursday, November 15, 2012 5:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 29, 2012 2:47 AM
Points: 2, Visits: 8
Thanks Mark,

This seems to do the trick

Gav
Post #1385094
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse