sql - Fill in Date Gaps in Sparse Data -

sql - Fill in Date Gaps in Sparse Data -

i apologize if novice question, struggling find solution knowledge of sql isn't great. below simple illustration of dataset. table can have 1 style or 15,000 styles , solution needs fill in gaps units = 0 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 simple illustration of desired output 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 illustration solution on site using recursive cte adapted dataset:

;with cte_minmax ( select style, min(month) minmonth, max(month) maxmonth dbo.orders grouping style ) ,cte_months ( select style, minmonth month cte_minmax union select c.style, month + 1 cte_months c inner bring together cte_minmax mm on c.style = mm.style month + 1 <= maxmonth ) select c.* , coalesce(o.units, 0) cte_months c left bring together orders o on c.style = o.style , c.month = o.month order style, month alternative (maxrecursion 0)

however noted in output below, using month range of 2 through 8 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 can given help me accomplish desired output appreciated.

here go. setup sample info table variable, can substitute @data references table name.

--style month units declare @data table ([style] varchar(15), [month] int, units int) insert @data select '108 p 000','1','82' union select '108 p 000','2','83' union select '108 p 000','3','84' union select '108 p 000','4','36' union select '108 p 000','5','127' union select '108 p 000','6','34' union select '108 p 000','7','83' union select '108 p 000','8','128' union select '108 p 000','9','162' union select '108 p 000','10','78' union select '108 p 000','11','55' union select '108 p 000','12','99' union select '109 b5 000','2','120' union select '109 b5 000','4','360' union select '109 b5 000','6','648' union select '109 b5 000','7','360' union select '109 b5 000','8','600' ;with months ( select 1 [month] union select [month] + 1 months [month] < 12 ), styles ( select distinct style @data ) select styles.style, months.[month], sum(coalesce(data.units, 0)) [units] months cross bring together styles left outer bring together @data info on data.style = styles.style , data.[month] = months.[month] grouping styles.style, months.[month] order 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

sql sql-server database tsql common-table-expression