I have two tables with the following schema:

CREATE TABLE sales_data (
     sales_time date NOT NULL,
     product_id integer NOT NULL,
     sales_amt double NOT NULL
);

CREATE TABLE date_dimension (
  id integer  NOT NULL,
  datestamp   date NOT NULL,
  day_part    integer NOT NULL,
  week_part   integer NOT NULL,
  month_part  integer NOT NULL,
  qtr_part    integer NOT NULL, 
  year_part   integer NOT NULL, 
);

I want to write two types of queries that will allow me to calculate:

• period on period change (e.g. week on week change)
• change in period on period change (e.g. change in week on week change)

I would prefer to write this in ANSI SQL, since I dont want to be tied to any particular db.

[Edit]

In light of some of the comments, if I have to be tied to a single database (in terms of SQL dialect), it will have to be PostgreSQL

The queries I want to write are of the form (pseudo SQL of course):

Query Type 1 (Period on Period Change)
=======================================
a). select product_id, ((sd2.sales_amt - sd1.sales_amt)/sd1.sales_amt) as week_on_week_change from sales_data sd1, sales_data sd2, date_dimension dd where {SOME CRITERIA)

b). select product_id, ((sd2.sales_amt - sd1.sales_amt)/sd1.sales_amt) as month_on_month_change from sales_data sd1, sales_data sd2, date_dimension dd where {SOME CRITERIA)


Query Type 2  (Change in Period on Period Change)
=================================================
a). select product_id, ((a2.week_on_week_change - a1.week_on_week_change)/a1.week_on_week_change) as change_on_week_on_week_change from 
(select product_id, ((sd2.sales_amt - sd1.sales_amt)/sd1.sales_amt) as week_on_week_change from sales_data sd1, sales_data sd2, date_dimension dd where {SOME CRITERIA)
as a1), 
(select product_id, ((sd2.sales_amt - sd1.sales_amt)/sd1.sales_amt) as week_on_week_change from sales_data sd1, sales_data sd2, date_dimension dd where {SOME CRITERIA) as a2)
WHERE {SOME OTHER CRITERIA}