Hi,
I have a table like below with charge for each item with the effective date so that from that day on wards the charge listed will be the charge of the item.
I would like to calculate the charge of an item year over year as of the 12/31 of that year.
if there is no effective date for a given year then there is no change in the charge from the last effective date.
the table looks like
ProcedureID | EffectiveDateTime | Charge |
---|---|---|
31500100 | 7/1/2014 | 124 |
31500100 | 7/1/2015 | 133 |
31500100 | 8/1/2016 | 200 |
31500100 | 9/1/2017 | 275 |
31500101 | 7/1/2014 | 87 |
31500101 | 7/1/2015 | 94 |
31500101 | 8/1/2016 | 200 |
31500105 | 7/1/2014 | 124 |
31500105 | 7/1/2015 | 133 |
and the desired results like
ProcedureID | 2014 | 2015 | 2016 | 2017 | 2018 |
---|---|---|---|---|---|
31500100 | 124 | 133 | 200 | 275 | 275 |
31500101 | 87 | 94 | 200 | 200 | 200 |
31500105 | 124 | 133 | 133 | 133 | 133 |
Any help is greatly appreciated.