MySQL data warehouse calculate difference over years in percentage

data-warehouseMySQLmysql-5.6

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..

enter image description here

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.

Star Schema

Thank you in advance for any help.

Best Answer

Ponder a LEFT JOIN something like this

SELECT  a.year,
        a.month,
        a.total_cost,
        IFNULL(( b.total_cost - a.total_cost ) / a.total_cost ),
                'BASE') AS Diff
    FROM  fact AS b
    LEFT JOIN  fact AS a  ON  a.year  = b.year - 1
                         AND  a.month = b.month
    WHERE  b.year BETWEEN 2009+1 AND 2011+1
      AND  a.year BETWEEN 2009   AND 2011;
Related Question