SQL Server:在上一个日期和当前日期之间的数天差异

Question

我一直在尝试找到一种方法来计算仅计数工作日期和当前行的两个日期之间的几天差异。

这里的示例数据和标准。

  ID        StartDate       EndDate         NewDate        DaysDifference
 ========================================================================
  0         04/05/2017      null       
  1         12/06/2017     16/06/2017      12/06/2017         29
  2         03/07/2017     04/07/2017      16/06/2017         13
  3         07/07/2017     10/07/2017      04/07/2017         5
  4         12/07/2017     26/07/2017      10/07/2017         13     

我的最终目标是我想要两个新列；newdate and dayDifference。

• newdate列是从上一行的endDate。如您所见，例如，ID 2的newdate是16/06/2017，它来自ID 1的末端1。但是，如果上一行的端端值是null的，请使用其startdate(ID 1 Case(。
• daysDifference列来自仅在末日和新达特列之间计算工作日。

这是我正在使用ATM的脚本。

    select distinct
    c.ID
    ,c.EndDate
    ,isnull(p.EndDate,c.StartDate) as NewDate
    ,count(distinct cast(l.CalendarDate as date)) as DaysDifference
    from    
                (select *
                from table) c
                full join
                (select *
                from table) p
                on c.level = p.level
                and c.id-1 = p.id           
    left join Calendar l
    on (cast(l.CalendarDate as date) between cast(p.EndDate as date) and cast(c.EndDate as date)
    or 
    cast(l.CalendarDate as date) between cast(p.EndDate as date) and cast(c.StartDate as date))
    and l.Day not in ('Sat','Sun') and l.Holiday <> 'Y'
    where c.ID <> 0 
    group by 
    c.ID
    ,c.EndDate
    ,isnull(p.EndDate,c.StartDate)

这是当前的结果：

  ID        EndDate         NewDate        DaysDifference
 =========================================================
  1         16/06/2017      12/06/2017         0
  2         04/07/2017      16/06/2017         13
  3         10/07/2017      04/07/2017         5
  4         26/07/2017      10/07/2017         13

似乎在实际数据中，我对ID 2,3,4的dayDivference除了ID 1之外有正确的daysDifference，因为从其上一行(ID 0(的null值(ID(null值(ID 0(，即打印起点而不是null Enddate，因此它数计数错误。

希望我已经提供了足够的信息。：(

您能否指导我正确计数daydifference的方法。

预先感谢！

Answer 1

我认为您可以使用此逻辑来获取上一个日期：

select t.*,
       lag(coalesce(enddate, startdate), 1) over (order by 1) as newdate 
from t;

然后是差异：

select id, enddate, newdate,
       sum(case when c.day not in ('Sat', 'Sun') and c.holiday <> 'Y' then 1 else 0 end) as diff
from (select t.*,
             lag(coalesce(enddate, startdate), 1) over (order by 1) as newdate 
      from t
     ) t join
     calendar c
     on c.calendardate >= newdate and c.calendardate <= startdate
group by select id, enddate, newdate;