[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

TSQL loop work

Hello All,

There is a table that stores data for the unique combination of account, product and time month. Now the issue – there is a possibility that many of times - the combination of account, product and month may not have all 12 month rows.
Example – Ac1 has all 12 rows for the prod1 (good)
Ac1 has only 4 rows for prod2 corresponding to months from month 8 through month 12.

Here is what I have to do something so that a code that detects the missed rows for the combination of account, product and month and inserts it into the same table – where in the value for the sales column is zero!!

The actual table is A1:D32. The missed row combination is indicated in column L through L. Now how can I do something in TSQL and / or SSIS to (1) detect the missing rows for each combination of account, product and month in that table and then (2) insert it in the same table?

Please see the attached workbook for reference


Thank you million
TSQL-question.xlsx
0
Rayne
Asked:
Rayne
2 Solutions
 
PortletPaulCommented:
Try this, it discovers the missing rows (via a left join and where clause) and inserts those:
INSERT YourTable
	([Account], [Product], [Period], [Sales])

    SELECT yt.account, yt.product, pers.per AS period, 0 AS sales
    FROM (SELECT DISTINCT account, product FROM yourtable) AS yt
    CROSS JOIN (
                  SELECT 'p1'  AS per UNION ALL
                  SELECT 'p2'  AS per UNION ALL
                  SELECT 'p3'  AS per UNION ALL
                  SELECT 'p4'  AS per UNION ALL
                  SELECT 'p5'  AS per UNION ALL
                  SELECT 'p6'  AS per UNION ALL
                  SELECT 'p7'  AS per UNION ALL
                  SELECT 'p8'  AS per UNION ALL
                  SELECT 'p9'  AS per UNION ALL
                  SELECT 'p10' AS per UNION ALL
                  SELECT 'p11' AS per UNION ALL
                  SELECT 'p12' AS per
               ) AS pers
    LEFT JOIN yourtable AS yt2
     ON yt.account = yt2.account AND yt.product = yt2.product AND pers.per = yt2.period
    WHERE yt2.account IS NULL
;

Open in new window

For more see below:
    CREATE TABLE YourTable
    	([Account] varchar(3), [Product] varchar(5), [Period] varchar(3), [Sales] int)
    ;
    	
    INSERT INTO YourTable
    	([Account], [Product], [Period], [Sales])
    VALUES
    	('Ac1', 'prod1', 'p1', 5),
    	('Ac1', 'prod1', 'p2', 6),
    	('Ac1', 'prod1', 'p3', 7),
    	('Ac1', 'prod1', 'p4', 5),
    	('Ac1', 'prod1', 'p5', 4),
    	('Ac1', 'prod1', 'p6', 4),
    	('Ac1', 'prod1', 'p7', 3),
    	('Ac1', 'prod1', 'p8', 3),
    	('Ac1', 'prod1', 'p9', 3),
    	('Ac1', 'prod1', 'p10', 3),
    	('Ac1', 'prod1', 'p11', 3),
    	('Ac1', 'prod1', 'p12', 3),
    	('Ac1', 'prod2', 'p8', 3),
    	('Ac1', 'prod2', 'p9', 3),
    	('Ac1', 'prod2', 'p10', 3),
    	('Ac1', 'prod2', 'p11', 3),
    	('Ac1', 'prod2', 'p12', 3),
    	('Ac2', 'prod1', 'p1', 1),
    	('Ac2', 'prod1', 'p2', 1),
    	('Ac2', 'prod1', 'p3', 1),
    	('Ac2', 'prod1', 'p4', 1),
    	('Ac2', 'prod1', 'p5', 1),
    	('Ac2', 'prod1', 'p6', 1),
    	('Ac2', 'prod1', 'p7', 1),
    	('Ac2', 'prod1', 'p8', 1),
    	('Ac2', 'prod1', 'p9', 1),
    	('Ac2', 'prod1', 'p10', 1),
    	('Ac2', 'prod1', 'p11', 1),
    	('Ac2', 'prod1', 'p12', 1),
    	('Ac2', 'prod2', 'p11', 1),
    	('Ac2', 'prod2', 'p12', 1)
    ;

**Query 1**:

    SELECT yt.account, yt.product, pers.per AS period, 0 AS sales
    FROM (SELECT DISTINCT account, product FROM yourtable) AS yt
    CROSS JOIN (
                  SELECT 'p1'  AS per UNION ALL
                  SELECT 'p2'  AS per UNION ALL
                  SELECT 'p3'  AS per UNION ALL
                  SELECT 'p4'  AS per UNION ALL
                  SELECT 'p5'  AS per UNION ALL
                  SELECT 'p6'  AS per UNION ALL
                  SELECT 'p7'  AS per UNION ALL
                  SELECT 'p8'  AS per UNION ALL
                  SELECT 'p9'  AS per UNION ALL
                  SELECT 'p10' AS per UNION ALL
                  SELECT 'p11' AS per UNION ALL
                  SELECT 'p12' AS per
               ) AS pers
    LEFT JOIN yourtable AS yt2
     ON yt.account = yt2.account AND yt.product = yt2.product AND pers.per = yt2.period
    WHERE yt2.account IS NULL
    

**[Results][2]**:
    
    | ACCOUNT | PRODUCT | PERIOD | SALES |
    |---------|---------|--------|-------|
    |     Ac1 |   prod2 |     p1 |     0 |
    |     Ac1 |   prod2 |     p2 |     0 |
    |     Ac1 |   prod2 |     p3 |     0 |
    |     Ac1 |   prod2 |     p4 |     0 |
    |     Ac1 |   prod2 |     p5 |     0 |
    |     Ac1 |   prod2 |     p6 |     0 |
    |     Ac1 |   prod2 |     p7 |     0 |
    |     Ac2 |   prod2 |     p1 |     0 |
    |     Ac2 |   prod2 |     p2 |     0 |
    |     Ac2 |   prod2 |     p3 |     0 |
    |     Ac2 |   prod2 |     p4 |     0 |
    |     Ac2 |   prod2 |     p5 |     0 |
    |     Ac2 |   prod2 |     p6 |     0 |
    |     Ac2 |   prod2 |     p7 |     0 |
    |     Ac2 |   prod2 |     p8 |     0 |
    |     Ac2 |   prod2 |     p9 |     0 |
    |     Ac2 |   prod2 |    p10 |     0 |


**Query 2**:

    INSERT YourTable
    	([Account], [Product], [Period], [Sales])
    
        SELECT yt.account, yt.product, pers.per AS period, 0 AS sales
        FROM (SELECT DISTINCT account, product FROM yourtable) AS yt
        CROSS JOIN (
                      SELECT 'p1'  AS per UNION ALL
                      SELECT 'p2'  AS per UNION ALL
                      SELECT 'p3'  AS per UNION ALL
                      SELECT 'p4'  AS per UNION ALL
                      SELECT 'p5'  AS per UNION ALL
                      SELECT 'p6'  AS per UNION ALL
                      SELECT 'p7'  AS per UNION ALL
                      SELECT 'p8'  AS per UNION ALL
                      SELECT 'p9'  AS per UNION ALL
                      SELECT 'p10' AS per UNION ALL
                      SELECT 'p11' AS per UNION ALL
                      SELECT 'p12' AS per
                   ) AS pers
        LEFT JOIN yourtable AS yt2
         ON yt.account = yt2.account AND yt.product = yt2.product AND pers.per = yt2.period
        WHERE yt2.account IS NULL
    

**[Results][3]**:
    

**Query 3**:

    select
    *
    from yourtable
    order by account, product, period
    
    	
    

**[Results][4]**:
    
    | ACCOUNT | PRODUCT | PERIOD | SALES |
    |---------|---------|--------|-------|
    |     Ac1 |   prod1 |     p1 |     5 |
    |     Ac1 |   prod1 |    p10 |     3 |
    |     Ac1 |   prod1 |    p11 |     3 |
    |     Ac1 |   prod1 |    p12 |     3 |
    |     Ac1 |   prod1 |     p2 |     6 |
    |     Ac1 |   prod1 |     p3 |     7 |
    |     Ac1 |   prod1 |     p4 |     5 |
    |     Ac1 |   prod1 |     p5 |     4 |
    |     Ac1 |   prod1 |     p6 |     4 |
    |     Ac1 |   prod1 |     p7 |     3 |
    |     Ac1 |   prod1 |     p8 |     3 |
    |     Ac1 |   prod1 |     p9 |     3 |
    |     Ac1 |   prod2 |     p1 |     0 |
    |     Ac1 |   prod2 |    p10 |     3 |
    |     Ac1 |   prod2 |    p11 |     3 |
    |     Ac1 |   prod2 |    p12 |     3 |
    |     Ac1 |   prod2 |     p2 |     0 |
    |     Ac1 |   prod2 |     p3 |     0 |
    |     Ac1 |   prod2 |     p4 |     0 |
    |     Ac1 |   prod2 |     p5 |     0 |
    |     Ac1 |   prod2 |     p6 |     0 |
    |     Ac1 |   prod2 |     p7 |     0 |
    |     Ac1 |   prod2 |     p8 |     3 |
    |     Ac1 |   prod2 |     p9 |     3 |
    |     Ac2 |   prod1 |     p1 |     1 |
    |     Ac2 |   prod1 |    p10 |     1 |
    |     Ac2 |   prod1 |    p11 |     1 |
    |     Ac2 |   prod1 |    p12 |     1 |
    |     Ac2 |   prod1 |     p2 |     1 |
    |     Ac2 |   prod1 |     p3 |     1 |
    |     Ac2 |   prod1 |     p4 |     1 |
    |     Ac2 |   prod1 |     p5 |     1 |
    |     Ac2 |   prod1 |     p6 |     1 |
    |     Ac2 |   prod1 |     p7 |     1 |
    |     Ac2 |   prod1 |     p8 |     1 |
    |     Ac2 |   prod1 |     p9 |     1 |
    |     Ac2 |   prod2 |     p1 |     0 |
    |     Ac2 |   prod2 |    p10 |     0 |
    |     Ac2 |   prod2 |    p11 |     1 |
    |     Ac2 |   prod2 |    p12 |     1 |
    |     Ac2 |   prod2 |     p2 |     0 |
    |     Ac2 |   prod2 |     p3 |     0 |
    |     Ac2 |   prod2 |     p4 |     0 |
    |     Ac2 |   prod2 |     p5 |     0 |
    |     Ac2 |   prod2 |     p6 |     0 |
    |     Ac2 |   prod2 |     p7 |     0 |
    |     Ac2 |   prod2 |     p8 |     0 |
    |     Ac2 |   prod2 |     p9 |     0 |



  [1]: http://sqlfiddle.com/#!3/191a2/15

  [2]: http://sqlfiddle.com/#!3/191a2/15/0

  [3]: http://sqlfiddle.com/#!3/191a2/15/1

  [4]: http://sqlfiddle.com/#!3/191a2/15/2

Open in new window

0
 
Koen Van WielinkIT ConsultantCommented:
I would do it like this:

Declare	@period table
(period	nvarchar(12))

Insert into @period
Values		('P1')
		,	('P2')
		,	('P3')
		,	('P4')
		,	('P5')
		,	('P6')
		,	('P7')
		,	('P8')
		,	('P9')
		,	('P10')
		,	('P11')
		,	('P12')
		
		
Insert	into Insert_missing_months

Select	distinct
			a1.account
		,	a1.product
		,	p.period
		,	0
from		Insert_missing_months a1
		, 	@period p
where	not exists
		(select 1
		from	Insert_missing_months a2
		where	a1.Account = a2.Account
		and		a1.Product = a2.Product
		and		p.period = a2.Period)
order by	a1.Account
		,	a1.Product
		,	p.period

Open in new window


This first creates a table variable to hold each of the 12 months. After that it selects each combination of account, product and period which you already have and create a full join against the table variable with the periods. This generates a list of all the possible combinations of account, product and period, This you can compare to the list of periods in the table variable using "not exists". Then it's a matter of inserting those records using the period value from the table variable.
0
 
RayneAuthor Commented:
Thank you both Guys :)
PortletPaul- for your patience in taking you time to explain me the important steps very Cristal clear
Kvwielink - for showing a different approach

Its always a great learning to know the different tricks of the trade.

Thank you :)
I learnt something very new today
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now