Fill in Date Gaps in Sparse Data

Question

I apologize if this is a novice question, but I am struggling to find a solution as my knowledge of SQL isn't great. Below is a simple example of my dataset. The table can have 1 style or 15,000 styles and the solution needs to fill in the gaps with Units = 0 for the Month gaps

Style    Month  Units
108 P 000   1   82
108 P 000   2   83
108 P 000   3   84
108 P 000   4   36
108 P 000   5   127
108 P 000   6   34
108 P 000   7   83
108 P 000   8   128
108 P 000   9   162
108 P 000   10  78
108 P 000   11  55
108 P 000   12  99
109 B5 000  2   120
109 B5 000  4   360
109 B5 000  6   648
109 B5 000  7   360
109 B5 000  8   600

Below is a simple example of the desired output I am striving for.

Style     Month Units
108 P 000   1   82
108 P 000   2   83
108 P 000   3   84
108 P 000   4   36
108 P 000   5   127
108 P 000   6   34
108 P 000   7   83
108 P 000   8   128
108 P 000   9   162
108 P 000   10  78
108 P 000   11  55
108 P 000   12  99
109 B5 000  1   0
109 B5 000  2   120
109 B5 000  3   0
109 B5 000  4   360
109 B5 000  5   0
109 B5 000  6   648
109 B5 000  7   360
109 B5 000  8   600
109 B5 000  9   0
109 B5 000  10  0
109 B5 000  11  0
109 B5 000  12  0

I found an example solution on this site using a recursive CTE which I adapted to my dataset:

;WITH CTE_MinMax AS
(
    SELECT Style, MIN(Month) AS MinMonth, MAX(Month) AS MaxMonth
FROM dbo.orders
GROUP BY Style
)
,CTE_Months AS
(
SELECT Style, MinMonth AS Month
FROM CTE_MinMax
UNION ALL
SELECT c.Style, Month + 1 FROM CTE_Months c
INNER JOIN CTE_MinMax mm ON c.Style = mm.Style
WHERE Month + 1 <= MaxMonth
)
SELECT c.* , COALESCE(o.Units, 0)
FROM CTE_Months c
LEFT JOIN Orders o ON c.Style = o.Style AND c.Month = o.Month
ORDER BY Style, Month
OPTION (MAXRECURSION 0)

However as noted in the output below, it is only using a Month range of 2 through 8 for Style 109 B5 000

Style     Month Units
108 P 000   1   82
108 P 000   2   83
108 P 000   3   84
108 P 000   4   36
108 P 000   5   127
108 P 000   6   34
108 P 000   7   83
108 P 000   8   128
108 P 000   9   162
108 P 000   10  78
108 P 000   11  55
108 P 000   12  99
109 B5 000  2   120
109 B5 000  3   0
109 B5 000  4   360
109 B5 000  5   0
109 B5 000  6   648
109 B5 000  7   360
109 B5 000  8   600

Any assistance which can be given to help me achieve my desired output would be greatly appreciated.

Answer 1

See whether this approach helps you..

Declare @Month Table
([Month] Int)
Insert into @Month 
values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

Declare @Style Table
(Style varchar(40),Month int, Units int)
Insert into @Style
values
('109 B5 000',2,120),
('109 B5 000',4,360),
('109 B5 000',6,648)

Select      LU.Style,
            M.Month,
            Isnull(S.Units,0)
From        @Month M 
Cross join  (Select Distinct Style From @Style) LU
Left join   @Style S On  Lu.Style = S.Style And M.[Month] = S.Month

Let @style be your source data. In my example, it has just 3 records, with one style. The result for me is given below.

Result:

Answer 2

Another way without using a recursive CTE:

create table #temp(
    style varchar(100),
    month int,
    value int
)
insert into #temp
select '108 P 000', 1, 82 union all
select '108 P 000', 2, 83 union all
select '108 P 000', 3, 84 union all
select '108 P 000', 4, 36 union all
select '108 P 000', 5, 127 union all
select '108 P 000', 6, 34 union all
select '108 P 000', 7, 83 union all
select '108 P 000', 8, 128 union all
select '108 P 000', 9, 162 union all
select '108 P 000', 10, 78 union all
select '108 P 000', 11, 55 union all
select '108 P 000', 12, 99 union all
select '109 B5 000', 2, 120 union all
select '109 B5 000', 4, 360 union all
select '109 B5 000', 6, 648 union all
select '109 B5 000', 7, 360 union all
select '109 B5 000', 8, 600

--select * from #temp

;with months(m) as(
    select 1 union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all
    select 7 union all select 8 union all select 9 union all
    select 10 union all select 11 union all select 12
)
select
    s.style,
    m.m,
    value = isnull(t.value, 0)
from months m
cross join(
    select distinct style from #temp
) s
left join #temp t
    on t.month = m.m
    and t.style = s.style
order by s.style, m.m

drop table #temp

Answer 3

Here you go. I setup your sample data into a table variable, but you can just substitute my @Data references with your table name.

--Style    Month  Units
DECLARE @Data TABLE ([Style] VARCHAR(15), [Month] INT, Units INT)
INSERT @Data
    SELECT '108 P 000','1','82' UNION ALL
    SELECT '108 P 000','2','83' UNION ALL
    SELECT '108 P 000','3','84' UNION ALL
    SELECT '108 P 000','4','36' UNION ALL
    SELECT '108 P 000','5','127' UNION ALL
    SELECT '108 P 000','6','34' UNION ALL
    SELECT '108 P 000','7','83' UNION ALL
    SELECT '108 P 000','8','128' UNION ALL
    SELECT '108 P 000','9','162' UNION ALL
    SELECT '108 P 000','10','78' UNION ALL
    SELECT '108 P 000','11','55' UNION ALL
    SELECT '108 P 000','12','99' UNION ALL
    SELECT '109 B5 000','2','120' UNION ALL
    SELECT '109 B5 000','4','360' UNION ALL
    SELECT '109 B5 000','6','648' UNION ALL
    SELECT '109 B5 000','7','360' UNION ALL
    SELECT '109 B5 000','8','600'
;WITH Months AS (
    SELECT 1 AS [Month]
    UNION ALL
    SELECT [Month] + 1
    FROM Months
    WHERE [Month] < 12
), Styles AS (
    SELECT DISTINCT Style FROM @Data
)
SELECT
    Styles.Style,
    Months.[Month],
    SUM(COALESCE(Data.Units, 0)) AS [Units]
FROM Months
    CROSS JOIN Styles
    LEFT OUTER JOIN @Data Data
        ON Data.Style = Styles.Style
            AND Data.[Month] = Months.[Month]
GROUP BY
    Styles.Style,
    Months.[Month]
ORDER BY Style, [Month]

Results

Style           Month       Units
--------------- ----------- -----------
108 P 000       1           82
108 P 000       2           83
108 P 000       3           84
108 P 000       4           36
108 P 000       5           127
108 P 000       6           34
108 P 000       7           83
108 P 000       8           128
108 P 000       9           162
108 P 000       10          78
108 P 000       11          55
108 P 000       12          99
109 B5 000      1           0
109 B5 000      2           120
109 B5 000      3           0
109 B5 000      4           360
109 B5 000      5           0
109 B5 000      6           648
109 B5 000      7           360
109 B5 000      8           600
109 B5 000      9           0
109 B5 000      10          0
109 B5 000      11          0
109 B5 000      12          0