CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Giuseppe Lolli
Giuseppe Lolli

Reputation: 177

Add missing members to a table from other table SQL Server

i have 2 tables:

First table is [Table_Time]:

Month Number    Week Number
------------    -----------
1               01
1               02
1               03
1               04
2               05
2               06
2               07
2               08
3               09
3               10
3               11
3               12

The second table is [Sales]:

Product         Store           Month Number    Week Number
------------    -----------     ------------    -----------
P1              ST1             1               03
P1              ST1             1               04
P1              ST1             2               05
P1              ST1             3               09
P1              ST1             3               11
P1              ST1             3               12

My question is:

How to add missing weeks to the table [Sales]?

My expected result should be:

Product         Store           Month Number    Week Number
------------    -----------     ------------    -----------
P1              ST1             1               01
P1              ST1             1               02
P1              ST1             1               03
P1              ST1             1               04
P1              ST1             2               05
P1              ST1             2               06
P1              ST1             2               07
P1              ST1             2               08
P1              ST1             3               09
P1              ST1             3               10
P1              ST1             3               11
P1              ST1             3               12

Thanks in advance!

Upvotes: 0

Views: 58

Answers (3)

Thom A
Thom A

Reputation: 95561

Assuming you have a store and product table, just use as CROSS JOIN:

USE Sandbox;
GO
CREATE TABLE Table_time (MonthNumber int,
                         WeekNumber int);
INSERT INTO dbo.Table_time (MonthNumber,
                            WeekNumber)
VALUES (1,01),
       (1,02),
       (1,03),
       (1,04),
       (2,05),
       (2,06),
       (2,07),
       (2,08),
       (3,09),
       (3,10),
       (3,11),
       (3,12);

CREATE TABLE Store (StoreID varchar(3));
INSERT INTO Store (StoreID)
VALUES ('ST1'),('ST2');
GO

CREATE TABLE Product (ProductID varchar(2));
INSERT INTO Product
VALUES('P1'),('P2'),('P3'),('P4');
GO

SELECT *
FROM Table_time
     CROSS JOIN Product
     CROSS JOIN Store;
GO

If you then want to aggregate you can add a LEFT JOIN to your sales table:

SELECT TT.MonthNumber,
       TT.MonthNumber,
       P.ProductID,
       S.StoreID,
       COUNT(SS.ID) 
FROM Table_time TT
     CROSS JOIN Product P
     CROSS JOIN Store S
     LEFT JOIN Sales SS ON TT.MonthNumber = SS.MonthNumber
                       AND TT.WeekNumber = SS.WeekNumber
                       AND P.ProductID = SS.ProductID
                       AND S.StoreID = SS.StoreID;

Upvotes: 1

The Impaler
The Impaler

Reputation: 48780

You can do an INSERT with an "anti join", as in:

insert into sales (product, store, month_number, week_number)
select 'P1', 'ST1', t.month_number, t.week_number
from table_time t
left join sales s on t.month_number = s.month_number
                 and t.week_number = s.week_number
where s.month_number is null and s.week_number is null

Upvotes: 2

Luis Cazares
Luis Cazares

Reputation: 3585

Here's a way of doing it, you need to create a cartesian product between your products and dates. Using EXCEPT you can only bring the needed values so you can insert them in your Sales table.

WITH AllProducts AS( --This can be avoided if you already have a list of them outside of Sales
    SELECT DISTINCT Product, Store
    FROM Sales
)
SELECT p.Product,
       p.Store,
       t.MonthNumber,
       t.WeekNumber
FROM AllProducts p
CROSS JOIN Table_Time t
EXCEPT
SELECT s.Product,
       s.Store,
       s.MonthNumber,
       s.WeekNumber
FROM Sales s;

Upvotes: 1

Related Questions