Worked hours for a day

[Anonymous]

Hallo,

I have a record with these fields:

- Employee nbr.

- Date/time start

- Date/time finish

I have to make a qw pivot table with the days on the x-axe, the employee nbr on the y-axe and show the worked hour for a day/employee.

The problem is that the start date and the finish date could be not the same (e.g. from 22:00 of March 7th to 06:00 of March 8th), so the right hours-for-a-day should be: 2 on March 7th and 6 on March 8th, but I'm not able to show them in a qw pivot table, bacause I have them in only record and not in two.

I Hope the problem is clear 🙂

Can anybody help me to find a way to solve this riddle?

Thank you.

nick.

[sbaldwin · Accepted solution]

Hi, Nick, look at the intervalmatch script function, this will allow you to join or associate each day between day between the start and the end then calculate the number of hours, or easier if a shift only went over a single day boundary for the records going over the midnight boundary just load a table which concatenates the start date with the hours from the start till midnight then the end date with the hours from midnight to the end (then add on the records which just exist in a single day. ie the table

EMP     START                    END

A          01/05/2005 22:00     02/05/2005     03:00

B          02/05/2005 05:00     02/05/2005     10:00  

would become

EMP     DATE               hours

A          01/05/2005        2

A          02/05/2005        3

B          02/05/2005        5

once you have the table in this structure the pivot table should be straght forward.

Thanks

Steve

[Anonymous]

Hi Steve,

I wasn't able to use the INTERVALMATCH function, but I get your 2nd suggestion and I solve the problem with a UNION ALL into the SELECT statement.

Thank you very much for your reply.

Bye.

Nick.