MDIFF Function Examples | VantageCloud Lake - MDIFF Function Examples - Teradata Vantage

Display the difference between each quarter and the same quarter sales for last year for product code 10.

   SELECT year_of_calendar, quarter_of_calendar,
   MDIFF(sumPrice, 4, year_of_calendar, quarter_of_calendar)
   FROM (SELECT a2.year_of_calendar,
   a2.quarter_of_calendar, SUM(a2.Price) AS sumPrice
   FROM Sales a1, SYS_CALENDAR.Calendar a2
   WHERE a1.itemID=10 and a1.calendar_date=a2.calendar_date
   GROUP BY a2.year_of_calendar, a2.quarter_of_calendar) AS T1
   ORDER BY year_of_calendar, quarter_of_year;

The following example computes the changing market volume week over week for the stock of company Horatio Parker Imports. The ticker name for the company is HPI.

   SELECT MarketWeek, WeekVolume, 
      MDIFF(WeekVolume,1,MarketWeek) AS HPIVolumeDiff
   FROM
   (SELECT MarketWeek, SUM(Volume) AS WeekVolume
   FROM MarketDailyClosing
   WHERE Ticker = 'HPI'
   GROUP BY MarketWeek)
   ORDER BY MarketWeek;

The result may look like the following. The first row is null for column HPIVolume Diff, indicating no previous row from which to compute a difference.

MarketWeek   WeekVolume   HPIVolumeDiff
----------   ----------   -------------
11/29/1999      9817671               ?
12/06/1999     10099459          128000
12/13/1999     10099459          153788
12/20/1999     10490732          391273
12/27/1999     11045331          554599