I'm trying to build a view in SQL which identifies and groups records with consecutive date ranges, and returns the earliest and latest date

Question

I have a table of absence days, and I'm trying to build a view that will find all absence records for the same person which are consecutive, and returns the earliest consecutive date and latest consecutive date as well as the total days and hours - I.e. return the summary information for that single period of absence, even if it was entered as multiple consecutive records.

Below is an example of how the current data looks, and what information I am trying to return

Original Data:

AbsenceID   PersonID   AbsenceDay   Days   Hours  
49334       9552       26/05/2020   0.5    3
49334       9552       27/05/2020   0.5    3
50000       9552       28/05/2020   1      7
50000       9552       29/05/2020   1      6
50010       9552       15/06/2020   1      6.5
50010       9552       16/06/2020   0.5    3.25
49334       8030       01/06/2020   0.5    4
49334       8030       02/06/2020   0.5    4
49334       8030       03/06/2020   0.5    4
49334       8030       04/06/2020   0.5    4
49334       8030       05/06/2020   0.5    4
49334       8030       06/06/2020   0.5    4
60012       8030       15/06/2020   0.5    3
60012       8030       16/06/2020   0.5    3

New View:

AbsenceID  PersonID    PeriodStart  PeriodEnd      TotalDays   TotalHours
49334      9552        26/05/2020   29/05/2020     3           19
50000      9552        26/05/2020   29/05/2020     3           19
50010      9552        15/06/2020   16/06/2020     1.5         9.75
49334      8030        01/06/2020   06/06/2020     3           24
60012      8030        15/06/2020   16/06/2020     1           6

So, what is your question please read through how to ask question in stack overflow, stack overflow is for helping community for developer whock stuck in their problems and its not for giving an answer to tasks. — jaibalaji, Jun 24 '20 at 11:26
Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, Jun 24 '20 at 12:24

score 0 · Answer 1 · answered Jun 24 '20 at 11:04

This is a type of gaps-and-islands problem. You can identify the adjacent ranges by subtracting an enumerated value -- row number. It will be constant for adjacent dates:

select min(absenceId), personID, min(absenceday), max(absenceday),
       sum(days), sum(hours)
from (select t.*,
             row_number() over (partition by PersonID order by absenceday) as seqnum
      from t
     ) t
group by personID, dateadd(day, -seqnum, absenceday);

Note: This answers the question that you asked:

return the summary information for that single period of absence, even if it was entered as multiple consecutive records.

Your sample results duplicate the first period of absence. This returns only one row for that period.

I'm trying to build a view in SQL which identifies and groups records with consecutive date ranges, and returns the earliest and latest date

1 Answers1