SQL Server Query to group sequential dates

Question

Post reply

SQL Server Query to group sequential dates

p.ramchander

Ten Centuries

Points: 1171
More actions
September 19, 2012 at 2:47 am

#266773

I have a table named Absence Details and i want a to group sequential dates . Here is the data
EIDAbsenceTypeAbsenceStartDateAbsenceEndDate
769Holiday2012-06-25 00:00:00.0002012-06-25 23:59:59.000
769Holiday2012-06-26 00:00:00.0002012-06-26 23:59:59.000
769Holiday2012-09-03 00:00:00.0002012-09-03 23:59:59.000
769Holiday2012-09-04 00:00:00.0002012-09-04 23:59:59.000
769Holiday2012-09-05 00:00:00.0002012-09-05 23:59:59.000
769Holiday2012-09-06 00:00:00.0002012-09-06 23:59:59.000
769Holiday2012-09-07 00:00:00.0002012-09-07 23:59:59.000
The result i am trying to get is
EIDAbsenceTypeAbsenceStartDateAbsenceEndDate
769Holiday2012-06-25 00:00:00.0002012-06-26 23:59:59.000
769Holiday2012-09-03 00:00:00.0002012-09-07 23:59:59.000
Any help is much appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Davin21 SSC Eights! Points: 860 More actions · Answer 1

Is the difference between the start date and end date of each row always fixed to be one day?

Assuming it, something like this should do the trick:

;with sample_data AS

(

SELECT 769 AS EID, 'Holiday' AS absencetype,'2012-06-25 00:00:00.000' as AbsenceStartDate,'2012-06-25 23:59:59.000' as AbsenceEndDate

UNION ALL SELECT 769,'Holiday','2012-06-26 00:00:00.000','2012-06-26 23:59:59.000'

UNION ALL SELECT 769,'Holiday','2012-09-03 00:00:00.000','2012-09-03 23:59:59.000'

UNION ALL SELECT 769,'Holiday','2012-09-04 00:00:00.000','2012-09-04 23:59:59.000'

UNION ALL SELECT 769,'Holiday','2012-09-05 00:00:00.000','2012-09-05 23:59:59.000'

UNION ALL SELECT 769,'Holiday','2012-09-06 00:00:00.000','2012-09-06 23:59:59.000'

UNION ALL SELECT 769,'Holiday','2012-09-07 00:00:00.000','2012-09-07 23:59:59.000'

)

,sample_data2 AS

(

SELECT

EID

,absencetype

,DENSE_RANK() OVER (PARTITION BY EID ORDER BY DATEPART(YEAR, absencestartdate)*12 + DATEPART(MONTH, absencestartdate)) AS rnk

,absencestartdate

,AbsenceendDate

FROM sample_data

)

SELECT

EID

,absencetype

,MIN(absencestartdate) as range_sart

,MAX(AbsenceendDate) as range_end

FROM sample_data2

GROUP BY

EID

,absencetype

,rnk

p.ramchander Ten Centuries Points: 1171 More actions · Answer 2

yes, the difference between the start date and end date is fixed to 1 day difference.

SomewhereSomehow SSCommitted Points: 1586 More actions · Answer 3

If I undestood correctly from desired results, what do you want, is grouping by month?

If so, it might be smth like this:

declare @t table (EID int,AbsenceType varchar(10), AbsenceStartDate datetime,AbsenceEndDate datetime);

insert @t values

(769,'Holiday','2012-06-25T00:00:00.000','2012-06-25T23:59:59.000'),

(769,'Holiday','2012-06-26T00:00:00.000','2012-06-26T23:59:59.000'),

(769,'Holiday','2012-09-03T00:00:00.000','2012-09-03T23:59:59.000'),

(769,'Holiday','2012-09-04T00:00:00.000','2012-09-04T23:59:59.000'),

(769,'Holiday','2012-09-05T00:00:00.000','2012-09-05T23:59:59.000'),

(769,'Holiday','2012-09-06T00:00:00.000','2012-09-06T23:59:59.000'),

(769,'Holiday','2012-09-07T00:00:00.000','2012-09-07T23:59:59.000')

;

select

EID,

AbsenceType,

AbsenceStartDate = min(AbsenceStartDate),

AbsenceEndDate = max(AbsenceEndDate)

from @t

group by

EID, AbsenceType, year(AbsenceStartDate), month(AbsenceStartDate)

--TO THINK OF: how we should group if interval starts in one month/year and ends in another?

If not, please give more explanations...

I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru[/url]
Twitter: @SomewereSomehow

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

p.ramchander (9/19/2012)
yes, the difference between the start date and end date is fixed to 1 day difference.

Then you can do this

WITH CTE AS (

SELECT EID, AbsenceType, AbsenceStartDate ,AbsenceEndDate,

AbsenceStartDate - ROW_NUMBER() OVER(PARTITION BY EID, AbsenceType ORDER BY AbsenceStartDate) AS rn

FROM AbsenceDetails)

SELECT EID, AbsenceType,

MIN(AbsenceStartDate) AS AbsenceStartDate,

MAX(AbsenceEndDate) AS AbsenceEndDate

FROM CTE

GROUP BY EID, AbsenceType,rn;

____________________________________________________

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

p.ramchander Ten Centuries Points: 1171 More actions · Answer 5

Not necessarily grouping by months, there are chances that the interval starts end of month and ends in first week on next month

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

Sample data used: -

SELECT EID,AbsenceType,AbsenceStartDate,AbsenceEndDate

INTO #yourSampleData

FROM (VALUES(769,'Holiday','2012-06-25 00:00:00.000','2012-06-25 23:59:59.000'),

(769,'Holiday','2012-06-26 00:00:00.000','2012-06-26 23:59:59.000'),

(769,'Holiday','2012-09-03 00:00:00.000','2012-09-03 23:59:59.000'),

(769,'Holiday','2012-09-04 00:00:00.000','2012-09-04 23:59:59.000'),

(769,'Holiday','2012-09-05 00:00:00.000','2012-09-05 23:59:59.000'),

(769,'Holiday','2012-09-06 00:00:00.000','2012-09-06 23:59:59.000'),

(769,'Holiday','2012-09-07 00:00:00.000','2012-09-07 23:59:59.000')

)a(EID,AbsenceType,AbsenceStartDate,AbsenceEndDate);

Answer to your question: -

SELECT EID, AbsenceType, MIN(AbsenceStartDate) AS AbsenceStartDate, MAX(AbsenceEndDate) AS AbsenceEndDate

FROM (SELECT EID, AbsenceType, AbsenceStartDate, AbsenceEndDate,

DATEADD(dd, - ROW_NUMBER() OVER (PARTITION BY EID, AbsenceType ORDER BY EID,AbsenceStartDate), AbsenceStartDate)

FROM #yourSampleData

GROUP BY EID,AbsenceType,AbsenceStartDate,AbsenceEndDate

) a(EID, AbsenceType, AbsenceStartDate, AbsenceEndDate, Grp)

GROUP BY EID, AbsenceType, Grp;

Result: -

EID AbsenceType AbsenceStartDate AbsenceEndDate

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

769 Holiday 2012-06-25 00:00:00.000 2012-06-26 23:59:59.000

769 Holiday 2012-09-03 00:00:00.000 2012-09-07 23:59:59.000

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

p.ramchander Ten Centuries Points: 1171 More actions · Answer 7

p.ramchander

Ten Centuries

Points: 1171

September 19, 2012 at 3:13 am

#1539450

Thanks a ton mike your solution worked for me.

p.ramchander Ten Centuries Points: 1171 More actions · Answer 8

p.ramchander

Ten Centuries

Points: 1171

September 19, 2012 at 3:16 am

#1539453

Thanks Cadavre. your solution also worked.