SQL Server Query to Group Sequential Dates

Question

I have a table called Absence Details, and I want to group consecutive dates. Here are the data

EID AbsenceType AbsenceStartDate AbsenceEndDate 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 

The result that I am trying to get is

 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 

Any help is greatly appreciated.

Answer 1

I simplified your script to isolate the main problem. Assume this table with spaces:

 with ns as ( select 1 as n union select 2 as n union select 3 as n union select 8 as n union --gap select 9 as n ) select * into #ns from ns; 

Now the result you expect is the following:

 ini fi --- -- 1 3 8 9 

To get these results, I massage the data this way: first I create two views with start and end periods, and secondly, I join both views to get the final result. Note that I join the table with it myself to find the start and end periods:

 with inis as -- identifying start periods ( select n1.n from #ns n1 left outer join #ns n2 on n1.n = n2.n + 1 where n2.n is null ), fis as -- identifying ends periods ( select n1.n from #ns n1 left outer join #ns n2 on n1.n = n2.n - 1 where n2.n is null ) select inis.n as ini, min( fis.n ) as fi -- joining starts and ends from inis inner join fis on inis.n <= fis.n group by inis.n ; 

You can transfer this method to your data and data types. If you have questions about translating questions, you can ask.

Check query and results.

Answer 2

Here is a solution that worked for me.

 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 AbsenceDetails GROUP BY EID,AbsenceType,AbsenceStartDate,AbsenceEndDate ) a(EID, AbsenceType, AbsenceStartDate, AbsenceEndDate, Grp) GROUP BY EID, AbsenceType, Grp; 

Answer 3

I would do it like this:

• Define a list of absence dates.

   SELECT ad1.EID, ad1.StartDate, ad2.EndDate FROM AbsenceDetails ad1 JOIN AbsenceDetails ad2 ON ad1.EID = ad2.EID WHERE DATEDIFF(ss, ad1.EndDate, ad2.StartDate) = 1 

  The results will be as follows:

   769 2012-06-25 00:00:00.000 2012-06-26 23:59:59.000 769 2012-09-03 00:00:00.000 2012-09-04 23:59:59.000 769 2012-09-04 00:00:00.000 2012-09-05 23:59:59.000 769 2012-09-05 00:00:00.000 2012-09-06 23:59:59.000 769 2012-09-06 00:00:00.000 2012-09-07 23:59:59.000 

• Iterate over the list and determine the start and end duration of each stretch. This is best done at the application level.

Answer 4

If I understand your question correctly, you want to find continuous intervals in your notes.
The main problem will be to determine what is actually a continuous time interval:
If you are looking for a lack of work than any sequence

 date1.09:00 to date1.18:00 date2.09:00 to date2.18:00 

where date2 is the next business day after date1 can be considered continuous.

In your case, this is relatively easy, but you cannot do it in a single request. At least I can't figure out how to do it right now.

PS The "Islands and Spaces" algorithm proposed by "podiluska" will help you write it in a single request / stored procedure.