Data transform in ADF. Total time work in minutes by the day.

Question

I have a SQL table on azure.

That I need to transform to the following format.

I need to transform each day by location (12+ Locations) sum total minutes worked per hour for all employs clocked in minus there breaks with in the hour.

I have over 2 years of data to run and then I like to automate it on a daily basis.

Any ideas on how to approach this is greatly appreciated.

Answer 1

Answer 2

Hi Petar Novakovic ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As I understand your requirement , you want to transform the data into desired format using ADF pipeline. However, from the screenshot , expected output is not very much clear because the rows are not overlapping between the input and expected output if you notice the locations are not matching, because of which it's difficult to determine from where all the column values are coming in output.

If you look at the total hours column , I assume it's the difference between clockindate and clockoutdate after removing the break hours , but still , the calculations are not matching in the output screenshot .

I would like to suggest you to use Derived column transformation in mapping dataflow to create new desired columns and use Date and time functions in mapping dataflow .

For example the below expression would give you the difference (in hours) between two timestamps which you can make use of to calculate the total hours.

divide(toTimestamp(clockouttime, 'yyyy-MM-dd HH:mm:ss.SSS') - toTimestamp(
clockintime, 'yyyy-MM-dd HH:mm:ss.SSS'),3600000)

You can follow this playlist to work with different scenarios in mapping dataflow.

---

Hope it helps. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well.