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:

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.


1 Answer 1


Ponder a LEFT JOIN something like this

SELECT  a.year,
        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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.