SQL:跨多行查找连续日期范围?
我正在尝试获取连续工作时间跨度的开始和结束日期组合.跨度可以跨多行，其中第一行的结束日期与下一行的结束日期相同.预期结果是显示一个连续的日期范围以及该范围内的工作小时数.
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
对于每个人和新的(非连续的)日期跨度，将当前行的结束日期与下一行的开始日期进行比较.如果它们相同，则累积小时数并继续处理行，直到 enddate/startdate 不相等.
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.
环境是 SQL Server 2008 R2.我尝试了涉及自连接的查询，使用 row_number 和 partition() 函数，但未能获得成功的解决方案.谢谢！
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!
这是 RichardTheKiwi 解决方案的数据流 - 我为一个人运行了它，看看一周的出拳会产生多少递归.
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 请参阅 Richard 对递归问题的修复的评论.
edit edit: see the comments for Richard's fix for the recursion issue.
推荐答案
示例数据
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);
查询
;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;
结果
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
相关文章