Pivot a table on start and end times

[rotsanders]

I have a table that has a start and end datetime for a certain event. I am trying to find a way to pivot it out so that every hour that this event occurs has it's own row.

Below is an example of what I'm trying to do. I want to go from the first table to the second table. Does anyone know a good way to do this? 

ID	start	end
1	8/30/2012 6:15	8/30/2012 8:27
2	4/4/2012 8:25	4/4/2012 14:47
3	10/4/2010 10:10	10/4/2010 10:11
ID	Hour
1	8/30/2016 6
1	8/30/2016 7
1	8/30/2016 8
2	4/4/2012  8
2	4/4/2012 9
2	4/4/2012 10
2	4/4/2012 11
2	4/4/2012 12
2	4/4/2012 13
2	4/4/2012  14
3	10/4/2010  10

Thanks!!

[jdunkerley79 · Accepted solution]

the easiest way is to use a generate rows tool.

First convert the start and end columns into datetimes and then you can use the DateTime add function.

The generate rows would start at:

datetimetrim(start, "hour")

and end at:

hour <= datetimetrim(end, "hour")

the step is:

datetimeadd(Hour,1, "hours")

[dataMack]

Here's how I would approach it, though there are likely several ways to solve the problem:

1. First I would use a Transpose tool - ID is your key feild and start and end are your data fields.  This will put all the dates in the same column with the ID field repeated.

2. Next I would use a formula tool to truncate the hour and make that its own field

3. Then a multi-row formula tool to interrogate the difference in hours between between start and end, and make that a field

4. Using the value from steps 2 and 3, you use a Generate  Rows tool to insert the # of rows needed (#3)

5. Then another Multi-Row fomula tool to fill in the details of the new rows

[SophiaF]

On the off chance you are looking to keep the formatting standardized, here's my take:

Use RegEx to parse [start] and [end] into Date and Hour:

(\d+\W\d+\W\d{4})\s(\d+)

Generate Rows:

• Initialization Expression

• [StartHour]

• Condition Expression

• [GeneratedHour]<=[EndHour]

• Loop Expression

• [GeneratedHour]+1

Formula Tool to concatenate:

StartDate]+" "+tostring([GeneratedHour])