September 30, 2009 at 7:36 am
Sample Input :
-----------------------------
EmpID----Date-------Status
-----------------------------
1-----01-Jan-09-----Present
1-----02-Jan-09-----Present
1-----03-Jan-09-----Absent
1-----04-Jan-09-----Absent
1-----05-Jan-09-----Holiday
1-----06-Jan-09-----Present
1-----07-Jan-09-----Present
1-----08-Jan-09-----WeeklyOff
1-----09-Jan-09-----WeeklyOff
2-----01-Jan-09-----Absent
2-----02-Jan-09-----Absent
2-----03-Jan-09-----Present
2-----04-Jan-09-----Present
2-----05-Jan-09-----WeeklyOff
2-----06-Jan-09-----WeeklyOff
2-----07-Jan-09-----Holiday
3-----08-Jan-09-----Holiday
3-----09-Jan-09-----Absent
3-----10-Jan-09-----Absent
3-----11-Jan-09-----Present
3-----12-Jan-09-----Present
3-----13-Jan-09-----Present
3-----14-Jan-09-----Present
-----------------------------
Which ranking function can give the following output?
Output :
----------------------------------
Rank--EmpID----Date-------Status
----------------------------------
1-----1-----01-Jan-09-----Present
1-----1-----02-Jan-09-----Present
2-----1-----03-Jan-09-----Absent
2-----1-----04-Jan-09-----Absent
3-----1-----05-Jan-09-----Holiday
4-----1-----06-Jan-09-----Present
4-----1-----07-Jan-09-----Present
5-----1-----08-Jan-09-----WeeklyOff
5-----1-----09-Jan-09-----WeeklyOff
1-----2-----01-Jan-09-----Absent
1-----2-----02-Jan-09-----Absent
2-----2-----03-Jan-09-----Present
2-----2-----04-Jan-09-----Present
3-----2-----05-Jan-09-----WeeklyOff
3-----2-----06-Jan-09-----WeeklyOff
4-----2-----07-Jan-09-----Holiday
1-----3-----08-Jan-09-----Holiday
2-----3-----09-Jan-09-----Absent
2-----3-----10-Jan-09-----Absent
3-----3-----11-Jan-09-----Present
3-----3-----12-Jan-09-----Present
3-----3-----13-Jan-09-----Present
3-----3-----14-Jan-09-----Present
----------------------------------
This output would help me to derive the following result
---------------------------------------------
EmpID-StartDate-----EndDate-------Status
---------------------------------------------
1-----01-Jan-09-----02-Jan-09-----Present
1-----03-Jan-09-----04-Jan-09-----Absent
1-----05-Jan-09-----05-Jan-09-----Holiday
1-----06-Jan-09-----07-Jan-09-----Present
1-----08-Jan-09-----09-Jan-09-----WeeklyOff
2-----01-Jan-09-----02-Jan-09-----Absent
2-----03-Jan-09-----04-Jan-09-----Present
2-----05-Jan-09-----06-Jan-09-----WeeklyOff
2-----07-Jan-09-----07-Jan-09-----Holiday
3-----08-Jan-09-----08-Jan-09-----Holiday
3-----09-Jan-09-----10-Jan-09-----Absent
3-----11-Jan-09-----14-Jan-09-----Present
---------------------------------------------
Query to get sample input
;WITH Sample(EmpID, Date, Status) AS
(
SELECT 1 AS EmpID, '01-Jan-09' AS Date, 'Present' AS Status UNION
SELECT 1, '02-Jan-09','Present' UNION
SELECT 1, '03-Jan-09','Absent' UNION
SELECT 1, '04-Jan-09','Absent' UNION
SELECT 1, '05-Jan-09','Holiday' UNION
SELECT 1, '06-Jan-09','Present' UNION
SELECT 1, '07-Jan-09','Present' UNION
SELECT 1, '08-Jan-09','WeeklyOff' UNION
SELECT 1, '09-Jan-09','WeeklyOff' UNION
SELECT 2, '01-Jan-09','Absent' UNION
SELECT 2, '02-Jan-09','Absent' UNION
SELECT 2, '03-Jan-09','Present' UNION
SELECT 2, '04-Jan-09','Present' UNION
SELECT 2, '05-Jan-09','WeeklyOff' UNION
SELECT 2, '06-Jan-09','WeeklyOff' UNION
SELECT 2, '07-Jan-09','Holiday' UNION
SELECT 3, '08-Jan-09','Holiday' UNION
SELECT 3, '09-Jan-09','Absent' UNION
SELECT 3, '10-Jan-09','Absent' UNION
SELECT 3, '11-Jan-09','Present' UNION
SELECT 3, '12-Jan-09','Present' UNION
SELECT 3, '13-Jan-09','Present' UNION
SELECT 3, '14-Jan-09','Present'
)
SELECT EmpID, Date, Status From Sample
September 30, 2009 at 7:51 am
Have a look at my blog entry here which , if i understand you requirement correct should help you nearer your goal
September 30, 2009 at 8:13 am
From:
http://www.sqlservercentral.com/Forums/Topic758095-338-1.aspx
DECLARE @t TABLE
(
EmpId int NOT NULL
,[Date] smalldatetime NOT NULL
,[Status] varchar(20) NOT NULL
)
INSERT INTO @t
SELECT 1, '20090101', 'Present'
UNION ALL SELECT 1, '20090102', 'Present'
UNION ALL SELECT 1, '20090103', 'Absent'
UNION ALL SELECT 1, '20090104', 'Absent'
UNION ALL SELECT 1, '20090105', 'Holiday'
UNION ALL SELECT 1, '20090106', 'Present'
UNION ALL SELECT 1, '20090107', 'Present'
UNION ALL SELECT 1, '20090108', 'WeeklyOff'
UNION ALL SELECT 1, '20090109', 'WeeklyOff'
UNION ALL SELECT 2, '20090101', 'Absent'
UNION ALL SELECT 2, '20090102', 'Absent'
UNION ALL SELECT 2, '20090103', 'Present'
UNION ALL SELECT 2, '20090104', 'Present'
UNION ALL SELECT 2, '20090105', 'WeeklyOff'
UNION ALL SELECT 2, '20090106', 'WeeklyOff'
UNION ALL SELECT 2, '20090107', 'Holiday'
UNION ALL SELECT 3, '20090108', 'Holiday'
UNION ALL SELECT 3, '20090109', 'Absent'
UNION ALL SELECT 3, '20090110', 'Absent'
UNION ALL SELECT 3, '20090111', 'Present'
UNION ALL SELECT 3, '20090112', 'Present'
UNION ALL SELECT 3, '20090113', 'Present'
UNION ALL SELECT 3, '20090114', 'Present'
;WITH OrderDiff
AS
(
SELECT EmpId
,[Date]
,[Status]
,ROW_NUMBER() OVER (ORDER BY EmpId, [Date]) -
ROW_NUMBER() OVER (PARTITION BY [Status] ORDER BY EmpId, [Date]) As rnDiff
FROM @t
)
SELECT EmpId
,MIN([Date]) AS StartDate
,MAX([Date]) AS EndDate
,[Status]
FROM OrderDiff
GROUP BY EmpId, [Status], rnDiff
ORDER BY EmpId, StartDate
October 1, 2009 at 1:12 am
Thanks Ken. Your solution worked perfect 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply