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

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

 

missing-slots.yxmd