I'm trying to calculate months of coverage (for YTD calculation - needed both for most recent time period selected and for trending).
I have data structure:
RYDate EnrollStartDate EnrollEndDate Member CovType
2018-01-31 2018-01-01 2018-01-31 1 A
2018-02-28 2018-02-01 2018-02-28 1 A
2018-03-31 2018-03-01 2018-03-31 1 A
2018-04-31 2018-04-01 2018-04-31 1 B
2018-05-31 2018-05-01 2018-05-31 1 B
2018-01-31 2018-01-01 2018-01-31 2 C
2018-02-28 2018-02-01 2018-02-28 2 C
2018-03-31 2018-03-01 2018-03-31 2 C
2018-04-31 2018-04-01 2018-04-31 2 C
2018-05-31 2018-05-01 2018-05-31 2 C
I want to be able to count YTD coverage months I.e. If I have RYDate="2018-03-31" selected, I would count 3 months (coverage A) for Member 1, and 3 months (coverage C) for Member 2. Given more than one RYDate can be selected at once for trending, I need this to work for multiple date selections. I.e. if RYDate has the following values selected: "2018-01-31", "2018-02-28", "2018-03-31" - then I would need to count 1 month of coverage for Member 1 for RYDate=2018-01-31, 2 months of coverage for Member 1 for RYDate=2018-02-28, etc. How can this be calculated? Is AGGR necessary?
