Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Time Fraction

user517674Dec 28 2010 — edited Dec 28 2010
I am trying to calculate the time fraction that will fall in each of these 3 4 hour blocks.

I have 3- 4 hours blocks of time:

3:00 AM – 7:00 AM
7:00AM – 11:00 AM
11:00AM – 3:00 PM

My input data is:
Start_Time			End_Time

6:00 AM			8:30 AM
8:00 AM			9:00 AM	
10:30 AM			11:30AM
I am trying to calculate what fraction of time (time_fraction field below) belongs in each of the above 4 hour blocks of time?
Start_Time	End_Time	Period_begin  Period_End Duration_start_end 	Time_Fraction
					
6:00 AM	8:30 AM	3:00 AM	7:00 AM	150	0.40
6:00 AM	8:30 AM	7:00 AM	11:00 AM	150	0.60
6:00 AM	8:30 AM	11:00 AM	3:00 PM	150	0.00
8:00 AM	9:00 AM	3:00 AM	7:00 AM	60	0.00
8:00 AM	9:00 AM	7:00 AM	11:00 AM	60	1.00
8:00 AM	9:00 AM	11:00 AM	3:00 PM	60	0.00
10:30 AM	11:30AM	3:00 AM	7:00 AM	60	0.00
10:30 AM	11:30AM	7:00 AM	11:00 AM	60	0.50
10:30 AM	11:30AM	11:00 AM	3:00 PM	60	0.50
Thank you in advance….

Edited by: user517674 on Dec 28, 2010 12:42 PM
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 25 2011
Added on Dec 28 2010
4 comments
178 views