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

I have a booking table with the following information:

我有一张预订表,其中包含以下信息:

BookingID,(unique, not null) StartDate, (not null) EndDate (not null)

BookingID,(唯一,非null)StartDate,(非null)EndDate(非null)

I need to calculate the number of nights someone remained in residence which I can do with a DATEDIFF between EndDate and StartDate. However, if someone is in residence for the entire month during a 31 day month we only charge them 30.

我需要计算一个人居住的夜晚,我可以在EndDate和StartDate之间使用DATEDIFF。但是,如果某人在31个月内整个月都在居住,我们只收取30美元。

I'm not sure how to do this in SQL. I was thinking I would have to create a variable, calculate on a monthly basis and add to the variable, but that seems like it would be messy and take a very long time, especially towards the end of year. This needs to be done for about 5,000 records on a daily basis.

我不确定如何在SQL中执行此操作。我以为我必须创建一个变量,按月计算并添加到变量,但这似乎很麻烦,需要很长时间,特别是在年底。这需要每天为大约5,000条记录完成。

So: If someone starts on 7/25/14 and ends 9/2/14, the nights is 38 not 39. If someone starts on 10/2/14 and ends on 11/1/14, the nights is 30. If someone starts on 10/2/14 and ends on 10/31/14, the nights is 29.

所以:如果某人在2014年7月25日开始并于2014年2月9日结束,那么晚上的时间是38而不是39。如果有人在2014年2月2日开始并在2014年1月11日结束,那么晚上是30。有人在2014年2月10日开始,到2014年10月31日结束,晚上是29。

We will be calculating into the future so it doesn't matter if the end date is greater than the day the report is being ran.

我们将计算未来,因此结束日期是否大于报告运行的日期并不重要。

Does anyone have any ideas how to accomplish this in the best way?

有没有人有任何想法如何以最好的方式完成这个?

4 个解决方案

#1

I would first to create a lookup table with all the month with 31 days

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

Such as

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

Then you can calculate the value with

然后你可以用它来计算值

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)

#2

Retrieve the integer part of the datediff divided by 31 :

检索datediff的整数部分除以31:

SELECT DATEDIFF(day,'2014-07-25', '2014-09-02') - DATEDIFF(day,'2014-07-25', '2014-09-02') / 31

#3

The SQLish solution is to create a calendar table that holds all the dates you will ever care about and any business meaning that those dates may have, such as "Is this a holiday?", "Is this off-season?", or "do we charge for this day?"

SQLish解决方案是创建一个日历表,其中包含您将关注的所有日期以及这些日期可能具有的任何业务含义,例如“这是假期吗?”,“这是淡季吗?”或“我们这天收费吗?“

This may sound insane to someone accustomed to other programming languages, but it is perfectly sensible in the database world. Business rules and business logic get stored as data, not as code.

对于习惯于其他编程语言的人来说,这可能听起来很疯狂,但在数据库世界中它是完全合理的。业务规则和业务逻辑存储为数据,而不是代码。

Make this table and populate it:

制作此表并填充它:

CREATE TABLE Calendar (
  [date] date
 ,[is_charged] bit
)

and then you can write code that is nearly plain English:

然后你可以编写几乎简单的英文代码:

SELECT
  [BookingID]
 ,COUNT([date])
FROM BookingTable
INNER JOIN Calendar
  ON  [date] >= [StartDate]
  AND [date] <  [EndDate]
WHERE [is_charged] = 1
GROUP BY [BookingId]

When your business rules change, you just update the calendar instead of rewriting the code.

当您的业务规则发生变化时,您只需更新日历而不是重写代码。

#4

If I've read your question correctly then you can't actually use those solutions above which consist of a table of billable and non billable days because the 31st is billable unless the whole month was booked.

如果我已经正确地阅读了您的问题,那么您实际上无法使用上面的解决方案,其中包括可计费和不可计费的日期表,因为除非整个月都已预订,否则31日是可计费的。

I reckon this is probably a job for a user defined function. Which runs up a total starting with the month that the start date is in and finishing with the month that the end date is in.

我认为这可能是用户定义函数的工作。从开始日期所在的月份开始累计,并以结束日期所在的月份结束。

CREATE FUNCTION dbo.FN_BillableDays (@StartDate date, @EndDate date) 
 returns int
AS
BEGIN
IF @StartDate > @EndDate
BEGIN 
return null  --Error
END
DECLARE @Next date
DECLARE @MonthStart date
DECLARE @MonthEnd  date
DECLARE @NextMonthStart  date
DECLARE @n int =0
SET @Next = @StartDate
SET @MonthStart = DATEADD(day,1-DAY(@Next),@Next)
SET @NextMonthStart  = DATEADD(month,1,@MonthStart )
SET @MonthEnd = DATEADD(day,-1,@NextMonthStart)
WHILE DATEDIFF(month,@Next,@EndDate) >0
BEGIN
   SET @n = @n +
    CASE 
    WHEN DAY(@next) = 1 AND DAY(@MonthEnd) = 31 THEN 30
    WHEN DAY(@next) = 1 THEN DAY(@MonthEnd)
    ELSE 1+DAY(@MonthEnd) -DAY(@next)  END

    SET @MonthStart = @NextMonthStart  
    SET @NextMonthStart  = DATEADD(month,1,@MonthStart )
    SET @MonthEnd = DATEADD(day,-1,@NextMonthStart)
    SET @Next = @NextMonthStart  
END
--Month of the EndDate
   SET @n = @n +
   CASE 
   WHEN DAY(@next) = 1 AND DAY(@EndDate) = 31 THEN 29
   WHEN DAY(@next) = 1 THEN DAY(@EndDate)-1
   ELSE DAY(@MonthEnd) -DAY(@EndDate)  END
return  @n 
END

I tried it with some test dates

我尝试了一些测试日期

SELECT 
b.BookingID,
b.StartDate,
b.EndDate, 
dbo.FN_BillableDays (b.StartDate,b.EndDate) AS BillableDays 
FROM dbo.Booking  b

And got the following

并得到以下

BookingID   StartDate  EndDate    BillableDays
----------- ---------- ---------- ------------
1           2013-12-31 2014-01-02 2
2           2013-12-31 2014-01-30 30
3           2014-01-01 2014-01-30 29
4           2014-01-01 2014-01-31 29
5           2014-01-01 2014-02-01 30
6           2014-01-01 2014-02-02 31
7           2014-02-02 2014-02-01 NULL

(7 row(s) affected)

Which matches my understanding of the logic you want to implement but you may want to tweak the last bit which adds on the days for the final month. If they leave on the 31st do you want to give them their last night (30th to 31st) for free.

这符合我对你想要实现的逻辑的理解,但你可能想要调整最后一点,这会增加最后一个月的日期。如果他们在31日离开,你想免费给他们昨晚(30日至31日)。

If you don't then delete the line WHEN DAY(@next) = 1 AND DAY(@EndDate) = 31 THEN 29

如果你没有那么删除行WHEN DAY(@next)= 1 AND DAY(@EndDate)= 31那么29

#1