calculate the sum of last 12 months

• Mh-397891

  SSCertifiable

  Points: 6698

  More actions

  October 15, 2009 at 9:37 am

  #138768

  I want TO calculate FOR a given product the SUM OF ALL the payments FOR the LAST 12 months OF March, 2009(i.e SUM OF payments FROM march,2008 TO Feb, 2009, which IS trailing 12 months).

  I will be passing parameters: MONTH, YEAR, productID

  Please let me know. Thanks!

  Attached is the sample data.

• mcha7628

  SSC Veteran

  Points: 265

  More actions

  October 15, 2009 at 10:30 am

  #1066386

  I don't think this is pretty but it works

  declare @date datetime

  declare @prodid nvarchar(20)

  declare @m nvarchar(2)

  declare @y nvarchar(4)

  set @prodid = 'prod1'

  set @y = 2009

  set @m = 3

  set @date = cast(@m + '/1/'+ @y as datetime)

  select prodid, sum(payment)

  from (

  select *, cast(month + '/1/'+ year as datetime) as date

  from #temp1

  ) as temp2

  where date between dateadd(mm,-12,@date) and dateadd(mm,-1,@date)

  and prodid = @prodid

  group by prodid