I am currently trying to calculate the standard deviation of the amount of products we have sold at our company over the past 12 months.
I have a table that contains every date over the past year and is updated moving forward. I use a left outer join on our sales table to come up with 4 columns:
Year | Month | Part ID | Value
The Year and Month columns come from the date table, and the Part ID and Value represent a summation from the sales table, where the summation is grouped based on Sales Year, Sales Month, and Part ID.
Now, I can calculate standard deviation fine using this.
SELECT PART_ID, STDEV(VALUE) STANDARD_DEV_VALUE FROM QUERYX GROUP BY PART_ID
This works fine when I have a sale for a given item each month, as it returns 12 months of data. But, if a part does not have a sale, I do not have data for that month.
For example, here is a possible output:
YEAR | MONTH | PART | VALUE 2016 | 1 | X | 100.0
when what I need is:
YEAR | MONTH | PART | VALUE 2016 | 1 | X | 100.0 2016 | 2 | X | 0 2016 | 3 | X | 0 and so on upto a full 12 months...
If I don't have this, I am not accurately calculating the standard deviation.
So, can someone help explain to me how I could fill in the Part ID when there are no sales? I have no link between the Part and the Dates, so I can only link on the sales dates. Thus, my issue.
This needs to be done in a single query, as there is other data being pulled here. Cursors are not an option. A temporary table is possible but it would have to execute very quickly.