Tricky hours/days worked logic

[DannyTmoov2]

I have 2 requirements relating to calculating worked time

the first is to count worked days between 2 given dates
the second is to return a date and time of a specified date/time less a specified offset (in hours).

Clear as mud? Excellent, then i'll continue...

I have the following tables:

tbl_calendar
calendar_date (date)
start_time (date)
end_time (date)

tbl_calendar_config
day_of_week
start_time
end_time

tbl_calendar starts off, for example, with the following values:

calendar_date start_time end_time
17-may-05 Null Null
21-may-05 09:00 16:00


this effectively means that no shift occured on the 17th (e.g. a public holiday) and an extra shift was done on the 21st. Any dates not in tbl_calendar revert to the default hours for that day (taken from tbl_calendar_config).

tbl_calendar_config contains the following:

day_of_week start_time end_time
monday 07:30 16:30
tuesday 07:30 16:30
wednesday 07:30 16:30
thursday 07:30 16:30
friday 07:30 13:30
saturday Null Null
sunday Null Null


which shows the default shift pattern.


Example of what is needed to be achieved for problem 1:
I need to return a number of worked days between today and the target_date based on the information in the above tables.
E.g.
Suppose today is 16-may-2005
We also have a target_date of 30-may-2005
No of days worked = 9
(one day less for the 17th, one day extra for 21st)


Example of what is needed to be achieved for problem 2:
Provide a date & time, provide an offset (in hours) return a worked date/time
E.g.
Date/Time = 18-may-05 08:30
offset = 4 hours

Return value = 16-may-05 13:30
So essentially 08:30 less 1 hour takes you to the start of that day, the 17th isn't worked so go back 3 hours from 16:30 of the 16th Which leaves 13:30.


I hope this is all (relatively) clear, any help is very much appreciated.

Dan

 

[SantaMufasa]

Dan,

Could you please post what you have coded and tried so far?


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.