Create rows for missing hours in table

viveknarayananp

Hi,
i have a table with data

store	date	time	people
A	20160101	13:00:00	6
A	20160101	14:00:00	7
A	20160101	15:00:00	8
A	20160101	18:00:00	9
A	20160101	20:00:00	10
B	20160102	15:00:00	1
B	20160102	18:00:00	2

It has a few hours missing in between, since they have no people visiting.
I want to create rows for those missing hours for all stores and all dates that exist.
Its safe to assume we can get distinct stores and min and max date to create the range from the existing table
but I do not know how to create empty rows, such that my output would look like :

store	date	time	people
...	...	...	...
A	20160101	13:00:00	6
A	20160101	14:00:00	7
A	20160101	15:00:00	8
A	20160101	16:00:00	0
A	20160101	17:00:00	0
A	20160101	18:00:00	9
A	20160101	19:00:00	0
A	20160101	20:00:00	10
...	...	...	...
B	20160102	13:00:00	0
B	20160102	14:00:00	0
B	20160102	15:00:00	1
B	20160102	18:00:00	2
...	...	...	...

Thank you

michael_treadwell

I'll add to this to say that in the Generate Rows tool you will need to increment your time variable. DATETIMEADD() requires a Date or DateTime field and will not work just on a Time field.

To get around this, you could either combine your [date] and [time] fields as strings and the use the DateTime tool to create one DateTime field or use the Text To Columns tool to get just the integer portion of your [time] field.