Excellent Solution
A reader, December 12, 2007 - 2:59 pm UTC
Hi Tom,
I am a admirer of your website.
The solutions you provide are "Best Fit".
I thank Oracle Corporation for having you available to answer our queries.
Regards.
December 13, 2007 - 9:04 am UTC
I should mention that in 9i and above, the use of a TIMESTAMP would make this trivial - the default interval will be of "day to second" form - consider:
ops$tkyte%ORA10GR2> select created, sysdate today, systimestamp - created from all_users where rownum < 5;
CREATED TODAY SYSTIMESTAMP-CREATED
--------- --------- ---------------------------------------------------------------------------
30-JUN-05 13-DEC-07 +000000895 13:59:29.393839
30-JUN-05 13-DEC-07 +000000895 13:59:29.393839
30-JUN-05 13-DEC-07 +000000895 13:59:23.393839
30-JUN-05 13-DEC-07 +000000895 13:55:02.393839
SQL
Hitesh, December 19, 2007 - 9:52 am UTC
Hi Tom,
I am very impressed by this SQL. However, I am trying to find some solution to one of the problems being faced.
I have to calculate the total hrs a driver drove the bus on a given day 18/12.
Bus-No Driver-No From_Stn To_Stn Dep_Time Arr_Time
1 ABC A B 18/12 22:00 18/12 23:30
1 ABC B C 18/12 23:30 18/12 23:55
1 ABC C D 18/12 23:55 19/12 00:40
Expected Result : 2 hrs and not 1 hr 55 mins.
Can you please help me writing this thru SQL.
December 19, 2007 - 10:54 am UTC
this is trivial - but, without a create table and insert intos - I'm not going to totally solve it for you - I'll give you what you need to write your query, but you'll need to test it and all.
when you subtract two DATES, you get the number of days between, eg:
ops$tkyte%ORA9IR2> select created, sysdate, sysdate-created from all_users where rownum <= 5;
CREATED SYSDATE SYSDATE-CREATED
-------------------- -------------------- ---------------
14-dec-2007 13:38:08 19-dec-2007 10:59:20 4.88972222
19-dec-2007 10:46:35 19-dec-2007 10:59:20 .008854167
15-oct-2007 09:45:12 19-dec-2007 10:59:20 65.0514815
06-dec-2003 13:44:27 19-dec-2007 10:59:20 1473.88534
06-dec-2003 13:40:21 19-dec-2007 10:59:20 1473.88818
for that first row, we have 4.88 days - for the second, 0.008 days...
Now, knowing a day is 24 hours, we can easily get hours:
ops$tkyte%ORA9IR2> select created, sysdate, (sysdate-created)*24 hours from all_users where rownum <= 5;
CREATED SYSDATE HOURS
-------------------- -------------------- ----------
14-dec-2007 13:38:08 19-dec-2007 10:59:34 117.357222
19-dec-2007 10:46:35 19-dec-2007 10:59:34 .216388889
15-oct-2007 09:45:12 19-dec-2007 10:59:34 1561.23944
06-dec-2003 13:44:27 19-dec-2007 10:59:34 35373.2519
06-dec-2003 13:40:21 19-dec-2007 10:59:34 35373.3203
and then you need to ceil it, floor it, round it - whatever it - you decide - to get an integer "hours"
ops$tkyte%ORA9IR2> select created, sysdate, ceil((sysdate-created)*24) hours from all_users where rownum <= 5;
CREATED SYSDATE HOURS
-------------------- -------------------- ----------
14-dec-2007 13:38:08 19-dec-2007 10:59:44 118
19-dec-2007 10:46:35 19-dec-2007 10:59:44 1
15-oct-2007 09:45:12 19-dec-2007 10:59:44 1562
06-dec-2003 13:44:27 19-dec-2007 10:59:44 35374
06-dec-2003 13:40:21 19-dec-2007 10:59:44 35374
Create table Statement With Inserts
hitesh, December 20, 2007 - 5:41 am UTC
Hi Tom,
Thanks for the reply. However I was looking for a different solution.
Now I have pasted the Create table and Insert statements.
Create table Bus_Stn_Driver (Bus_No Number, Driver_No Number, From_Stn Varchar2(3), To_Stn Varchar2(3), Dep_Time Date, Arr_Time Date);
Insert into Bus_Stn_Driver values (1,1,'A','B',to_date('14/12/2007 00:00:00'),to_date('14/12/2007 23:30:00','dd/mm/yyyy hh24:mi:ss'));
Insert into Bus_Stn_Driver values (1,1,'B','C',to_date('14/12/2007 23:30:00'),to_date('14/12/2007 23:55:00','dd/mm/yyyy hh24:mi:ss'));
Insert into Bus_Stn_Driver values (1,1,'C','D',to_date('14/12/2007 23:55:00'),to_date('15/12/2007 00:40:00','dd/mm/yyyy hh24:mi:ss'));
Insert into Bus_Stn_Driver values (1,1,'D','C',to_date('15/12/2007 04:30:00'),to_date('15/12/2007 05:30:00','dd/mm/yyyy hh24:mi:ss'));
create table price (Driver_No Number, Valid_From Date, Valid_to Date, Pay_Day NUMBER);
Insert into Price Values (1,to_date('01/12/2007 00:00:00','dd/mm/yyyy hh24:mi:ss'),to_date('15/12/2007 00:00:00'),100);
Insert into Price Values (1,to_date('15/12/2007 00:00:00','dd/mm/yyyy hh24:mi:ss'),to_date('31/12/2007 00:00:00'),200);
Find some solution to find how much money has to be paid to the driver based on his dates and routes at the end of the month?
Answer : 14/12/2007 : 100 * 2 /24
15/12/2007 : 200 * 1.66/24
Total : Rs 22.16
December 20, 2007 - 10:12 am UTC
you know, some days I just sit here and go "wow, this cannot really be happening"
This
is
one
of
those
days........
Please explain
hitesh, December 20, 2007 - 10:50 am UTC
Hi Tom,
Can you please explain what you meant.
Thanks
December 20, 2007 - 2:14 pm UTC
I meant it is really sort of bothersome to get a very very clear question one day:
...
I have to calculate the total hrs a driver drove the bus on a given day 18/12.
Bus-No Driver-No From_Stn To_Stn Dep_Time Arr_Time
1 ABC A B 18/12 22:00 18/12 23:30
1 ABC B C 18/12 23:30 18/12 23:55
1 ABC C D 18/12 23:55 19/12 00:40
Expected Result : 2 hrs and not 1 hr 55 mins.
Can you please help me writing this thru SQL.
......
and then the next day "ok, now that I made you do that, what I really want is for you to do this instead"....
If you know sql at all - you should be able to answer your own question from day two (hint: you have a join to perform, I showed you how to get hours, now you take that, join to your other table - it is entirely up to you to figure out what to do if dates span eachother - if I start working on the 14th and finish on the 15th). When you join, multiply. It is rather basic.
My comment was simply to express my frustration.......
your example doesn't "run"
your example looks a lot like homework
hahaha...
Jay, December 20, 2007 - 10:53 am UTC
Laughing Out Loud!
Merry christmas and advanced happy new year greetings to you and your family Tom!
cheers,
Jay
Not willingness to help
hitesh, December 22, 2007 - 12:27 am UTC
I thought you will help as we all learn from your SQL capability every passing day, but if make fun of people requesting you for something than its your choice.
Sorry for making you frustrated, I never wanted it to be that case.
anyways thanks for all help what you could do.
December 22, 2007 - 12:00 pm UTC
willing to help = absolutely.
willing to do your beck and call = no way.
I suggest you go back and re-read this thread, starting with your request and ask yourself "what if someone came into my office, ask me to spend time with them - they ask me a very specific question and I take my time, I answer them exactly. Then they say - ok, that is not what I want, I want this entirely different thing"
Now, imagine that happens to you 50 times a day. And imagine that you are actually doing someones homework?
How you can take your original question - to which you got a PRECISE answer and then say...
...
Thanks for the reply. However I was looking for a different solution.
.....
you were not looking for a different solution, you moved on to part b of your assignment.
and it is *just a join*, pretty simple stuff. If you have a record that has the number of hours and the date - and another table with that bus drivers id and the date range and the dollars per hour
and you cannot join those two together.... by yourself....
Getting the hours between two dates - ok, maybe that would be tricky if you didn't know the Oracle date stuff.
Joining two tables though?
Hrs based on date
hitesh, December 23, 2007 - 12:47 am UTC
Hi Tom,
The earlier question was not just substract date from date (which is pretty simple) and calculate hrs but it was to get the hrs on a date to date basis. If you see the INSERT statements we have to calculate the hrs for 14th and 15th. The problem is when a row spans across dates.
scott@ADAM.DOMAIN> select * from my_test;
DEP_TIME ARR_TIME
------------------- -------------------
18/12/2007 23:00:00 18/12/2007 23:30:00
18/12/2007 23:30:00 18/12/2007 23:55:00
18/12/2007 23:55:00 19/12/2007 00:10:00
19/12/2007 04:50:00 19/12/2007 08:30:00
scott@ADAM.DOMAIN> Select dep_time, (Case When (trunc(arr_time) - trunc(dep_time)) < 1 then (arr_time-dep_time) * 24 * 60
2 ELSE ((trunc(arr_time)) - dep_time) * 24 * 60 END) elapsed_time from my_test
3 /
DEP_TIME ELAPSED_TIME
------------------- ------------
18/12/2007 23:00:00 30
18/12/2007 23:30:00 25
18/12/2007 23:55:00 5
19/12/2007 04:50:00 220
What I am missing is 10 mins the driver spent in the 3rd row of 19th. This is what I was asking for your help from the day one.
December 23, 2007 - 5:54 pm UTC
you were not asking for that from day one, Please go back and read your question.
...
Expected Result : 2 hrs and not 1 hr 55 mins.
......
it is just date arithmetic...
but anyway, even from your second followup - there is no way I could discern what you want - because now - well, you are on the third unique distinct separate question.
Now you are even at "minutes" level - not hours. But anyway, if you just did what I did in the first place:
ops$tkyte%ORA10GR2> select a, b, (b-a)*24*60
2 from t;
A B (B-A)*24*60
------------------- ------------------- -----------
18/12/2007 23:00:00 18/12/2007 23:30:00 30
18/12/2007 23:30:00 18/12/2007 23:55:00 25
18/12/2007 23:55:00 19/12/2007 00:10:00 15
19/12/2007 04:50:00 19/12/2007 08:30:00 220
you would "lose nothing"
Just subtract, just multiply - you can get minutes, hours, whatever you want...
Date Wise Hrs Spent
hitesh, December 24, 2007 - 12:03 am UTC
Hi Tom,
It is very unfortunate that I am not able to make you understand what is required as a solution,
I can try once again! What is required is the total time spent by the driver each day
So,third row the driver's departure time is 23:55 but the arrival is on next day 00:10 hrs which means 5 mins has to be counted for 18th and 10 mins have to be counted for 19th.
ops$tkyte%ORA10GR2> select a, b, (b-a)*24*60
2 from t;
A B (B-A)*24*60
------------------- ------------------- -----------
18/12/2007 23:00:00 18/12/2007 23:30:00 30
18/12/2007 23:30:00 18/12/2007 23:55:00 25
18/12/2007 23:55:00 19/12/2007 00:10:00 15
19/12/2007 04:50:00 19/12/2007 08:30:00 220
Below mentioned result is what I need:-
A B (B-A)*24*60
------------------- ------------------- -----------
18/12/2007 23:00:00 18/12/2007 23:30:00 30
18/12/2007 23:30:00 18/12/2007 23:55:00 25
18/12/2007 23:55:00 18/12/2007 00:00:00 5
18/12/2007 00:00:00 19/12/2007 00:10:00 10
19/12/2007 04:50:00 19/12/2007 08:30:00 220
December 24, 2007 - 9:16 am UTC
Now that -
"What is required is the total time spent by the driver
each day"
it the fourth question - let's run with that one. No dollars and cents (no money)
Not "i need two hours, not 1 hour 55 minutes" (which sounds like a problem with rounding)
Not "What I am missing is 10 mins the driver spent in the 3rd row of 19th. This is what I was asking for
your help from the day one."
a missing 10 minutes (which was only missing because you made it go missing)
ops$tkyte%ORA10GR2> select * from t;
A B
------------------- -------------------
18/12/2007 23:00:00 18/12/2007 23:30:00
18/12/2007 23:30:00 18/12/2007 23:55:00
18/12/2007 23:55:00 19/12/2007 00:10:00
19/12/2007 04:50:00 19/12/2007 08:30:00
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select x.*, (b_prime-a_prime) * 24 * 60
2 from (
3 select a,b,
4 case when trunc(t.a) <> trunc(t.b) and d.x = 2
5 then trunc(t.b)
6 else t.a
7 end A_prime,
8 case when trunc(t.a) <> trunc(t.b) and d.x = 1
9 then trunc(t.b)
10 else t.b
11 end b_prime,
12 d.x
13 from t, (select 1 x from dual union all select 2 x from dual) d
14 where d.x = 1
15 or (trunc(a) <> trunc(b))
16 ) x
17 order by 1, 2, 5
18 /
A B A_PRIME B_PRIME X (B_PRIME-A_PRIME)*24*60
------------------- ------------------- ------------------- ------------------- ---------- -----------------------
18/12/2007 23:00:00 18/12/2007 23:30:00 18/12/2007 23:00:00 18/12/2007 23:30:00 1 30
18/12/2007 23:30:00 18/12/2007 23:55:00 18/12/2007 23:30:00 18/12/2007 23:55:00 1 25
18/12/2007 23:55:00 19/12/2007 00:10:00 18/12/2007 23:55:00 19/12/2007 00:00:00 1 5
18/12/2007 23:55:00 19/12/2007 00:10:00 19/12/2007 00:00:00 19/12/2007 00:10:00 2 10
19/12/2007 04:50:00 19/12/2007 08:30:00 19/12/2007 04:50:00 19/12/2007 08:30:00 1 220
it should be easy to take that and aggregate up to the day level...
A little mistake
hitesh, December 24, 2007 - 12:05 am UTC
Below mentioned result is what I need:-
A B (B-A)*24*60
------------------- ------------------- -----------
18/12/2007 23:00:00 18/12/2007 23:30:00 30
18/12/2007 23:30:00 18/12/2007 23:55:00 25
18/12/2007 23:55:00 19/12/2007 00:00:00 5
19/12/2007 00:00:00 19/12/2007 00:10:00 10
19/12/2007 04:50:00 19/12/2007 08:30:00 220
Marco van der Linden, December 24, 2007 - 7:28 am UTC
Hitesh, simply use a union like this:
SELECT start_date
, decode(trunc(start_date), trunc(end_date), end_date, trunc(end_date))
FROM t
UNION
SELECT decode(trunc(start_date), trunc(end_date), start_date, trunc(end_date))
, end_date
FROM t
that will get you the 'extra' row your looking for.
Thanks
hitesh, December 26, 2007 - 6:35 am UTC
Thanks Tom for the excellent solution.
Wow, Give an inch, get asked for a mile
Avi, May 24, 2019 - 6:02 am UTC
Hey Tom,
I stumbled across this and I know this thread is ancient. But that guy didn't get your point which from your first reply was straightforward. Thanks for taking time to answer questions from those that appreciate your replies.
May 24, 2019 - 8:41 am UTC
:)
The Best
Pamela Hammond, April 29, 2020 - 12:30 pm UTC
Your answer fit my needs PERFECTLY! Thanks, I was way overthinking it.
April 29, 2020 - 4:17 pm UTC
Great to hear :)