Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
gavinb80
Starting Member
2 Posts |
Posted - 2012-11-15 : 05:09:22
|
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 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-15 : 05:20:34
|
[code]SELECT t.Person,MIN(t.[DATE]) AS LeaveStart,MAX(t.[Date]) AS LeaveEndFROM Table tOUTER APPLY (SELECT MIN(DATE) AS [Date] FROM Table WHERE Person = t.Person AND SHIFTTYPE='WORKING' AND DATE > t.DATE )t1WHERE t.SHIFTTYPE <> 'WORKING'GROUP BY t.Person,t1.[Date][/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
gavinb80
Starting Member
2 Posts |
Posted - 2012-11-15 : 07:11:22
|
Thanks visakh16, thats a great help |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-15 : 10:08:37
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|