CODEWITHSUNDEEP
sql-servert-sql
JJSSOQ
JJSSOQ

Reputation: 49

SQL-Calculate price for how long one used an item- monthly price, weekly price, and hourly price

let's say we are renting out a bike. There's a monthly($100), weekly($10), and hourly($1) price. If I used it for 1 month, 2 weeks, and 4 hours, the price would be $124. I have a while loop that will do the modulus to see if the hours is longer than monthly and weekly and add to the price and subtract the duration of the borrowed item.

SET @dateDiff =   DATEDIFF(hour,@TimeIn,@TimeOut); 
-- 7 days is 168 hours
-- 30 days is 720 hour

WHILE  @bool != 1  
    BEGIN  

    IF (@dateDiff > 720)
        SET  @totalPrice += (@dateDiff%720) * @monthlyPrice
        SET @dateDiff = @dateDiff - (@dateDiff%720)
    ELSE IF  (@dateDiff > 168)
        SET  @totalPrice += (@dateDiff%168) * @weeklyPrice
        SET @dateDiff = @dateDiff - (@dateDiff%168)
    ELSE
        SET  @totalPrice += @dateDiff * @weeklyPrice
        SET @bool = 1;
END

I want to do something like above.

Upvotes: 0

Views: 175

Answers (2)

Ilyes
Ilyes

Reputation: 14928

How about using @TimeIn and @TimeOut directly without calucate the hours first, in this case @Start and @End represent @TimeIn and @TimeOut

DECLARE @Start DATETIME = '2018-01-01 01:00:00';
DECLARE @End   DATETIME = '2018-02-14 04:00:00';

SELECT FORMAT(
       DATEDIFF(Month, @Start, @End) * 100 + --Months
       DATEDIFF(Week, @Start, DATEADD(Month, -DATEDIFF(Month, @Start, @End), @End)) * 10 + --Weeks
       DATEDIFF(Hour, @Start, DATEADD(Day, -DATEDIFF(Day, @Start, @End), @End)) --Hours
       , 'C'
       , 'en-us'
       ) AS ClientShouldPay;

Results:

+-----------------+
| ClientShouldPay |
+-----------------+
| $123.00         |
+-----------------+

Note: FORMAT() funtion avaliable only on 2012+, you can remove it if you want to.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86775

SET @months = @datediff / 720
SET @weeks = (@datediff % 720) / 168
SET @hours = (@datediff % 720) % 168

No loop required

Upvotes: 2

Related Questions