I'm trying to create a query to calculate the percentage difference based on a time range (e.g.
WHERE dim_date.year BETWEEN 2009 AND 2011) spreaded on months.
My question is how to add the new column to calculate the differences (positive or negative) against same month from base year.
I'm aware they are two questions in one but if the above question is not possible, it is possible to compare a given date range against current year/month. Please see the below table for a better overview of the question. This is the desired output..
The simple SELECT statement (without COUNT or SUM) is like this:
SELECT dim_date.year, dim_date.month, fact.total_cost FROM fact INNER JOIN dim_date ON fact.date_key = dim_date.date_key WHERE dim_date.year BETWEEN 2009 AND 2011
For simplicity please have a look at below star schema.
Thank you in advance for any help.