安排日期的行

admin

安排日期的行

sql

我有如下数据：

StartDate     EndDate    Duration 
----------
41890         41892       3
41898         41900       3
41906         41907       2
41910         41910       1

StartDate和EndDate是日历中任何日期的各自ID值。我想计算连续几天的工期总和。我想在这里包括周末。例如，在上面的数据中，假设41908和41909是周末，那么我所需的结果集应如下所示。

我已经有了另一个可以在下一个工作日返回我的proc，即，如果我在该proc中传递41907或41908或41909作为DateID，它将在下一个工作日返回41910。基本上，我想检查传递上述EndDateID时proc返回的DateID是否与上述数据中的下一个StartDateID相同，那么这两行都应合并在一起。以下是我要获取的数据。

ID          StartDate     EndDate    Duration 
----------
278457        41890       41892       3
278457        41898       41900       3
278457        41906       41910       3

如果要求不明确，请告诉我，我可以进一步解释。

我的日期表如下所示：

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代码：

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')

阅读 138

2021-06-07

共1个答案

admin

这相当复杂。这是使用窗口函数的一种方法。

首先，使用日期表枚举没有周末的日期（如果需要，您也可以休假）。然后，通过使用非等额合并，将周期扩展为每行一天。

然后，您可以使用技巧来确定连续的日期。这个技巧是为每个id生成一个序号，然后从日期的序号中减去它。这是连续几天的常数。最后一步只是一个汇总。

结果查询如下所示：

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上的版本：

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;

我相信这是可行的，但日期表中并未包含所有日期。

2021-06-07