I just to let you know that i have searched forum before posting this , may be requirement is too specific.
I have a Ztable shown below:
Company code | Calendar ID | Range ID | Hour range from | Hour range to |
CC1 | IT | R1 | 09:00:00am | 01:00:00pm |
CC1 | IT | R2 | 02:00:00am | 06:00:00pm |
I now have to calculate time difference between two dates (considering holidays in calendar ) also considering times based on working hours in the range table.
creation date1: 05/12/2014
creation hour1: 09:15:00am
Creation date2: 08/12/2014
Creation hour2: 10:00:00am
The difference is: 07:45:00 + 01:00:00 = 08:45:00.
Please provide some inputs on how to proceed and various scenarios i need to consider to avoid miscalculation.