I am making an attempt to create a question to calculate the share distinction primarily based on a time vary (e.g.
WHERE dim_date.yr BETWEEN 2009 AND 2011) spreaded on months.
My query is how you can add the brand new column to calculate the variations (optimistic or unfavorable) towards identical month from base yr.
I am conscious they’re two questions in a single but when the above query just isn’t attainable, it’s attainable to check a given date vary towards present yr/month.
Please see the beneath desk for a greater overview of the query. That is the specified output..
The straightforward SELECT assertion (with out COUNT or SUM) is like this:
SELECT dim_date.yr, dim_date.month, reality.total_cost FROM reality INNER JOIN dim_date ON reality.date_key = dim_date.date_key WHERE dim_date.yr BETWEEN 2009 AND 2011
For simplicity please take a look at beneath star schema.
Thanks upfront for any assist.