Rounddown in SQL and calculating cost

[korytnackaruzova]

Hi.

I'm working on a query in SQL. I have datetime A and datetime B. I calculated the time difference between them. And now the fun part starts.

I need to round down the time difference to every 15 minutes. I partially figured out the round down part, except, it's rounding minutes in the time difference only. So for some incidents the time difference 00:44:18 rounds to 00:45:00 (I would need that to be 00:30:00).

The second part is to calculate the cost. Each 15 minutes can be billed 5. In example:
30 minutes 5
45 minutes 10
1:15 minutes 20
1:45 minutes 30
2:30 minutes 40

Any advice would be truly appreciated! Thank you!

 

[Andrzejek]

> I calculated the time difference between them.
And how did you do it? Could you show us?

Is it something like:[tt]
SELECT datetime_A, datetime_B, [blue]
DATEDIFF[/blue]([blue]MINUTE[/blue], datetime_A, datetime_B) AS MinuteDiff
FROM TableName[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[korytnackaruzova]

That's exactly how I did it.

 

[Andrzejek]

Then you can wrap it this way:
[tt]
SELECT datetime_A, datetime_B,[blue]
CONVERT(DECIMAL(10,2), ROUND([/blue]DATEDIFF(MINUTE, datetime_A, datetime_B[blue]), 2, 1))[/blue] AS MinuteDiff
FROM TableName
[/tt]
I hope I've got all ( and ) correctly...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

>Each 15 minutes can be billed 5
Looks like your example is off:

[pre]
Min Hrs/Min Amt YOU
0 0:00 $0
15 0:15 $5
30 0:30 $10 $5
45 0:45 $15 $10
60 1:00 $20
75 1:15 $25 $20
90 1:30 $30
105 1:45 $35 $30
120 2:00 $40
135 2:15 $45
150 2:30 $50 $40
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[korytnackaruzova]

I'm sorry. Anything over 30 minutes starts with 5 for every 15 minutes.

 

[Andrzejek]

Did you get 'the round down part' working

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[korytnackaruzova]

It didn't work. Still showing 45. My datetime A is 12:45:49. Datetime B is 13:30:07. The difference is 00:44:18. I would need it to round down to 00:30:00.

Do you have any advice for part two?

Thank you so much for your time helping me!

 

[korytnackaruzova]

So in excel I would use =rounddown(datediff field * 96,0)/96 . That would give me rounddown for nearest 15 minutes

Then I would use formula =rounddown(datediff field * 96,0) -1. That would give me how many 15 minutes is above first 30 minutes. I would then multiple it by my 5.

How can I accomplish it in SQL?

 

[korytnackaruzova]

I think I figured out the second part. If you have any advice for the first part I would appreciate it. I known it's because I'm taking "minutes" so it's taking 30-45 (ok instead of including seconds in it).

 

[Andrzejek]

When I take datetime A of 12:45:49 and datetime B of 13:30:07, I get 0.0307638 days or 44.3 minutes, which is 44 min 18 sec

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[korytnackaruzova]

That is correct. But the problem is, that it's rounding down to 00:45, which in the fact, I would need that to be 00:30.

 

[Andrzejek]

You could ask for Seconds in DATEDIFF and then divide it by 60:
[tt]
SELECT DATEDIFF([blue]second[/blue], '2022/02/10 [red]12:45:49[/red]', '2022/02/10 [red]13:30:07[/red]') / [blue]60[/blue] AS MinDiff;[/tt]

This way you do get 44

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[korytnackaruzova]

Thank you! But how do you round down 15 minutes increments for datediff in seconds?

 

[Andrzejek]

Where you have this data of: datetime A of 12:45:49 and datetime B of 13:30:07, I would have another Computed column in this table where you can have the Minutes between A and B.

If it would be me, I would have another small table:

[pre]
TblCharges
ChargeID MinFrom MinTo ChargeAmt
1 0 29 0
2 30 45 5
3 46 60 10
4 61 75 15
5 76 90 20
...
[/pre]
and I would forget the calculations in the SQL, I would just use this table to calculate the charges.

That's one way...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mikrom]

The difference is 00:44:18. I would need it to round down to 00:30:00.

Given is the time hh:mm:ss = 00:44:18
then you can do following:
1) extract hours and minutes using time functions getting: hh=1, mm=44
2) divide mm by 15 using integer division, it gives: mm/15 = 44/15 = 2
3) using previous result compute mm_rounded = mm/15 * 15 = 44/15 * 15 = 2 * 15 = 30
4) finally concatenate together hh, ':', mm_rounded and create time from it: 00:30:00

I tried it in SQL on DB2:

select 
  time('00:44:18') as "hh:mm:ss", 
  hour(time('00:44:18')) as "hh", 
  minute(time('00:44:18')) as "mm", 
  second(time('00:44:18')) as "ss", 
  minute(time('00:44:18'))/15 * 15 as "mm_rounded", 
  time(varchar(hour(time('00:44:18')))||':'||
       varchar(minute(time('00:44:18'))/15 * 15)) as "hh:mm:ss rounded" 
from sysibm.sysdummy1

Output:

hh:mm:ss   hh       mm      ss   mm_rounded    hh:mm:ss rounded    
00:44:18    0       44      18           30    00:30:00

 

[korytnackaruzova]

As soon as I plugged " /60" to my query with datediff in seconds, it would beautifully. Thank you so much for your help!

 

[Andrzejek]

Great
What about the second part? Are you going with mikrom's solution? Or are you going to have additional table, as I suggested?

In my experience, sooner or later, Management will say: "We are changing our charges. First 35 minutes are free, and next increments of 20 minutes will cost $6.50 And you have 3 days to make these changes" or something like this. Me, as a lazy person, would make a few changes to my Charge table in a few minutes, and take the rest of the three days to complain of how much work this change is causing me...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[korytnackaruzova]

Hahahahaha.. you are on spot!
I was just lazy now and I did (rounddown(datediff)-1)*5. If I have time, I will for sure go back and adjust it with the charge table.

 

[gmmastros]

I have another method for rounding down to 15 minute increments.

Basically, calculate the DateDiff in seconds. Then you can divide by 60.0 to get minutes. In your example, you get 44.3 minutes.

Since you want 15 minute increments, you can divide by 15. You would get 2.953 "increments", Next, you can use the FLOOR function to remove the decimal part. Lastly, multiply the whole number by 15 to get your final results.

Declare @datetimeA DateTime,
        @dateTimeB DateTime;

Select  @DateTimeA = '12:45:49',
        @datetimeB = '13:30:07';

Select  Floor(DateDiff(second, @dateTimeA, @DateTimeB) / 60.0 / 15) * 15

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom