丢弃结果中包含的现有日期，SQL Server

admin

丢弃结果中包含的现有日期，SQL Server

sql

在我的数据库中，我有一个Reservation表，它有三列Initial Day，Last Day 和House Id。

我想计算总天数，而忽略重复的天数，例如：

+-------------+------------+------------+
|             | Results    |            |
+-------------+------------+------------+
|   House Id  | InitialDay |  LastDay   |
+-------------+------------+------------+
|  1          | 2017-09-18 | 2017-09-20 |
|  1          | 2017-09-18 | 2017-09-22 |
| 19          | 2017-09-18 | 2017-09-22 |
| 20          | 2017-09-18 | 2017-09-22 |
+-------------+------------+------------+

如果您发现House Id编号为1的包含两行，并且每一行都有日期，但是第一行在第二行的日期间隔中。总的天数应该为5，因为不应将第一天计算在内，因为第二天已经存在这些天数。

发生这种情况的原因是，每个房屋都有两个房间，不同的人可以在同一日期住进该房屋。

我的问题是：我该如何忽略这些情况，而仅计算房屋被占用的真实日期？

阅读 163

2021-07-01

共1个答案

admin

在使用SQL Server 2012或更高版本的系统中，您可以LAG()用来获取上一个最终日期并调整初始日期：

with ReservationAdjusted as (
select *,
  lag(LastDay) over(partition by HouseID order by InitialDay, LastDay) as PreviousLast
from Reservation
)
select HouseId,
  sum(case when PreviousLast>LastDay then 0 -- fully contained in the previous reservation
    when PreviousLast>=InitialDay then datediff(day,PreviousLast,LastDay) -- overlap
    else datediff(day,InitialDay,LastDay)+1 -- no overlap
    end) as Days
from ReservationAdjusted
group by HouseId

情况是：

该保留项完全包含在先前的保留项中：我们只需要比较结束日期，因为前一行是按获取的InitialDay, LastDay，因此先前的开始日期始终小于或等于当前的开始日期。
当前的预订与之前的预订重叠：在这种情况下，我们调整起点，不加1（已经计算了起始日期），这种情况包括前一个终点等于当前的起点（是一天的重叠）。
没有重叠：我们只计算差值，然后加1即可计算出第一天的时间。

请注意，我们不需要额外的条件来保留a，HouseID因为默认情况下该LAG()函数NULL在没有上一行时返回，并且与null的比较始终为false。

样本输入和输出：

| HouseId | InitialDay |    LastDay |
|---------|------------|------------|
|       1 | 2017-09-18 | 2017-09-20 |
|       1 | 2017-09-18 | 2017-09-22 |
|       1 | 2017-09-21 | 2017-09-22 |
|      19 | 2017-09-18 | 2017-09-27 |
|      19 | 2017-09-24 | 2017-09-26 |
|      19 | 2017-09-29 | 2017-09-30 |
|      20 | 2017-09-19 | 2017-09-22 |
|      20 | 2017-09-22 | 2017-09-26 |
|      20 | 2017-09-24 | 2017-09-27 |

| HouseId | Days |
|---------|------|
|       1 |    5 |
|      19 |   12 |
|      20 |    9 |

2021-07-01