How could we implement a SQL Flow that would split lines based on certain logic

FXM32

Hello,

I have an ask where I need to split lines when there are multiple Users working at the same time in order to accurately count how many users are working together during a certain interval.

If I have 3 users working each one during different periods I want to split the Initial rows as per below picture. If I am able to do this then I can accurately count how many users worked during a period.

For example, I can say that between 11.30 and 11.40 I had User 1 and User 3 working together.

I was trying to do this in a SQL Flow but given the fact that we cannot use CTEs nor Cursors in MYSQL 5.6 I was wondering if someone else would have an idea on how to do this. It does not have to be done with Flows, it could be ETL or anything else as long as I get the splits in a new dataset that would work for me.

Thank you

MarkSnodgrass

@FXM32 Not sure if you are still needing help on this, but I put together this in Magic ETL that may help you.

Here is what is happening in these tiles:

Use the Select columns to select just the start and end time columns and then use the Remove Duplicates to get a distinct list of all start and end times.

Add a Constants tile with a field called JoinKey and a value of 1 and then do an Inner Join on that field. This will give a list of all start and end times next to each original entry that you will use in the next tile to compare against.

Use the Formula tile to check to see if they worked during that time slot.

CASE when UserStartTime >= StartTime and UserEndTime <= EndTime then 'Yes'

ELSE 'No'

END

Use the filter tile to filter where rows equal to Yes.

Hopefully this helps.

MarkSnodgrass

@FXM32 Not sure if you are still needing help on this, but I put together this in Magic ETL that may help you.

Here is what is happening in these tiles:

Use the Select columns to select just the start and end time columns and then use the Remove Duplicates to get a distinct list of all start and end times.

Add a Constants tile with a field called JoinKey and a value of 1 and then do an Inner Join on that field. This will give a list of all start and end times next to each original entry that you will use in the next tile to compare against.

Use the Formula tile to check to see if they worked during that time slot.

CASE when UserStartTime >= StartTime and UserEndTime <= EndTime then 'Yes'

ELSE 'No'

END

Use the filter tile to filter where rows equal to Yes.

Hopefully this helps.

FXM32

Hi Mark,

Thank you very much for your help. Finally I have done it in MySQL code but I have followed the pattern you described and it worked for me.

Much appreciated