SQL: find continuous date ranges across multiple rows?

Tags:

I'm trying to get a start and end date combination for continuous spans of time worked. The spans can cross multiple rows, where the end date of the first row is the same as the end date of the next row. The intended result is to show a continuous date range with the sum of hours worked for that range.

person  startdate                enddate                   hours
------  -----------------------  -----------------------  ------
5163    2013-04-29 07:00:00.000  2013-04-29 11:00:00.000    4.00
5163    2013-04-29 11:30:00.000  2013-04-29 15:30:00.000    4.00
5163    2013-04-29 15:30:00.000  2013-04-29 19:06:00.000    3.60
5851    2013-05-02 19:00:00.000  2013-05-02 23:00:00.000    4.00
5851    2013-05-02 23:00:00.000  2013-05-03 00:00:00.000    1.00
5851    2013-05-03 00:00:00.000  2013-05-03 00:31:00.000    0.52

From the above data, I want the following.

person  startdate                enddate                   hours
------  -----------------------  -----------------------  ------
5163    2013-04-29 07:00:00.000  2013-04-29 11:00:00.000    4.00
5163    2013-04-29 11:30:00.000  2013-04-29 19:06:00.000    7.60
5851    2013-05-02 19:00:00.000  2013-05-03 00:31:00.000    5.52

For each person and new (non-continuous) date span, compare the current row's enddate to the next row's startdate. If they are the same, accumulate the hours and continue processing rows until the enddate / startdate does not equal.

The environment is SQL Server 2008 R2. I tried queries involving self joins, using row_number and partition() functions, but have not been able to get a successful solution. Thanks!

edit: Here is the data flow for RichardTheKiwi's solution - I ran it for one person to see how much recursion is generated for a week's worth of punches.

declare @startdate datetime;
    set @startdate = '20130429';
declare @enddate datetime;
    set @enddate = '20130506';

with tbl as (
select 
PERSONNUM,
STARTDTM,
ENDDTM,
convert(decimal(10,2),1.0 * TIMEINSECONDS / 3600) as timeinhours
from vp_totals
where paycodetype = 'p'
and applydate >= @startdate and APPLYDATE < @enddate 
and (paycodename like '%regular%'
     or paycodename like '%overtime%'
     or PAYCODENAME like '%double time%')
and (PAYCODENAME not like '%shift premium%')
and PERSONNUM = 'loh-5851'
)

select * from tbl order by startdtm -- 27 rows


PERSONNUM       STARTDTM            ENDDTM               timeinhours 
LOH-5851        2013-04-29 14:30:00 2013-04-29 18:30:00  4.0000 
LOH-5851        2013-04-29 19:00:00 2013-04-29 23:00:00  4.0000 
LOH-5851        2013-04-29 23:00:00 2013-04-30 00:00:00  1.0000 
LOH-5851        2013-04-30 00:00:00 2013-04-30 00:11:00  0.1800 
LOH-5851        2013-04-30 14:45:00 2013-04-30 18:45:00  4.0000 
LOH-5851        2013-04-30 19:15:00 2013-04-30 23:00:00  3.7500 
LOH-5851        2013-04-30 23:00:00 2013-04-30 23:15:00  0.2500 
LOH-5851        2013-04-30 23:15:00 2013-05-01 00:00:00  0.7500 
LOH-5851        2013-05-01 00:00:00 2013-05-01 00:11:00  0.1800 
LOH-5851        2013-05-01 14:30:00 2013-05-01 18:30:00  4.0000 
LOH-5851        2013-05-01 19:00:00 2013-05-01 23:00:00  4.0000 
LOH-5851        2013-05-01 23:00:00 2013-05-02 00:00:00  1.0000 
LOH-5851        2013-05-02 00:00:00 2013-05-02 00:22:00  0.3700 
LOH-5851        2013-05-02 14:30:00 2013-05-02 18:30:00  4.0000 
LOH-5851        2013-05-02 19:00:00 2013-05-02 23:00:00  4.0000 
LOH-5851        2013-05-02 23:00:00 2013-05-03 00:00:00  1.0000 
LOH-5851        2013-05-03 00:00:00 2013-05-03 00:31:00  0.5200 
LOH-5851        2013-05-03 14:45:00 2013-05-03 17:45:00  3.0000 
LOH-5851        2013-05-03 17:45:00 2013-05-03 18:45:00  1.0000 
LOH-5851        2013-05-03 19:15:00 2013-05-03 23:00:00  3.7500 
LOH-5851        2013-05-03 23:00:00 2013-05-03 23:15:00  0.2500 
LOH-5851        2013-05-03 23:15:00 2013-05-04 00:00:00  0.7500 
LOH-5851        2013-05-04 00:00:00 2013-05-04 00:15:00  0.2500 
LOH-5851        2013-05-04 14:00:00 2013-05-04 18:00:00  4.0000 
LOH-5851        2013-05-04 18:30:00 2013-05-04 22:30:00  4.0000 
LOH-5851        2013-05-04 22:30:00 2013-05-04 23:00:00  0.5000 
LOH-5851        2013-05-04 23:00:00 2013-05-04 23:30:00  0.5000 


,cte as (
    select personnum, startdtm, enddtm, timeinhours
    from tbl
    union all
    select t.personnum, cte.startdtm, t.enddtm, cast(cte.timeinhours + t.timeinhours as decimal(10,2))
    from cte
    join tbl t on cte.personnum = t.personnum and cte.enddtm = t.startdtm
)

select * from cte order by startdtm, timeinhours option (maxrecursion 32000) -- 52 rows



personnum       startdtm            enddtm               timeinhours 
LOH-5851        2013-04-29 14:30:00 2013-04-29 18:30:00  4.0000 
LOH-5851        2013-04-29 19:00:00 2013-04-29 23:00:00  4.0000 
LOH-5851        2013-04-29 19:00:00 2013-04-30 00:00:00  5.0000 
LOH-5851        2013-04-29 19:00:00 2013-04-30 00:11:00  5.1800 
LOH-5851        2013-04-29 23:00:00 2013-04-30 00:00:00  1.0000 
LOH-5851        2013-04-29 23:00:00 2013-04-30 00:11:00  1.1800 
LOH-5851        2013-04-30 00:00:00 2013-04-30 00:11:00  0.1800 
LOH-5851        2013-04-30 14:45:00 2013-04-30 18:45:00  4.0000 
LOH-5851        2013-04-30 19:15:00 2013-04-30 23:00:00  3.7500 
LOH-5851        2013-04-30 19:15:00 2013-04-30 23:15:00  4.0000 
LOH-5851        2013-04-30 19:15:00 2013-05-01 00:00:00  4.7500 
LOH-5851        2013-04-30 19:15:00 2013-05-01 00:11:00  4.9300 
LOH-5851        2013-04-30 23:00:00 2013-04-30 23:15:00  0.2500 
LOH-5851        2013-04-30 23:00:00 2013-05-01 00:00:00  1.0000 
LOH-5851        2013-04-30 23:00:00 2013-05-01 00:11:00  1.1800 
LOH-5851        2013-04-30 23:15:00 2013-05-01 00:00:00  0.7500 
LOH-5851        2013-04-30 23:15:00 2013-05-01 00:11:00  0.9300 
LOH-5851        2013-05-01 00:00:00 2013-05-01 00:11:00  0.1800 
LOH-5851        2013-05-01 14:30:00 2013-05-01 18:30:00  4.0000 
LOH-5851        2013-05-01 19:00:00 2013-05-01 23:00:00  4.0000 
LOH-5851        2013-05-01 19:00:00 2013-05-02 00:00:00  5.0000 
LOH-5851        2013-05-01 19:00:00 2013-05-02 00:22:00  5.3700 
LOH-5851        2013-05-01 23:00:00 2013-05-02 00:00:00  1.0000 
LOH-5851        2013-05-01 23:00:00 2013-05-02 00:22:00  1.3700 
LOH-5851        2013-05-02 00:00:00 2013-05-02 00:22:00  0.3700 
LOH-5851        2013-05-02 14:30:00 2013-05-02 18:30:00  4.0000 
LOH-5851        2013-05-02 19:00:00 2013-05-02 23:00:00  4.0000 
LOH-5851        2013-05-02 19:00:00 2013-05-03 00:00:00  5.0000 
LOH-5851        2013-05-02 19:00:00 2013-05-03 00:31:00  5.5200 
LOH-5851        2013-05-02 23:00:00 2013-05-03 00:00:00  1.0000 
LOH-5851        2013-05-02 23:00:00 2013-05-03 00:31:00  1.5200 
LOH-5851        2013-05-03 00:00:00 2013-05-03 00:31:00  0.5200 
LOH-5851        2013-05-03 14:45:00 2013-05-03 17:45:00  3.0000 
LOH-5851        2013-05-03 14:45:00 2013-05-03 18:45:00  4.0000 
LOH-5851        2013-05-03 17:45:00 2013-05-03 18:45:00  1.0000 
LOH-5851        2013-05-03 19:15:00 2013-05-03 23:00:00  3.7500 
LOH-5851        2013-05-03 19:15:00 2013-05-03 23:15:00  4.0000 
LOH-5851        2013-05-03 19:15:00 2013-05-04 00:00:00  4.7500 
LOH-5851        2013-05-03 19:15:00 2013-05-04 00:15:00  5.0000 
LOH-5851        2013-05-03 23:00:00 2013-05-03 23:15:00  0.2500 
LOH-5851        2013-05-03 23:00:00 2013-05-04 00:00:00  1.0000 
LOH-5851        2013-05-03 23:00:00 2013-05-04 00:15:00  1.2500 
LOH-5851        2013-05-03 23:15:00 2013-05-04 00:00:00  0.7500 
LOH-5851        2013-05-03 23:15:00 2013-05-04 00:15:00  1.0000 
LOH-5851        2013-05-04 00:00:00 2013-05-04 00:15:00  0.2500 
LOH-5851        2013-05-04 14:00:00 2013-05-04 18:00:00  4.0000 
LOH-5851        2013-05-04 18:30:00 2013-05-04 22:30:00  4.0000 
LOH-5851        2013-05-04 18:30:00 2013-05-04 23:00:00  4.5000 
LOH-5851        2013-05-04 18:30:00 2013-05-04 23:30:00  5.0000 
LOH-5851        2013-05-04 22:30:00 2013-05-04 23:00:00  0.5000 
LOH-5851        2013-05-04 22:30:00 2013-05-04 23:30:00  1.0000 
LOH-5851        2013-05-04 23:00:00 2013-05-04 23:30:00  0.5000 



,cte2 as (
    select *, rn = row_number() over (partition by personnum, enddtm order by startdtm)
    from cte
)

select * from cte2 order by startdtm, rn -- 52 rows


personnum       startdtm            enddtm               timeinhours        rn
LOH-5851        2013-04-29 14:30:00 2013-04-29 18:30:00  4.0000             1
LOH-5851        2013-04-29 19:00:00 2013-04-29 23:00:00  4.0000             1
LOH-5851        2013-04-29 19:00:00 2013-04-30 00:00:00  5.0000             1
LOH-5851        2013-04-29 19:00:00 2013-04-30 00:11:00  5.1800             1
LOH-5851        2013-04-29 23:00:00 2013-04-30 00:11:00  1.1800             2
LOH-5851        2013-04-29 23:00:00 2013-04-30 00:00:00  1.0000             2
LOH-5851        2013-04-30 00:00:00 2013-04-30 00:11:00  0.1800             3
LOH-5851        2013-04-30 14:45:00 2013-04-30 18:45:00  4.0000             1
LOH-5851        2013-04-30 19:15:00 2013-04-30 23:00:00  3.7500             1
LOH-5851        2013-04-30 19:15:00 2013-04-30 23:15:00  4.0000             1
LOH-5851        2013-04-30 19:15:00 2013-05-01 00:11:00  4.9300             1
LOH-5851        2013-04-30 19:15:00 2013-05-01 00:00:00  4.7500             1
LOH-5851        2013-04-30 23:00:00 2013-05-01 00:00:00  1.0000             2
LOH-5851        2013-04-30 23:00:00 2013-05-01 00:11:00  1.1800             2
LOH-5851        2013-04-30 23:00:00 2013-04-30 23:15:00  0.2500             2
LOH-5851        2013-04-30 23:15:00 2013-05-01 00:11:00  0.9300             3
LOH-5851        2013-04-30 23:15:00 2013-05-01 00:00:00  0.7500             3
LOH-5851        2013-05-01 00:00:00 2013-05-01 00:11:00  0.1800             4
LOH-5851        2013-05-01 14:30:00 2013-05-01 18:30:00  4.0000             1
LOH-5851        2013-05-01 19:00:00 2013-05-01 23:00:00  4.0000             1
LOH-5851        2013-05-01 19:00:00 2013-05-02 00:00:00  5.0000             1
LOH-5851        2013-05-01 19:00:00 2013-05-02 00:22:00  5.3700             1
LOH-5851        2013-05-01 23:00:00 2013-05-02 00:22:00  1.3700             2
LOH-5851        2013-05-01 23:00:00 2013-05-02 00:00:00  1.0000             2
LOH-5851        2013-05-02 00:00:00 2013-05-02 00:22:00  0.3700             3
LOH-5851        2013-05-02 14:30:00 2013-05-02 18:30:00  4.0000             1
LOH-5851        2013-05-02 19:00:00 2013-05-02 23:00:00  4.0000             1
LOH-5851        2013-05-02 19:00:00 2013-05-03 00:00:00  5.0000             1
LOH-5851        2013-05-02 19:00:00 2013-05-03 00:31:00  5.5200             1
LOH-5851        2013-05-02 23:00:00 2013-05-03 00:31:00  1.5200             2
LOH-5851        2013-05-02 23:00:00 2013-05-03 00:00:00  1.0000             2
LOH-5851        2013-05-03 00:00:00 2013-05-03 00:31:00  0.5200             3
LOH-5851        2013-05-03 14:45:00 2013-05-03 17:45:00  3.0000             1
LOH-5851        2013-05-03 14:45:00 2013-05-03 18:45:00  4.0000             1
LOH-5851        2013-05-03 17:45:00 2013-05-03 18:45:00  1.0000             2
LOH-5851        2013-05-03 19:15:00 2013-05-03 23:00:00  3.7500             1
LOH-5851        2013-05-03 19:15:00 2013-05-03 23:15:00  4.0000             1
LOH-5851        2013-05-03 19:15:00 2013-05-04 00:00:00  4.7500             1
LOH-5851        2013-05-03 19:15:00 2013-05-04 00:15:00  5.0000             1
LOH-5851        2013-05-03 23:00:00 2013-05-04 00:15:00  1.2500             2
LOH-5851        2013-05-03 23:00:00 2013-05-04 00:00:00  1.0000             2
LOH-5851        2013-05-03 23:00:00 2013-05-03 23:15:00  0.2500             2
LOH-5851        2013-05-03 23:15:00 2013-05-04 00:00:00  0.7500             3
LOH-5851        2013-05-03 23:15:00 2013-05-04 00:15:00  1.0000             3
LOH-5851        2013-05-04 00:00:00 2013-05-04 00:15:00  0.2500             4
LOH-5851        2013-05-04 14:00:00 2013-05-04 18:00:00  4.0000             1
LOH-5851        2013-05-04 18:30:00 2013-05-04 22:30:00  4.0000             1
LOH-5851        2013-05-04 18:30:00 2013-05-04 23:00:00  4.5000             1
LOH-5851        2013-05-04 18:30:00 2013-05-04 23:30:00  5.0000             1
LOH-5851        2013-05-04 22:30:00 2013-05-04 23:30:00  1.0000             2
LOH-5851        2013-05-04 22:30:00 2013-05-04 23:00:00  0.5000             2
LOH-5851        2013-05-04 23:00:00 2013-05-04 23:30:00  0.5000             3


select personnum, startdtm, max(enddtm) enddtm, max(timeinhours) timeinhours
from cte2
where rn=1
group by personnum, startdtm
order by personnum, startdtm
option (maxrecursion 32000) -- 12 rows


personnum       startdtm            enddtm               timeinhours 
LOH-5851        2013-04-29 14:30:00 2013-04-29 18:30:00  4.0000 
LOH-5851        2013-04-29 19:00:00 2013-04-30 00:11:00  5.1800 
LOH-5851        2013-04-30 14:45:00 2013-04-30 18:45:00  4.0000 
LOH-5851        2013-04-30 19:15:00 2013-05-01 00:11:00  4.9300 
LOH-5851        2013-05-01 14:30:00 2013-05-01 18:30:00  4.0000 
LOH-5851        2013-05-01 19:00:00 2013-05-02 00:22:00  5.3700 
LOH-5851        2013-05-02 14:30:00 2013-05-02 18:30:00  4.0000 
LOH-5851        2013-05-02 19:00:00 2013-05-03 00:31:00  5.5200 
LOH-5851        2013-05-03 14:45:00 2013-05-03 18:45:00  4.0000 
LOH-5851        2013-05-03 19:15:00 2013-05-04 00:15:00  5.0000 
LOH-5851        2013-05-04 14:00:00 2013-05-04 18:00:00  4.0000 
LOH-5851        2013-05-04 18:30:00 2013-05-04 23:30:00  5.0000

The query works perfectly for small amounts of data, but when run for the expected employee population for a pay period (usually one week), the ugly max recursions error message appears.

edit edit: see the comments for Richard's fix for the recursion issue.

467

asked May 16 '13 19:05

Hanna Goodbar

1 Answers

Sample data

create table tbl (person int, startdate datetime, enddate datetime, hours decimal(10,2));
insert tbl values
(5163 ,'2013-04-29 07:00:00.000' ,'2013-04-29 11:00:00.000', 4.00),
(5163 ,'2013-04-29 11:30:00.000' ,'2013-04-29 15:30:00.000', 4.00),
(5163 ,'2013-04-29 15:30:00.000' ,'2013-04-29 19:06:00.000', 3.60),
(5851 ,'2013-05-02 19:00:00.000' ,'2013-05-02 23:00:00.000', 4.00),
(5851 ,'2013-05-02 23:00:00.000' ,'2013-05-03 00:00:00.000', 1.00),
(5851 ,'2013-05-03 00:00:00.000' ,'2013-05-03 00:31:00.000', 0.52);

The query

;with cte as (
    select person, startdate, enddate, hours
    from tbl
    union all
    select t.person, cte.startdate, t.enddate, cast(cte.hours + t.hours as decimal(10,2))
    from cte
    join tbl t on cte.person = t.person and cte.enddate = t.startdate
), cte2 as (
    select *, rn = row_number() over (partition by person, enddate order by startdate)
    from cte
)
select person, startdate, max(enddate) enddate, max(hours) hours
from cte2
where rn=1
group by person, startdate
order by person, startdate;

Results

person      startdate               enddate                 hours
----------- ----------------------- ----------------------- -------
5163        2013-04-29 07:00:00.000 2013-04-29 11:00:00.000 4.00
5163        2013-04-29 11:30:00.000 2013-04-29 19:06:00.000 7.60
5851        2013-05-02 19:00:00.000 2013-05-03 00:31:00.000 5.52

answered Dec 17 '22 12:12

RichardTheKiwi

Related questions
                            
                                How to decode nvarchar to text (SQL Server 2008 R2)?
                            
                                Delay in using fulltext search in SQL Server
                            
                                Split a string and return greatest in mssql
                            
                                Sorting/Grouping SQL by day-of-week and hour-of-day
                            
                                Scripts all Sprocs at once with DROP and CREATE
                            
                                What are T-SQL Statement Blocks?
                            
                                is sql server transaction atomic
                            
                                Asynchronous ADO.NET
                            
                                SELECT CASE vs. CASE IN SQL
                            
                                How are SQL Server sysschedules intervals calculated?
                            
                                Hive vs SQL Server performance
                            
                                Confused about nvarchar limit
                            
                                Will SQL Server Service Broker be supported in SQL Azure in future?
                            
                                Properly using the Count Function
                            
                                GROUP-BY expression must contain at least one column that is not an outer reference
                            
                                SQL Server .nodes() XML parent nodes by name
                            
                                Biztalk Adapter Pack install - not seeing WCF-SQL adapter
                            
                                How to find the number of concurrent SQL Server connections
                            
                                query SQL Server from PostgreSQL
                            
                                How to determine what is compiling in SQL Server

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!

Donate Us With

SQL: find continuous date ranges across multiple rows?

Tags:

sql-server

tsql

sql-server-2008

sql-server-2008-r2

Hanna Goodbar

People also ask

1 Answers

RichardTheKiwi

Recent Activity

Donate For Us