Insert rows based on a condition

[AlterCom]

Hello,

Need your help in solving an issue.

I'm trying to insert a number of rows in the data. The number of rows isn't fixed. For eg. for some transactions, 2 rows have to be inserted, for others, the number is 5 and so on. 

Following data will clear this out:

date_time	time_slot
2019-08-21 10:23:12	20-30
2019-08-21 10:24:01	20-30
2019-08-21 11:14:07	10-20
2019-08-21 11:23:09	20-30
2019-08-21 11:50:45	50-60

Desired output: 

date_time	time_slot
2019-08-21 10:23:12	20-30
2019-08-21 10:24:01	20-30
2019-08-21 10:30:00	30-40
2019-08-21 10:40:00	40-50
2019-08-21 10:50:00	50-60
2019-08-21 11:00:00	0-10
2019-08-21 11:14:07	10-20
2019-08-21 11:23:09	20-30
2019-08-21 11:30:00	30-40
2019-08-21 11:40:00	40-50
2019-08-21 11:50:45	50-60

Please guide!

[MichalM]

@AlterCom 

Could you please provide a bit of direction around the logic you apply to insert the rows? Is it the missing time slots as each day needs to have all of these populated?

[AlterCom]

@MichalM Yes! These are the missing time slots. Have marked them in bold in the post as well.

So basically, if there is no entry in the 10 min slot interval, then a dummy value of "start time of slot" should be inserted in the data

[MichalM · Accepted solution]

Gotya!

In which case I'd do the following

• Assign the existing date_time values to the appropriate rounded slots
  • I split the date time into date and time
  • And used a formula shared by @jdunkerley79 here

DateTimeAdd(
[date_time1],
Round(
  DateTimeDiff([date_time], [date_time1], "minutes")
   , 10)
, "minutes")

• Calculate Min and Max of date time
• Use the Generate Rows tool to calculate all 10min increments between Min and Max
• Use the minute information from date_time to generate time_slot 

DateTimeFormat([date_time],"%M") + "-" + DateTimeFormat(DateTimeAdd([date_time], 10, "minutes"),"%M")

• Finally, perform a right outer join by combining the Join tool and the Union tool to append the missing data

Example workflow attached

 

[AlterCom]

@MichalM Thank you!