Filling gaps in sparse data
Apologies if this is a newbie, but I am struggling to find a solution as my SQL knowledge is not great. Below is a simple example of my dataset. The table can have 1 style or 15,000 styles and the solution is to fill in spaces with units = 0 for month spaces
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 result I am aiming 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 that 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, only the 2 to 8 month range for style 109 B5 000 is used
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 help that can be provided to help me achieve the desired result would be greatly appreciated.
+3
source to share
3 answers
Here you go. I am setting your sample data to a table variable, but you can just replace 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
+1
source to share
Another way without using 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
+1
source to share
See if 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 original data. In my example, it has only 3 posts with one style. The result for me is shown below.
Result:
0
source to share