?
Solved

TSQL loop work

Posted on 2014-03-17
3
Medium Priority
?
407 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

771 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