16.20 - Example - Advanced SQL Engine - Teradata Database

Consider the itemID, smonth, and sales columns from sales_table:

   SELECT itemID, smonth, sales 
   FROM fiscal_year_sales_table 
   ORDER BY itemID, smonth;
   itemID  smonth    sales
   ------  --------  -----
   A              1    100
   A              2    110
   A              3    120
   A              4    130
   A              5    140
   A              6    150
   A              7    170
   A              8    190
   A              9    210
   A             10    230
   A             11    250
   A             12  ?
   B              1     20
   B              2     30
   ...

Assume that the NULL in the sales column is because in this example the month of December (month 12) is a future date and the value is unknown.

The following statement uses MLINREG to display the expected sales using past trends for each month for each product using the sales data for the previous six months.

   SELECT itemID, smonth, sales, MLINREG(sales,7,smonth)
   FROM fiscal_year_sales_table;
   GROUP BY itemID;
   itemID  smonth    sales  MLinReg(sales,7,smonth)
   ------  --------  -----  -----------------------
   A              1    100  ?
   A              2    110  ?
   A              3    120          120
   A              4    130          130
   A              5    140          140
   A              6    150          150
   A              7    170          160
   A              8    190          177
   A              9    210          198
   A             10    230          222
   A             11    250          247
   A             12  ?              270
   B              1     20  ?
   B              2     30  ?
   ...