Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community,
I have to calculate the time of taking charge of a ticket taking into account only the hours of opening and closing every day.
Example :
open-close
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
Date_recept Date_traitement
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,
Go through below link. You will get the idea what to do with your problem.
This link might help
try this
Date#(rDate_recept,'MM/DD/YYYY HH:MM:SS') -Date#(Date_traitement,'MM/DD/YYYY HH:MM:SS')
Hi
Directory;
LOAD Id,
Start_Time,
End_Time,
Interval(Start_Time-End_Time,'h-mm-ss') as Difference
FROM TABLE
Deepak
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
Kiran
Thank, but we should not count the hours outside of the opening and closing hours. As I did in the above examples
Have you checked the link I have provided. That link will solve your problem.
@Manish
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.