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!!
Solved! Go to 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")
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
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:
[StartHour]
[GeneratedHour]<=[EndHour]
[GeneratedHour]+1
Formula Tool to concatenate:
StartDate]+" "+tostring([GeneratedHour])