Calculate average and standard deviation for pre defined number of values substituting missing rows with zeros

Question

I have a simple table that contains a record of products and their total sales per day over a year (just 3 columns - Product, Date, Sales). So, for example, if product A is sold every single day, it'll have 365 records. Similarly, if product B is sold for only 50 days, the table will have just 50 rows for that product - one for each day of sale.

I need to calculate the daily average sales and standard deviation for the entire year, which means that, for product B, I need to have additional 365-50=315 entries with zero sales to be able to calculate the daily average and standard deviation for the year correctly.

Is there a way to do this efficiently and dynamically in SQL?

Thanks

Answer 1

We can generate 366 rows and join the sales data to it:

WITH rg(rn) AS (
   SELECT 1 AS rn
   UNION ALL
   SELECT a.rn + 1 AS rn
   FROM   rg a
   WHERE  a.rn <= 366
)

SELECT
  *
FROM
  rg
  LEFT JOIN (
    SELECT YEAR(saledate) as yr, DATEPART(dayofyear, saledate) as doy, count(*) as numsales 
    FROM sales 
    GROUP BY YEAR(saledate), DATEPART(dayofyear, saledate) 
  ) s ON rg.rn = s.doy

OPTION (MAXRECURSION 370);

You can replace the nulls (where there is no sale data for that day) with e.g. AVG(COALESCE(numsales, 0)). You'll probably also need a WHERE clause to eliminate the 366th day on non leap years (such as MODULO the year by 4 and only do 366 rows if it's 0).

If you're only doing a single year, you can use a where clause in the sales subquery to give only the relevant records; most efficient is to use a range like WHERE salesdate >= DATEFROMPARTS(YEAR(GetDate()), 1, 1) AND salesdate < DATEFROMPARTS(YEAR(GetDate()) + 1, 1, 1) rather than calling a function on every sales date to extract the year from it to compare to a constant. You can also drop the YEAR(salesdate) from the select/group by if there is only a single year

If you're doing multiple years, you could make the rg generate more rows, or (perhaps simpler) cross join it to a list of years so you get 366 rows multiplied by e.g. VALUES (2015),(2016),(2017),(2018),(2019),(2020) (and make the year from the sales part of the join too)

Answer 2

find the first and last day of the year and then use datediff() to find number of days in that year.

After that don't use AVG on sales, but SUM(Sales) / days_in_year

select  *,
         days_in_year = datediff(day, first_of_year, last_of_year) + 1
from    (values (2019), (2020)) v(year)
        cross apply
        (
            select  first_of_year = dateadd(year, year - 1900, 0),
                    last_of_year  = dateadd(year, year - 1900 + 1, -1)
        ) d

Answer 3

There's a different way to look at it - don't try to add additional empty rows, just divide by the number of days in a year. While the number of days a year isn't constant (a leap year will have 366 days), it can be calculated easily since the first day of the year is always January 1st and the last is always December 31st:

SELECT   YEAR(date), 
         product,
         SUM(sales) / DATEPART(dy, DATEFROMPARTS(YEAR(date)), 12, 31))
FROM     sales_table
GROUP BY YEAR(date), product