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

FXM32
FXM32 Member
in SQL DataFlows

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.

image.png

Thank you

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @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:

    image.png

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @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:

    image.png

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • FXM32
    FXM32 Member

    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

Categories