17.10 - Example - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Functions, Expressions, and Predicates

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

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
   (SELECT MarketWeek, SUM(Volume) AS WeekVolume
   FROM MarketDailyClosing
   WHERE Ticker = 'HPI'
   GROUP BY MarketWeek)
   ORDER BY MarketWeek;

The result might look like the following table. Note that 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 9945671 128000
12/13/1999 10099459 153788
12/20/1999 10490732 391273
12/27/1999 11045331 554599