Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Location
    Corpus Christi, TX
    Posts
    26

    Unanswered: Three Previous Month Sum

    Is there an easy way to calculate a sum for the previous three months?

    Data
    Date,Area,PropertyID,Volume,DaysInMonth
    197904,6,888888,9589,30
    197905,6,888888,27403,31
    197906,6,888888,17130,30
    197907,6,888888,14321,31
    197908,6,888888,15234,31
    198103,10,999999,11925,31
    198104,10,999999,21137,30
    198105,10,999999,17141,31
    198106,10,999999,18221,30
    198107,10,999999,12126,31
    198108,10,999999,8186,31
    197904,5,888888,9589,30
    197905,5,888888,27403,31
    197906,5,888888,17130,30
    197907,5,888888,14321,31
    197908,5,888888,15234,31

    Desired Output
    Date,Area,PropertyID,Volume,DaysInMonth,3MonthSum, DaysInMonthSUm
    197904,6,888888,9589,30,NULL,NULL
    197905,6,888888,27403,31,NULL,NULL
    197906,6,888888,17130,30,NULL,NULL
    197907,6,888888,14321,31,54122,91
    197908,6,888888,15234,31,58854,92
    198103,10,999999,11925,31,NULL,NULL
    198104,10,999999,21137,30,NULL,NULL
    198105,10,999999,17141,31,NULL,NULL
    198106,10,999999,18221,30,50203,92
    198107,10,999999,12126,31,56499,91
    198108,10,999999,8186,31,47488,92
    197904,5,888888,9589,30,NULL,NULL
    197905,5,888888,27403,31,NULL,NULL
    197906,5,888888,17130,30,NULL,NULL
    197907,5,888888,14321,31,54122,91
    197908,5,888888,15234,31,58854,92

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    What you are trying to accomplish is a modified running total. Though not matching your requested output, I believe this shows a more complete picture.
    I also used a standard date data type so that I could use the DateAdd function.

    Code:
    declare @t table
    (
       cdate date,
       area int,
       PropertyID int,
       Volume int,
       DaysInMonth int
    )
    
    Insert @t (cdate, area, PropertyID, Volume, DaysInMonth) Values
      
    ('1979-04-01',6,888888,9589,30),
    ('1979-05-01',6,888888,27403,31),
    ('1979-06-01',6,888888,17130,30),
    ('1979-07-01',6,888888,14321,31),
    ('1979-08-01',6,888888,15234,31),
    ('1981-03-01',10,999999,11925,31),
    ('1981-04-01',10,999999,21137,30),
    ('1981-05-01',10,999999,17141,31),
    ('1981-06-01',10,999999,18221,30),
    ('1981-07-01',10,999999,12126,31),
    ('1981-08-01',10,999999,8186,31),
    ('1979-04-01',5,888888,9589,30),
    ('1979-05-01',5,888888,27403,31),
    ('1979-06-01',5,888888,17130,30),
    ('1979-07-01',5,888888,14321,31),
    ('1979-08-01',5,888888,15234,31)
    
    ;with cte as
    (
       select row_number() over(Partition by propertyID, area order by cdate) RowNum
         from @t
    )
    
    select t1.cdate, t1.area, t1.propertyID, t1.volume, t1.daysInMonth,
      (select case when cte.Rownum < 3 then null else sum(daysInMonth) end
         from @t t2
         where t2.propertyID = t1.propertyID
           and t2.area = t1.area
           and t2.cdate between dateadd(month, -2, t1.cdate) and t1.cdate) as sum3Months,
      (select case when cte.RowNum < 3 then null else sum(volume) end
         from @t t2
         where t2.propertyID = t1.propertyID
           and t2.area = t1.area
           and t2.cdate between dateadd(month, -2, t1.cdate) and t1.cdate) as sumVolume
      from @t t1
      join cte on cte.propertyID = t1.propertyID and cte.area = t1.area and cte.cdate = t1.cdate
    Last edited by LinksUp; 09-17-15 at 19:16.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Using LinksUp dataset above, try the following query:
    Code:
    SELECT *
    FROM   @t As t
     OUTER
     APPLY (
            SELECT Sum(Volume) As ThreeMonthSum
            FROM   @t
            WHERE  PropertyID = t.PropertyID
            AND    area = t.area
            AND    cdate <= DateAdd(mm, -1, t.cdate)
            AND    cdate >  DateAdd(mm, -4, t.cdate)
           ) As x
    ;
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •