Calculate Month Over Month

• selpoivre

  Say Hey Kid

  Points: 702

  More actions

  November 3, 2018 at 1:51 pm

  #416442

  Hi ,
  I want to calculate month over month change for below data
  Date     platform     Value
  09/04/2018 X    2345
  09/05/2018 X     2678
  09/06/2018 X     2673
  10/05/2018  X   2876
  10/06/2018  X   2789

• pietlinden

  SSC Guru

  Points: 63509

  More actions

  November 3, 2018 at 3:23 pm

  #2012317

  So you want an aggregate by month first and then month over month change?
  If you're using 2008, I think you have to use a CTE to point to the previous record. If you're using 2012 or later, you can use LAG(), which makes your life infinitely easier.
  Here's how to do it in 2012+ ... You have to use a CTE to do it in previous versions...
  USE TEMPdb;
GO

  INSERT INTO #SomeData
VALUES ('09/04/2018', 'X' , 2345)
    ,('09/05/2018', 'X', 2678)
    ,('09/06/2018', 'X', 2673)
    ,('10/05/2018', 'X', 2876)
    ,('10/06/2018', 'X', 2789 ) ;

  SELECT sd.EventMonth
    , sd.MonthTotal
    , LAG(sd.MonthTotal,1) OVER (ORDER BY sd.EventMonth) AS PrevMonthTotal
    , sd.MonthTotal - LAG(sd.MonthTotal,1) OVER (ORDER BY sd.EventMonth) AS Delta
FROM
    (SELECT MONTH(EventDate) AS EventMonth
    , SUM(EventValue) AS MonthTotal
    FROM #SomeData
    GROUP BY MONTH(EventDate)) sd;

  If you're really using a super old version of SQL Server, here's a link to an example that doesn't use LAG.

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  November 3, 2018 at 11:14 pm

  #2012328

  pietlinden - Saturday, November 3, 2018 3:23 PM

  So you want an aggregate by month first and then month over month change?
  If you're using 2008, I think you have to use a CTE to point to the previous record. If you're using 2012 or later, you can use LAG(), which makes your life infinitely easier.
  Here's how to do it in 2012+ ... You have to use a CTE to do it in previous versions...
  USE TEMPdb;
  GO

  INSERT INTO #SomeData
  VALUES ('09/04/2018', 'X' , 2345)
      ,('09/05/2018', 'X', 2678)
      ,('09/06/2018', 'X', 2673)
      ,('10/05/2018', 'X', 2876)
      ,('10/06/2018', 'X', 2789 ) ;

  SELECT sd.EventMonth
    , sd.MonthTotal
    , LAG(sd.MonthTotal,1) OVER (ORDER BY sd.EventMonth) AS PrevMonthTotal
    , sd.MonthTotal - LAG(sd.MonthTotal,1) OVER (ORDER BY sd.EventMonth) AS Delta
FROM
    (SELECT MONTH(EventDate) AS EventMonth
    , SUM(EventValue) AS MonthTotal
    FROM #SomeData
    GROUP BY MONTH(EventDate)) sd;

  If you're really using a super old version of SQL Server, here's a link to an example that doesn't use LAG.

  I would rather include year as well while calculating.  What if you have values for eventdate= '09/05/2018'

  
create table #SomeData
(
eventdate date,
indicator varchar(10),
eventvalue int)

  INSERT INTO #SomeData
VALUES ('09/04/2018', 'X' , 2345)
  ,('09/05/2018', 'X', 2678)
  ,('09/06/2018', 'X', 2673)
  ,('10/05/2018', 'X', 2876)
  ,('10/06/2018', 'X', 2789 ) ;

  SELECT sd.EventMonth
  , sd.MonthTotal
  , LAG(sd.MonthTotal,1) OVER (ORDER BY sd.EventMonth) AS PrevMonthTotal
  , sd.MonthTotal - LAG(sd.MonthTotal,1) OVER (ORDER BY sd.EventMonth) AS Delta
FROM
  (SELECT concat(MONTH(EventDate),'-',YEAR(EventDate)) AS EventMonth
  , SUM(EventValue) AS MonthTotal
  FROM #SomeData
  GROUP BY concat(MONTH(EventDate),'-',YEAR(EventDate))) sd;


  Saravanan