A way to count records open (but not yet closed) per dynamic period
I have data that exists as such:
RECORD ID, OPEN DATE, CLOSE DATE
12345, 01/01/2020, 02/15/2020
12346, 01/15/2020, null
12347, 02/01/2020, 3/31/2020
And I have the same data pivoted as well:
RECORD HISTORY ID, RECORD ID, STATUS, STATUS CHANGE DATE
98765, 12345, Opened, 01/01/2020
98766, 12345, Closed, 02/15/2020
I am trying to produce a report that would show the records that were open (had opened, but not yet closed), grouped by period, hopefully where the period remains dynamic so my report viewer could group by month, quarter, year on the fly as they wish.
Let's say my report consumer wants to see total records that were open per month. A record opened in January but closed in mid-March would count in each of January, Feb and March's numbers. I assume this means that I have to explode my data in some way with an ETL process (I prefer to use a DataFlow) but I cannot currently wrap my brain around how to accomplish this. I'm feeling a bit dense and would love any pointers or guidance.
Thank you very much in advance.