Un-binning? dates/period to days per month

[TheApprentice]

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

[CharlieS · Accepted 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. 

[acastelazo]

Here's one way you can do it with the "Generate Rows" tool.