Solved

TSQL loop work

Posted on 2014-03-17
3
406 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 250 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 250 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

726 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