calculating change (over a period) for a dated field
- by morpheous
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}
