17.10 - Example - Advanced SQL Engine - Teradata Database

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

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
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
   FROM
   (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