How to Group By Consecutive Dates? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to Group By Consecutive Dates?

Author

Topic

KevMull
Starting Member

11 Posts

Posted - 2009-10-02 : 06:05:21

My table (tblVacation) records employees’ work leave/vacation as follows…

Id(pk), empId, vacationDate

1, 007, 01/20/2009
2, 007, 01/21/2009
3, 007, 01/22/2009
4, 007, 01/25/2009
5, 007, 01/28/2009
6, 007, 01/29/2009
7, 008, 04/17/2009
8, 008, 04/18/2009
9, 008, 06/11/2009

I need to created a view/sp that shows consecutive dates grouped as one record with the final date being the ‘end date’ in a new column, all in a single row. Single dates would also show but have the same start and end date.

So based on the above table it would look something like…

empId, startDate, endDate

007, 01/20/2009, 01/22/2009
007, 01/25/2009, 01/25/2009
007, 01/28/2009, 01/29/2009
008, 04/17/2009, 04/18/2009
008, 06/11/2009, 06/11/2009

Many Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 08:54:21

this should give you a start

http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Another-running-streaks-algorithm.aspx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-02 : 08:56:16

[code]
DECLARE @sample TABLE
(
Id int,
empId varchar(3),
vacationDate datetime
)
INSERT INTO @sample
SELECT 1, '007', '01/20/2009' UNION ALL
SELECT 2, '007', '01/21/2009' UNION ALL
SELECT 3, '007', '01/22/2009' UNION ALL
SELECT 4, '007', '01/25/2009' UNION ALL
SELECT 5, '007', '01/28/2009' UNION ALL
SELECT 6, '007', '01/29/2009' UNION ALL
SELECT 7, '008', '04/17/2009' UNION ALL
SELECT 8, '008', '04/18/2009' UNION ALL
SELECT 9, '008', '06/11/2009'

; WITH
data (empId, vacationDate, seqNo)
AS
(
SELECT empId,
vacationDate,
seqNo = row_number() OVER (PARTITION BY empId ORDER BY vacationDate)
FROM @sample
),
r_cte
AS
(
-- anchor member
SELECT empId, vacationDate, seqNo, startDate = vacationDate
FROM data
WHERE seqNo = 1

UNION ALL

-- recursive member
SELECT d.empId, d.vacationDate, d.seqNo,
startDate = CASE WHEN c.vacationDate = DATEADD(DAY, -1, d.vacationDate)
THEN c.startDate
ELSE d.vacationDate
END
FROM r_cte c
INNER JOIN data d ON c.empId = d.empId
AND c.seqNo = d.seqNo - 1
)
SELECT empId, startDate, endDate = MAX(vacationDate)
FROM r_cte
GROUP BY empId, startDate

/*
empId startDate endDate
----- ----------- -----------
007 2009-01-20 2009-01-22
007 2009-01-25 2009-01-25
007 2009-01-28 2009-01-29
008 2009-04-17 2009-04-18
008 2009-06-11 2009-06-11

(5 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources