I am writing a simple data warehouse that will allow me to query the table to observe periodic (say weekly) changes in data, as well as changes in the change of the data (e.g. week to week change in the weekly sale amount).
For the purposes of simplicity, I will present very simplified (almost trivialized) versions of the tables I am using here. The sales data table is a view and has the following structure:
CREATE TABLE sales_data (
sales_time date NOT NULL,
sales_amt double NOT NULL
)
For the purpose of this question. I have left out other fields you would expect to see - like product_id, sales_person_id etc, etc, as they have no direct relevance to this question. AFAICT, the only fields that will be used in the query are the sales_time and the sales_amt fields (unless I am mistaken).
I also have a date dimension table with the following structure:
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,
);
which partition dates into reporting ranges.
I need to write queries that will allow me to do the following:
Return the change in week on week sales_amt for a specified period. For example the change between sales today and sales N days ago - where N is a positive integer (N == 7 in this case).
Return the change in change of sales_amt for a specified period. For in (1). we calculated the week on week change. Now we want to know how that change is differs from
the (week on week) change calculated last week.
I am stuck however at this point, as SQL is my weakest skill. I would be grateful if an SQL master can explain how I can write these queries in a DB agnostic way (i.e. using ANSI SQL).
