Sql Server 2008计算两个日期之间的夜晚但删除第31晚

Question

我有一个包含以下信息的预订表:

BookingID，(唯一，不为空)开始日期，(不为空)结束日期(不为空)

我需要计算某人留在住所的天数，我可以使用 EndDate 和 StartDate 之间的 DATEDIFF 来计算。但是，如果某人在 31 天的月份内整个月都在居住，我们只向他们收取 30 美元。

我不知道如何在 SQL 中执行此操作。我想我必须创建一个变量，每月计算并添加到变量中，但这似乎会很困惑并且需要很长时间，尤其是在年底。每天需要对大约 5,000 条记录执行此操作。

所以:如果某人于 2014 年 7 月 25 日开始并于 2014 年 9 月 2 日结束，则夜晚数为 38，而不是 39。如果某人于 10/2/14 开始并于 11/1/14 结束，则晚数为 30。如果某人于 10/2/14 开始并于 10/31/14 结束，则夜晚数为 29。

我们将计算 future ，因此结束日期是否晚于报告运行日期并不重要。

有人知道如何以最佳方式实现这一目标吗？

Answer 1

我首先创建一个查找表，其中包含 31 天的所有月份

比如

DECLARE @month TABLE (start_date date,end_date date)

    INSERT INTO @month VALUES ('2014-07-01','2014-07-31'),('2014-08-01','2014-08-31'),('2014-10-01','2014-10-31'),('2014-12-01','2014-12-31') 
//populate all months in your calculate range

然后您可以使用

计算该值

DECLARE @start DATE = '2014-07-25', @end DATE = '2014-09-02'

SELECT DATEDIFF(day,@start,@end) - 
    (SELECT COUNT(*) FROM @month WHERE start_date >= @start AND end_date <= @end)