CODEWITHSUNDEEP
sql-server
Shyam Pandian
Shyam Pandian

Reputation: 45

Fill in missing values in a table in SQL

This is how my table looks like..i want to fill in the missing week numbers in 2015 and 2016,say 1-52 weeks in 2015 and again 1-52 for 2016 for the same product and in that particular location.

row product loc     year    week    quantity
1   A       X      2015      2        30
2   A       X      2015      4        34
3   A       X      2015      5        45
4   A       X      2015      6        56
5   A       X      2015      7        324
6   A       X      2015      8        56
7   A       X      2015      9        67
8   A       X      2016      1        34
9   A       X      2016      2        76
10  A       X      2016      3        45

output would be like

row product loc     year    week    quantity
1   A       X      2015      1        0
2   A       X      2015      2        30
3   A       X      2015      3        0
4   A       X      2015      4        34
5   A       X      2015      5        45
6   A       X      2015      6        56
7   A       X      2015      7        324
8   A       X      2015      8        56
52  A       X      2015      52        0
53  A       X      2016      1        34
54  A       X      2016      2        76
55  A       X      2016      3        45
106 A       X      2016      53       0 ...

Updating the table with values did not work and also tried cross apply..

Upvotes: 0

Views: 1130

Answers (1)

Jan W
Jan W

Reputation: 474

I would use a dummy table with week numbers and then a simple left join.

CREATE TABLE #TABLE
(
    [WEEK] INT,
    [WEEK_DATA] VARCHAR(100)
)

INSERT INTO #TABLE
SELECT 1, 'DATA FOR WEEK 1' UNION
SELECT 3, 'DATA FOR WEEK 3' UNION
SELECT 10, 'DATA FOR WEEK 10'

SELECT * FROM #TABLE

---

DECLARE @WEEK INT
SET @WEEK = 1

DECLARE @DUMMY TABLE
(
    [WEEK] INT 
)

WHILE @WEEK <= 52
BEGIN
    INSERT INTO @DUMMY
    SELECT @WEEK
    SET @WEEK = @WEEK + 1
END

SELECT
    T1.[WEEK],
    WEEK_DATA = CASE WHEN T2.WEEK_DATA IS NULL THEN 'NO DATA' ELSE T2.WEEK_DATA END
FROM
    @DUMMY AS T1
LEFT JOIN
    #TABLE AS T2 ON T1.[WEEK] = T2.[WEEK]

EDIT:

CREATE TABLE #PROD
(
    PRODUCT VARCHAR(1),
    LOC VARCHAR(1),
    [YEAR] VARCHAR(4),
    [WEEK] INT,
    QUANTITY INT
)

INSERT INTO #PROD
SELECT 'A', 'x', 2015, 2, 30 union
SELECT 'A', 'x', 2015, 4, 34 union
SELECT 'A', 'x', 2015, 5, 45 union
SELECT 'A', 'x', 2015, 6, 56 union
SELECT 'A', 'x', 2015, 7, 324 union
SELECT 'A', 'x', 2015, 8, 56 union
SELECT 'A', 'x', 2015, 9, 67 union
SELECT 'A', 'x', 2016, 1, 34 union
SELECT 'A', 'x', 2016, 2, 76 union
SELECT 'A', 'x', 2016, 3, 45

DECLARE @WEEK INT
SET @WEEK = 1

DECLARE @DUMMY TABLE
(
    [WEEK] INT 
)

WHILE @WEEK <= 52
BEGIN
    INSERT INTO @DUMMY
    SELECT @WEEK
    SET @WEEK = @WEEK + 1
END

SELECT
    [ROW] = ROW_NUMBER() OVER(ORDER BY X.[YEAR], X.[WEEK]),
    X.*
FROM
(
SELECT
    PRODUCT = CASE WHEN P.PRODUCT IS NULL THEN 'A' ELSE P.PRODUCT END,
    LOC = CASE WHEN P.LOC IS NULL THEN 'x' ELSE P.LOC END,
    [YEAR] = CASE WHEN P.[YEAR] IS NULL THEN 2015 ELSE P.[YEAR] END,
    [WEEK] = D.[WEEK],
    QUANTITY = CASE WHEN P.QUANTITY IS NULL THEN 0 ELSE P.QUANTITY END
FROM
    @DUMMY AS D
LEFT JOIN
    #PROD AS P
ON
    D.[WEEK] = P.[WEEK]
AND P.[YEAR] = 2015

UNION

SELECT
    PRODUCT = CASE WHEN P.PRODUCT IS NULL THEN 'A' ELSE P.PRODUCT END,
    LOC = CASE WHEN P.LOC IS NULL THEN 'x' ELSE P.LOC END,
    [YEAR] = CASE WHEN P.[YEAR] IS NULL THEN 2016 ELSE P.[YEAR] END,
    [WEEK] = D.[WEEK],
    QUANTITY = CASE WHEN P.QUANTITY IS NULL THEN 0 ELSE P.QUANTITY END
FROM
    @DUMMY AS D
LEFT JOIN
    #PROD AS P
ON
    D.[WEEK] = P.[WEEK]
AND P.[YEAR] = 2016
) AS X

Upvotes: 1

Related Questions