丢弃结果中包含的现有日期，SQL Server [英] Discard existing dates that are included in the result, SQL Server

问题描述

在我的数据库中，我有一个 Reservation 表，它有三列 Initial Day ，最后一天和房屋编号。

In my database I have a Reservation table and it has three columns Initial Day, Last Day and the House Id.

我要计算总天数，并省略重复的天，例如：

I want to count the total days and omit those who are repeated, for example:

+-------------+------------+------------+
|             | 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 |
+-------------+------------+------------+

如果您注意到编号为1的房屋ID 有两行，并且每行都有日期，但第一行在第二行的日期间隔内。总的天数应该为5，因为第一天不应算作第二天中已经存在的那几天。

If you noticed the House Id with the number 1 has two rows, and each row has dates but the first row is in the interval of dates of the second row. In total the number of days should be 5 because the first shouldn't be counted as those days already exist in the second.

发生这种情况的原因是每一天这座房子有两个房间，不同的人可以在同一日期住在那个房子里。

The reason why this is happening is that each house has two rooms, and different persons can stay in that house on the same dates.

我的问题是：我该如何省略这些情况，只计算真实的日子

My question is: how can I omit those cases, and only count the real days the house was occupied?

推荐答案

在使用SQL Server 2012或更高版本时，可以使用 LAG（ ）获取上一个最终日期并调整初始日期：

In your are using SQL Server 2012 or higher you can use LAG() to get the previous final date and adjust the initial date:

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即可计算出第一天。

• The reservation is fully included in the previous reservation: we only need to compare end dates because the previous row is obtained ordering by InitialDay, LastDay, so the previous start date is always minor or equal than the current start date.
• The current reservation overlaps with the previous: in this case we adjust the start and don't add 1 (the initial day is already counted), this case include when the previous end is equal to the current start (is a one day overlap).
• There is no overlap: we just calculate the difference and add 1 to count also the initial day.

请注意，对于 HouseID 的预订，我们不需要额外的条件，因为默认情况下， LAG（ ）函数在以下情况下返回 NULL

Note that we don't need extra condition for the reservation of a HouseID because by default the LAG() function returns NULL when there isn't a previous row, and comparisons with null always are 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 |

这篇关于丢弃结果中包含的现有日期，SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！