I have to calculate the time of taking charge of a ticket taking into account only the hours of opening and closing every day.
Monday : 08h00 - 12h00 and 14h00 - 16h00
Tuesday : 08h00 - 11h00 and 16h00 - 20h00
Wednesday : 11h00 - 16h00 and 21h00-Thursday08h00
Thursday : 10h00-15h00 and 20h00-Friday09h00
Saturday : 08h00-12h00
Here are the data
06/02/2017 07:00:00 06/02/2017 15:00:00
06/02/2017 21:00:00 07/02/2017 08:30:00
08/02/2017 22:00:00 09/02/2017 07:00:00
09/02/2017 16:00:00 09/02/2017 20:30:00
11/02/2017 11:00:00 13/02/2017 09:00:00
And I would like to have the following results
Date_recept Date_traitement Duration
06/02/2017 07:00:00 06/02/2017 15:00:00 05:00:00
06/02/2017 21:00:00 07/02/2017 08:30:00 00:30:00
08/02/2017 22:00:00 09/02/2017 07:00:00 09:00:00
09/02/2017 16:00:00 09/02/2017 20:30:00 00:30:00
11/02/2017 11:00:00 13/02/2017 09:00:00 02:00:00
Any ideas ?
Thanks in advance,
Thanks, but we should not count the hours outside of the opening and closing hours. As I did in the above examples.
The duration is calculated according to the hours of opening and closing. Hours outside the opening and closing times are ignored
I tried but it did not meet my expectations.
The opening and closing hours are not fixed, variable for each day.
And the duration is calculated according to the hours of opening and closing. Hours outside the opening and closing times are ignored
You have to create two new fields having closing and opening hours and left join them with the fact table.
You will not get 100% accurate ready-made answer...
Try to use the logic provided and implement it with some additional changes.