Time between tow timestamps

[harson]

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,

[MK_QSL]

Go through below link. You will get the idea what to do with your problem.

Business Working Hours Calculation

[adamdavi3s]

This link might help

LMGTFY

[dsharmaqv]

try this

Date#(rDate_recept,'MM/DD/YYYY HH:MM:SS') -Date#(Date_traitement,'MM/DD/YYYY HH:MM:SS')

[#5]

Hi

Directory;

LOAD Id,

     Start_Time,

     End_Time,

     Interval(Start_Time-End_Time,'h-mm-ss') as Difference

FROM TABLE

[harson]

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

[harson]

Kiran

Thank, but we should not count the hours outside of the opening and closing hours. As I did in the above examples

[MK_QSL]

Have you checked the link I have provided. That link will solve your problem.

[harson]

@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

[MK_QSL]

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.