September 19, 2012 at 2:47 am
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.
September 19, 2012 at 2:54 am
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
September 19, 2012 at 2:58 am
yes, the difference between the start date and end date is fixed to 1 day difference.
September 19, 2012 at 3:01 am
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...
September 19, 2012 at 3:06 am
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/61537September 19, 2012 at 3:10 am
Not necessarily grouping by months, there are chances that the interval starts end of month and ends in first week on next month
September 19, 2012 at 3:12 am
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
September 19, 2012 at 3:13 am
Thanks a ton mike your solution worked for me.
September 19, 2012 at 3:16 am
Thanks Cadavre. your solution also worked.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply