SQLTeam.com | Weblogs | Forums

T Sql for price for a given year when there are different effective dates


#1

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.


#2

Could you please post your CREATE TABLE statements, INSERT INTO statements and the query you've been working on?


#3
DECLARE @t table(ProcedureID int, EffectiveDate date, Charge money);
INSERT @t (
      ProcedureID
    , EffectiveDate
    , Charge
      )
VALUES (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);
WITH t
AS
   (
   SELECT ProcedureID, DatePart(yy, EffectiveDate) [Year], Charge FROM @t
   )
SELECT ProcedureID, [2014], [2015], IsNull([2016], [2015]) [2016],  IsNull([2017], IsNull([2016], [2015])) [2017], IsNull([2018],IsNull([2017], IsNull([2016], [2015]))) [2018]
FROM t
PIVOT (Max(Charge) FOR Year IN([2014], [2015], [2016], [2017], [2018])) p;

image


#4

Thank you it worked exactly what i wanted.


#5

Does the price always increase?