How to insert missing numbers that depends on multiple columns sql

Question

so I have this kind of table:

What you can see is that I have a missing month number which is 1. What I want is to insert a value that is like this to my table:

Month  Year  Col1        Forecast   Customer
1      2015  HD/FB/BK/      0          AFC

since that was a missing month, so I want the forecast to be 0 value.

And my parameters for such insert would be by year,col1 and customer.

I dont get your missing number .. there is month 1 after 12 , what do you mean by missing number ? — japzdivino
– japzdivino, Commented Sep 29, 2015 at 2:16
as I said, the missing month depends on per row and by year, col1, and customer's value. So you can see there that I want to insert the missing month which is 1 and that is according to the year 2015, col1: HD/FB/BK/ and by customer: AFC — Eraniichan
– Eraniichan, Commented Sep 29, 2015 at 2:28

Felix Pamittan · Accepted Answer · 2015-09-29 02:25:21Z

1

First, generate rows of all month-year combination based on your parameters. Then use NOT EXISTS to insert missing rows:

DECLARE @yr         INT,
        @col1       VARCHAR(50),
        @customer   VARCHAR(50);

WITH Cte(mnt, yr, col1, customer) AS(
    SELECT *, @yr, @col1, @customer
    FROM(VALUES
        (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)
    )t(N)
)
INSERT INTO tbl(Month, Year, Col1, Forecast, Customer)
    SELECT
        mnt,
        yr,
        col1,
        0,
        customer
    FROM Cte c
    WHERE
        NOT EXISTS(
            SELECT 1 
            FROM tbl
            WHERE
                Col1 = c.col1
                AND Customer = c.customer
                AND Month = c.mnt
                AND Year = c.yr
        )

answered Sep 29, 2015 at 2:25

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James Jithin · Accepted Answer · 2015-09-29 02:48:09Z

Approach here is to generate combinations of month and col1 and match with the current table and generate the insert for missing rows. You may extend this with other required combinations of customer and year. This works well if there is no single entry for a particular combination.

Step 1: Create combinations table

SELECT 
    *
FROM
    (SELECT 1 month 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) months
        JOIN
    (SELECT 'HD/FB/BK' colname UNION ALL SELECT 'HD/FB/BL') col1

Step 2: Find missing items

select expected.* from data_table dt right join (SELECT 
    *
FROM
    (SELECT 1 month 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) months
        JOIN
    (SELECT 'HD/FB/BK' colname UNION ALL SELECT 'HD/FB/BL') col1) expected on (dt.month = expected.month and dt.Col1 = expected.colname) where dt.col1 is null

Step 3: Insert missing values (All combined)

insert into data_table
select datainsert.month, 2015, datainsert.colname, 0.0, 'AFC' FROM
(select expected.* from data_table dt right join (SELECT 
    *
FROM
    (SELECT 1 month 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) months
        JOIN
    (SELECT 'HD/FB/BK' colname UNION ALL SELECT 'HD/FB/BL') col1) expected on (dt.month = expected.month and dt.Col1 = expected.colname) where dt.col1 is null) datainsert;

Collectives™ on Stack Overflow

How to insert missing numbers that depends on multiple columns sql

2 Answers 2

Comments

Step 1: Create combinations table

Step 2: Find missing items

Step 3: Insert missing values (All combined)

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Step 1: Create combinations table

Step 2: Find missing items

Step 3: Insert missing values (All combined)

Comments

Your Answer

Sign up or log in

Post as a guest

Related