Solved

TSQL loop work

Posted on 2014-03-17
3
404 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
3 Comments
 
LVL 48

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

816 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now