Calculating price based on changing hourly rates

Question

Imagine I have a business that rents widgets by the minute, but the rental rates vary depending on the day of the week and hour of the day. I describe this information in a "Rates" table as follows:

CREATE TABLE Rates (
    DayNumber int,
    HourNumber int,
    HourlyRate decimal(19,4),
    PRIMARY KEY (DayNumber, HourNumber)
)

DayNumber  HourNumber  HourlyRate
---------  ----------  ----------
1          1           3.75
1          2           4.50
1          3           4.25
1          4           3.75

In the above table, the day number is retrieved from datepart(dw, Start), the hour number from datepart(hour, Start). It has 168 records (the number of hours in a standard week).

I have the rental information as follows in a "Rentals" table:

CREATE TABLE Rentals (
    RentalId int,
    CustomerId int,
    Start datetimeoffset,
    Finish datetimeoffset,
    Cost decimal(19,4),
    PRIMARY KEY (RentalId)
)

RentalId  CustomerId  Start            Finish           Cost
--------  ----------  ---------------  ---------------  ----
1         1           1/1/2016 6:11am  1/1/2016 2:34pm  
2         1           1/2/2016 7:23am  1/3/2016 8:12am  

Using T-SQL (SQL Server 2014 or better), I'd like to update the Rentals table to calculate the Cost column that considers the rate of each day-hour, summed up for the total rental period. Bonus points for efficiency.

Answer 1

Tested in SSMS, it updates the table now. It solved the issues below:

1) it works no matter whether or not START and FINISH are on the same day;

2) it works no matter whether or not START and FINISH are on the same week or month.

update rentals
set cost = (select sum(hourlyrate) from rates 
                where (daynumber > datepart(dw,start) and daynumber < datepart(dw,finish)) or
                (daynumber = datepart(dw,start) and hournumber >  datepart(hour,start)) or
                (daynumber = datepart(dw,finish) and hournumber <  datepart(hour,finish))
                ) + 
                (select hourlyrate from rates
                where daynumber = datepart(dw,start) and hournumber =  datepart(hour,start)
                ) * 1.00 * (60-datepart(minute, start))/60 +
                (select hourlyrate from rates
                where daynumber = datepart(dw,finish) and hournumber =  datepart(hour,finish)
                ) * 1.00 * datepart(minute, finish)/60 -
                (
                    Case when datediff(day,start,finish)%7 = 0 then 230 -- deal with same day case
                         when datediff(day,start,finish)%7 <> 0 then 0
                    end
                ) +
                (select datediff(day,start,finish)/7 * sum(hourlyrate) from rates) -- deal with multiple weeks case

Answer 2

You can use a tally table to split record into one record per hour.

For example, the following rental

RentalId  CustomerId  Start            Finish           Cost
--------  ----------  ---------------  ---------------  ----
1         1           1/1/2016 1:30pm  1/1/2016 4:45pm  

is processed using tally into

RentalId  Start            Finish           Cost
--------  ---------------  ---------------  ----
1         1/1/2016 1:30pm  1/1/2016 2:00pm  1
1         1/1/2016 2:00pm  1/1/2016 3:00pm  2
1         1/1/2016 3:00pm  1/1/2016 4:00pm  3
1         1/1/2016 4:00pm  1/1/2016 4:45pm  4

With this, you can calculate the cost of each preprocessed records. You have to use rate per minute since not all records lasted a full hour.

Then, just sum these costs grouped by rental and you have got the cost of each rentals.

Here is the complete solution.
I used CTE for the tally table and preprocessed records.

;WITH
N0(_)            AS (SELECT NULL UNION ALL SELECT NULL),
N1(_)            AS (SELECT NULL FROM N0 AS L CROSS JOIN N0 AS R),
N2(_)            AS (SELECT NULL FROM N1 AS L CROSS JOIN N1 AS R),
N3(_)            AS (SELECT NULL FROM N2 AS L CROSS JOIN N2 AS R),
Tally            AS (SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))  FROM N3 AS L CROSS JOIN N3 AS R),

PreprocessedData AS (SELECT Rent.RentalId,
                            BillingStart =( CASE WHEN Tally.N = 1 THEN                          
                                                Rent.Start
                                            ELSE
                                                DATEADD(HOUR, DATEDIFF(HOUR, 0, DATEADD(HOUR, Tally.N - 1, Rent.Start)), 0)--Trim exceeding minutes
                                            END),
                            BillingEnd = (  CASE WHEN DATEDIFF(HOUR, Rent.Start, Rent.Finish) < Tally.N THEN    
                                                Rent.Finish
                                            ELSE
                                                DATEADD(HOUR, DATEDIFF(HOUR, 0, DATEADD(HOUR, Tally.N, Rent.Start)), 0)--Trim exceeding minutes
                                            END),
                            Rate.HourlyRate
                    FROM Rentals AS Rent
                    INNER JOIN Tally ON DATEDIFF(HOUR, Rent.Start, Rent.Finish) >= Tally.N - 1  -- DATEADD(HOUR, Tally.N, Rent.Start) < Rent.Finish
                    LEFT JOIN Rates AS Rate ON  DATEPART(DW, DATEADD(HOUR, Tally.N - 1, Rent.Start)) = Rate.DayNumber
                                                AND DATEPART(HOUR, DATEADD(HOUR, Tally.N - 1, Rent.Start)) = Rate.HourNumber
                    )



UPDATE Rentals
SET Cost = CalculateCostPerRental.CalculateCost 
FROM Rentals
INNER JOIN (SELECT  RentalId,
                    CalculateCost = SUM(HourlyRate * DATEDIFF(MINUTE, BillingStart, BillingEnd) /60)
            FROM PreprocessedData
            GROUP BY RentalId
            HAVING SUM(CASE WHEN HourlyRate IS NOT NULL THEN 0 ELSE 1 END) = 0 /*Update only if all the rates where found*/) AS CalculateCostPerRental ON Rentals.RentalId = CalculateCostPerRental.RentalId
/*cost is null when there is a rate missing in rate table*/

As for performance, they are poor, but it is due to your database design. Without, changing the design, it's going to be really hard to do better than what this solution does. However, I would challenge if if you really need rocking performance for this task.

Disclaimer : You should do some testing before using this in production because I haven't tested every edge case there is. Also, you might have missing rates in your rate table.