Hello,
I have a request from a user to look at the "DATE" field, below, and to grab the total number of discharges that occurred 30 days prior.
I'm able to build, into my data model, the discharges that occurred 30 days prior and group them into days:
| Date - 30 | Discharges |
| 12/9/2018 | 253 |
| 12/22/2018 | 273 |
| 12/30/2018 | 253 |
| 1/1/2019 | 273 |
| | 1,052 |
I am using the "DATE - 30" to tie the prior discharges to the current "DATE".
| CalendarYearMonth | DATE | DATE - 30 | Plan | Total Acct | Discharges -30 days | Unmatched Volume |
| 2019-01 | 2019-01-08 00:00:00.000 | 2018-12-09 00:00:00.000 | MINNESOTA GEN MCP M | 2 | 253 | 0.8% |
| 2019-01 | 2019-01-08 00:00:00.000 | 2018-12-09 00:00:00.000 | MONTANA M | 2 | 253 | 0.8% |
| 2019-01 | 2019-01-08 00:00:00.000 | 2018-12-09 00:00:00.000 | SOUTH DAKOTA M | 22 | 253 | 8.7% |
| 2019-01 | 2019-01-08 00:00:00.000 | 2018-12-09 00:00:00.000 | NEBRASKA TC | 1 | 253 | 0.4% |
| 2019-01 | 2019-01-08 00:00:00.000 | 2018-12-09 00:00:00.000 | NEBRASKA WC | 0 | 253 | 0.0% |
| | | | | | | |
| 2019-01 | 2019-01-21 00:00:00.000 | 2018-12-22 00:00:00.000 | SOUTH DAKOTA M | 4 | 273 | 1.5% |
| | | | | | | |
| 2019-01 | 2019-01-29 00:00:00.000 | 2018-12-30 00:00:00.000 | MONTANA M | 1 | 253 | 0.4% |
| 2019-01 | 2019-01-29 00:00:00.000 | 2018-12-30 00:00:00.000 | SOUTH DAKOTA M | 5 | 253 | 2.0% |
| 2019-01 | 2019-01-29 00:00:00.000 | 2018-12-30 00:00:00.000 | WYOMING M | 4 | 253 | 1.6% |
| | | | | | | |
| 2019-01 | 2019-01-31 00:00:00.000 | 2019-01-01 00:00:00.000 | DENVER HM CHO | 1 | 273 | 0.4% |
| 2019-01 | 2019-01-31 00:00:00.000 | 2019-01-01 00:00:00.000 | WYOMING M | 1 | 273 | 0.4% |
| | | | This is what I have | 43 | 526 | 8.2% |
| | | | | | | |
| | | | | | | |
| | | | This is what I want | 43 | 1052 | 4.1% |
While this is fine I can not seem to calculate correctly the total number of discharges.
So for "CalendarYearMonth" of 2019-01 I have 43 "Total Acct". I'm trying to produce a calculation that will show the total distinct discharges of 1052; not the 526 that I currently have.
Any insight on how to correctly calculate the total discharges will be greatly appreciated.
John T
