SQL Server getting last X month average including missing months

Question

My monthly sales data look like this.(Wish I can draw table but it's not letting me put one in or I don't know how)

Table has two columns date and units sold. Date just have first day of month. But not all months have entry. So it might be 3/1/2017 has 20 units. then no row for 4/1/2017. And 5/1/2017 has 100 units.

I need to calculate last 3 months average for example. But there is no row for some months. How would I include the missing month into the average calculation? I can do group and average but that is leaving out the missing month in calculation.

Thanks in advance.

Just Sum the amount of units sold in that range and divide by however many months you need. No need to worry about missing months — Ryan Wilson, Apr 20 '18 at 14:03
You can draw your table here: https://www.tablesgenerator.com/text_tables and put the generated ascii in question — Ricardo Pontual, Apr 20 '18 at 14:04
Create a separate table with all months (or create a dynamic view) and `LEFT JOIN` it to your table. — PM 77-1, Apr 20 '18 at 14:04
Search for some information on building and using a calendar table. It's something you'll come back to again and again. Here's a link to get you started. There's quite a body of work out there, though. https://stackoverflow.com/questions/5635594/how-to-create-a-calendar-table-for-100-years-in-sql — Eric Brandt, Apr 20 '18 at 14:08

MtwStark · Answer 1 · 2018-04-20T17:06:06.800

-- reference date to start data aggregation
declare @refDate date = '2017-04-27'
-- number of months to go back    
declare @LastMonthsN int = 3

If you need to show all rows for last n months:

;with
m as (
    select DATEADD(mm, DATEDIFF(mm, 0, @refdate)-_month+1, 0) _month
    from ( values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) x (_month)
    where _month<=@LastMonthsN
),
f as (
    select *
    from SalesData d
    where ref_month between dateadd(mm, -@LastMonthsN, @refDate) and dateadd(mm, 0, @refDate)
)
select _month, Isnull(qty, 0) qty
from m
left join f on _month = ref_month

If you only need the period average:

;with
f as (
    select *
    from SalesData d
    where ref_month between dateadd(mm, -@LastMonthsN, @refDate) and dateadd(mm, 0, @refDate)
)
select avg(isnull(qty, 0)) periodAVG
from f

dfundako · Answer 2 · 2018-04-20T14:14:02.593

You can make a cte with all the months and then join to it. Any months without data will be nulls, so those might need to be ISNULL()'d to 0's if you find the calculations to be incorrect.

;with months AS (
SELECT CAST('2017-01-01' AS DATE) AS month_
UNION ALL
SELECT DATEADD(MONTH, 1, month_) 
FROM months
WHERE month_ <'2017-12-01'
)

SELECT 
DATEPART(QUARTER, m.month_) AS sale_quarter, --should be fine in SQL Server 2008
SUM(units_sold) AS units_sold

FROM months m

LEFT JOIN your_sales_table s
    ON s.date = m.month_

GROUP BY DATEPART(QUARTER, m.month_)

If you need a custom 3 month range and not normal quarters, just change it a little.

SELECT 
SUM(units_sold) AS units_sold

FROM months m

LEFT JOIN your_sales_table s
    ON s.date = m.month_

WHERE m.month_ IN ('2017-03-01','2017-04-01','2017-05-01')

SQL Server getting last X month average including missing months

2 Answers2

Linked