Hi everyone!
I would greatly appreciate some help with transforming some data.
My goal is to take take two dates (a period), and dump their respective number of days into individual month buckets.
I would like to transform the following:
Client Number | Period Start | Period End | Total Days |
11111 | Jan-01-2018 | Mar-15-2018 | 74 |
22222 | Jul-02-2018 | Sep-08-2018 | 69 |
Into this:
Client Number | Period Start | Period End | Total Days | January | February | March | April | May | June | July | August | September | October | November | December |
11111 | Jan-01-2018 | Mar-15-2018 | 74 | 31 | 28 | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
22222 | Jul-02-2018 | Sep-08-2018 | 69 | 0 | 0 | 0 | 0 | 0 | 0 | 30 | 31 | 8 | 0 | 0 | 0 |
How might I go about doing this? Apologies if this has been addressed elsewhere. Please refer me to the question if that is the case.
Thanks!
//Sam
Solved! Go to Solution.
Use the Generate Rows tool to create a list of all the days, format those dates as months, then count the results in a Cross Tab tool. Check out the attached example solution to see this in action.
Edit; I added a few more tools to show how to make sure all months are output and in the correct order.