[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

TSQL loop work

Posted on 2014-03-17
3
Medium Priority
?
409 Views
Last Modified: 2014-03-18
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
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1000 total points
ID: 39935854
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
 
LVL 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 1000 total points
ID: 39935873
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
 

Author Comment

by:Rayne
ID: 39936364
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question