問題描述
我有如下數(shù)據(jù):
StartDate EndDate Duration
----------
41890 41892 3
41898 41900 3
41906 41907 2
41910 41910 1
StartDate 和 EndDate 是日歷中任何日期的各自 ID 值.我想計(jì)算連續(xù)幾天的持續(xù)時(shí)間總和.在這里，我想包括周末的日子.例如.在上面的數(shù)據(jù)中，假設(shè) 41908 和 41909 是周末，那么我需要的結(jié)果集應(yīng)該如下所示.
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.
我已經(jīng)有另一個(gè) proc 可以在下一個(gè)工作日返回我，即如果我在該 proc 中傳遞 41907 或 41908 或 41909 作為 DateID，它將在下一個(gè)工作日返回 41910.基本上我想檢查當(dāng)我傳遞上面的 EndDateID 時(shí)我的 proc 返回的 DateID 是否與上面數(shù)據(jù)中的下一個(gè) StartDateID 相同，然后這兩行都應(yīng)該被打成棍子.下面是我想要獲取的數(shù)據(jù).
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
如果要求不明確，請(qǐng)告訴我，我可以進(jìn)一步解釋.
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
這是用于設(shè)置的 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')
推薦答案
這相當(dāng)復(fù)雜.這是使用窗口函數(shù)的方法.
This is rather complicated. Here is an approach using window functions.
首先用日期表枚舉沒有周末的日期(如果你想也可以去掉假期).然后，使用非等值聯(lián)接將期間擴(kuò)展為每行一天.
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.
然后，您可以使用一種技巧來確定連續(xù)的日子.這個(gè)技巧是為每個(gè) id 生成一個(gè)序列號(hào)，然后從日期的序列號(hào)中減去它.這是連續(xù)天數(shù)的常數(shù).最后一步只是一個(gè)聚合.
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.
查詢結(jié)果如下:
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;
以下是這個(gè) 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.
這篇關(guān)于為工作日俱樂部排行的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！