Sql Server 2008 Count nights between two dates, but delete the 31st night

Question

I have a reservation table with the following information:

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

I need to calculate the number of nights that stayed in the residence that I can do with DATEDIFF between EndDate and StartDate. However, if someone lives the whole month for a 31-day month, we only pay 30.

I am not sure how to do this in SQL. I thought I would have to create a variable, calculate monthly and add to the variable, but it looks like it will be dirty and take a lot of time, especially at the end of the year. This is necessary for approximately 5,000 entries on a daily basis.

So: If someone starts on 7/25/14 and ends on 9/2/14, there will be not 38, but 39 at night. If someone starts 10/2/14 and ends on 11/1/14, the nights will be 30. If someone starts 10/2/14 and ends on 10/31/14, there will be 29 at night.

We will look forward to the future, so it doesn’t matter if the end date is longer than the day the report is executed.

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

Answer 1

First I would create a lookup table with the whole month with 31 days

For instance,

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) 

Answer 2

Get the integer part of a datiff divided by 31:

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

Answer 3

SQLish's solution is to create a calendar table that lists all the dates you will ever care about, and any business that means these dates can be like “Is this a holiday?”, “This is the offseason ? ", or" do we charge for this day? "

This may seem crazy for people who are used to other programming languages, but in the database world it’s quite reasonable. Business rules and business logic are stored as data, not as code.

Make this table and fill it in:

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

and then you can write code that is almost 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 simply update the calendar, not rewrite the code.

Answer 4

If I read your question correctly, you will not be able to actually use the solutions that are higher, which consist of a table of paid and non-paid days, because the 31st is paid unless the entire month has been booked.

I believe this is probably the job for a user-defined function. Starting from the month in which the start date begins and ends with the month in which the end date is located.

 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 this 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 next

 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 is in line with my understanding of the logic you want to implement, but you can configure the last bit that is added on the days of the last month. If they leave on the 31st, you want to give them the last night for free (30-31).

If you do not delete the line WHEN DAY (@next) = 1 AND DAY (@EndDate) = 31 THEN 29