为工作日俱乐部排行
我有如下数据:
StartDate EndDate Duration
----------
41890 41892 3
41898 41900 3
41906 41907 2
41910 41910 1
StartDate 和 EndDate 是日历中任何日期的各自 ID 值.我想计算连续几天的持续时间总和.在这里，我想包括周末的日子.例如.在上面的数据中，假设 41908 和 41909 是周末，那么我需要的结果集应该如下所示.
StartDate and EndDate are respective ID values for any dates from calendar. I want to calculate the sum of duration for consecutive days. Here I want to include the days which are weekends. E.g. in the above data, let's say 41908 and 41909 are weekends, then my required result set should look like below.
我已经有另一个 proc 可以在下一个工作日返回我，即如果我在该 proc 中传递 41907 或 41908 或 41909 作为 DateID，它将在下一个工作日返回 41910.基本上我想检查当我传递上面的 EndDateID 时我的 proc 返回的 DateID 是否与上面数据中的下一个 StartDateID 相同，然后这两行都应该被打成棍子.下面是我想要获取的数据.
I already have another proc that can return me the next working day, i.e. if I pass 41907 or 41908 or 41909 as DateID in that proc, it will return 41910 as the next working day. Basically I want to check if the DateID returned by my proc when I pass the above EndDateID is same as the next StartDateID from above data, then both the rows should be clubbed. Below is the data I want to get.
ID StartDate EndDate Duration
----------
278457 41890 41892 3
278457 41898 41900 3
278457 41906 41910 3
如果要求不明确，请告诉我，我可以进一步解释.
Please let me know in case the requirement is not clear, I can explain further.
我的日期表如下:
DateId Date Day
----------
41906 09-04-2014 Thursday
41907 09-05-2014 Friday
41908 09-06-2014 Saturdat
41909 09-07-2014 Sunday
41910 09-08-2014 Monday
这是用于设置的 SQL 代码:
Here is the SQL Code for setup:
CREATE TABLE Table1
(
StartDate INT,
EndDate INT,
LeaveDuration INT
)
INSERT INTO Table1
VALUES(41890, 41892, 3),
(41898, 41900, 3),
(41906, 41907, 3),
(41910, 41910, 1)
CREATE TABLE DateTable
(
DateID INT,
Date DATETIME,
Day VARCHAR(20)
)
INSERT INTO DateTable
VALUES(41907, '09-05-2014', 'Friday'),
(41908, '09-06-2014', 'Saturday'),
(41909, '09-07-2014', 'Sunday'),
(41910, '09-08-2014', 'Monday'),
(41911, '09-09-2014', 'Tuesday')
推荐答案
这相当复杂.这是使用窗口函数的方法.
This is rather complicated. Here is an approach using window functions.
首先用日期表枚举没有周末的日期(如果你想也可以去掉假期).然后，使用非等值联接将期间扩展为每行一天.
First, use the date table to enumerate the dates without weekends (you can also take out holidays if you want). Then, expand the periods into one day per row, by using a non-equijoin.
然后，您可以使用一种技巧来确定连续的日子.这个技巧是为每个 id 生成一个序列号，然后从日期的序列号中减去它.这是连续天数的常数.最后一步只是一个聚合.
You can then use a trick to identify sequential days. This trick is to generate a sequential number for each id and subtract it from the sequential number for the dates. This is a constant for sequential days. The final step is simply an aggregation.
查询结果如下:
with d as (
select d.*, row_number() over (order by date) as seqnum
from dates d
where day not in ('Saturday', 'Sunday')
)
select t.id, min(t.date) as startdate, max(t.date) as enddate, sum(duration)
from (select t.*, ds.seqnum, ds.date,
(d.seqnum - row_number() over (partition by id order by ds.date) ) as grp
from table t join
d ds
on ds.date between t.startdate and t.enddate
) t
group by t.id, grp;
以下是这个 SQL Fiddle的版本:
The following is the version on this SQL Fiddle:
with d as (
select d.*, row_number() over (order by date) as seqnum
from datetable d
where day not in ('Saturday', 'Sunday')
)
select t.id, min(t.date) as startdate, max(t.date) as enddate, sum(duration)
from (select t.*, ds.seqnum, ds.date,
(ds.seqnum - row_number() over (partition by id order by ds.date) ) as grp
from (select t.*, 'abc' as id from table1 t) t join
d ds
on ds.dateid between t.startdate and t.enddate
) t
group by grp;
我相信这是有效的，但日期表中没有包含所有日期.
I believe this is working, but the date table doesn't have all the dates in it.
相关文章