Hi ,how to code the below procedure with loop.

Question

Hi ,how to code the below procedure with loop.

skmoh2

SSChasing Mays

Points: 625
More actions
January 10, 2017 at 4:57 am

#316818

i have two tables.
Table1
--------
PeggingSKU QTY RDD ComponentAvilable QtyAvlbl Dte Material Available Date
SO1 Notebook1 2 6-Feb-17 Keyboard 2 6-Feb-177-Feb-16
SO1 Notebook1 2 6-Feb-17 DisplayPanel 2 6-Feb-177-Feb-16
SO1 Notebook1 2 6-Feb-17 PowerCard 2 6-Feb-177-Feb-16
SO1 Notebook1 2 6-Feb-17 MotherBoard 2 6-Feb-177-Feb-16
SO1 Notebook1 2 6-Feb-17 Ram 4 6-Feb-177-Feb-16
SO1 Notebook1 2 6-Feb-17 MousePad 2 6-Feb-177-Feb-16
SO1 Notebook1 2 6-Feb-17 1TB Seagate 2 6-Feb-177-Feb-16
SO1 Notebook1 2 6-Feb-17 Seagate Label 4 7-Feb-177-Feb-16
Table2:
-----------
SKU ComponentQty Per
Notebook1 Keyboard 1
Notebook1 DisplayPanel 1
Notebook1 PowerCard 1
Notebook1 MotherBoard 1
Notebook1 Ram 2
Notebook1 HDD1 1
Notebook1 MousePad 1
HDD1 1TB Seagate 1
HDD1 Seagate Label 2
OUTPUT
-------------
PeggingSKU QTYRDD Component Avilable QtyAvlbl Dte Material Available Date
SO1 Notebook126-Feb-17Keyboard 2 6-Feb-177-Feb-16
SO1 Notebook126-Feb-17DisplayPanel 2 6-Feb-177-Feb-16
SO1 Notebook126-Feb-17PowerCard 2 6-Feb-177-Feb-16
SO1 Notebook126-Feb-17MotherBoard 2 6-Feb-177-Feb-16
SO1 Notebook126-Feb-17Ram 4 6-Feb-177-Feb-16
SO1 Notebook126-Feb-17MousePad 2 6-Feb-177-Feb-16
SO1 Notebook126-Feb-17HDD1 2 6-Feb-177-Feb-16
SO1 HDD1 26-Feb-171TB Seagate 2 6-Feb-177-Feb-16
SO1 HDD1 26-Feb-17Seagate Label 4 7-Feb-177-Feb-16
PSEDOCODE:
Loop1 : Table1_Peggging read Sales Order Material(SKU) Per sales Order
Loop2: Read full table2 for above Material. (Based on our example data you will get 7 records)
Loop3: Based on Loop2 Component, read Table2 SKU level table2.
Loop 4: If any data exists in Loop3 then
insert into output table.
Loop 4 End.
Loop3 End
Insert Loop2 data into output table.
Loop2 End
Loop 1 End

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

Welcome to SSC, however, have you actually asked a question in your above post?

Also, can you supply DDL and DLM statements for your tables. At first glance you have more headers than you do data columns. See table layout below:

Pegging SKU QTY RDD Component Avilable Qty Avlbl Dte Material Available Date

SO1 Notebook1 2 6-Feb-17 Keyboard 2 6-Feb-17 7-Feb-16

Where are the values for Dte, Material, Available and Date. Is Avilable spelt incorrectly the first time to avoid duplication of column names, yet the column QTY exists twice? If you have case sensivitivity enabled, having two columns with the same name with different cases is very bad practice.

Have a look at the link in my signature on how to post questions on the SSC forum.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

skmoh2 (1/10/2017)
...PSEDOCODE:
Loop1 : Table1_Peggging read Sales Order Material(SKU) Per sales Order
Loop2: Read full table2 for above Material. (Based on our example data you will get 7 records)
Loop3: Based on Loop2 Component, read Table2 SKU level table2.
Loop 4: If any data exists in Loop3 then
insert into output table.
Loop 4 End.
Loop3 End
Insert Loop2 data into output table.
Loop2 End
Loop 1 End

With a loop? Three loops even? Wouldn't you rather learn how to code this up properly? I hope this isn't coursework, because it's bloody hopeless - my kitten could write better questions.

Follow Thom's advice and you'll get some great insight into how TSQL should be written.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

skmoh2 (1/10/2017)
i have two tables.
Table1
--------
PeggingSKU QTY RDD ComponentAvilable QtyAvlbl Dte Material Available Date
SO1 Notebook1 2 6-Feb-17 Keyboard 2 6-Feb-177-Feb-16
SO1 Notebook1 2 6-Feb-17 DisplayPanel 2 6-Feb-177-Feb-16
SO1 Notebook1 2 6-Feb-17 PowerCard 2 6-Feb-177-Feb-16
SO1 Notebook1 2 6-Feb-17 MotherBoard 2 6-Feb-177-Feb-16
SO1 Notebook1 2 6-Feb-17 Ram 4 6-Feb-177-Feb-16
SO1 Notebook1 2 6-Feb-17 MousePad 2 6-Feb-177-Feb-16
SO1 Notebook1 2 6-Feb-17 1TB Seagate 2 6-Feb-177-Feb-16
SO1 Notebook1 2 6-Feb-17 Seagate Label 4 7-Feb-177-Feb-16
Table2:
-----------
SKU ComponentQty Per
Notebook1 Keyboard 1
Notebook1 DisplayPanel 1
Notebook1 PowerCard 1
Notebook1 MotherBoard 1
Notebook1 Ram 2
Notebook1 HDD1 1
Notebook1 MousePad 1
HDD1 1TB Seagate 1
HDD1 Seagate Label 2
OUTPUT
-------------
PeggingSKU QTYRDD Component Avilable QtyAvlbl Dte Material Available Date
SO1 Notebook126-Feb-17Keyboard 2 6-Feb-177-Feb-16
SO1 Notebook126-Feb-17DisplayPanel 2 6-Feb-177-Feb-16
SO1 Notebook126-Feb-17PowerCard 2 6-Feb-177-Feb-16
SO1 Notebook126-Feb-17MotherBoard 2 6-Feb-177-Feb-16
SO1 Notebook126-Feb-17Ram 4 6-Feb-177-Feb-16
SO1 Notebook126-Feb-17MousePad 2 6-Feb-177-Feb-16
SO1 Notebook126-Feb-17HDD1 2 6-Feb-177-Feb-16
SO1 HDD1 26-Feb-171TB Seagate 2 6-Feb-177-Feb-16
SO1 HDD1 26-Feb-17Seagate Label 4 7-Feb-177-Feb-16
PSEDOCODE:
Loop1 : Table1_Peggging read Sales Order Material(SKU) Per sales Order
Loop2: Read full table2 for above Material. (Based on our example data you will get 7 records)
Loop3: Based on Loop2 Component, read Table2 SKU level table2.
Loop 4: If any data exists in Loop3 then
insert into output table.
Loop 4 End.
Loop3 End
Insert Loop2 data into output table.
Loop2 End
Loop 1 End

First of all, you have to stop worrying about how to code loops. SQL Server uses a declarative language, so you don't need to tell it how to process data, you just need to tell what you need to do. You need to think about sets of data instead of arrays.

Next, you're trying to guess data. You're adding a row from Table2 but you don't have all the information. You don't have a way to relate both tables consistently. Sometimes it uses SKU and sometimes it uses Component. This is probably due to a normalization (design) problem.

Last, I think I can help you, but you need to do some additional work. Follow my example and post your Table1 the same way I'm posting your Table2.

CREATE TABLE Table2(

SKU varchar(20),

Component varchar(100),

Qty_Per int

);

INSERT INTO Table2

VALUES

('Notebook1', 'Keyboard ', 1),

('Notebook1', 'DisplayPanel ', 1),

('Notebook1', 'PowerCard ', 1),

('Notebook1', 'MotherBoard ', 1),

('Notebook1', 'Ram ', 2),

('Notebook1', 'HDD1 ', 1),

('Notebook1', 'MousePad ', 1),

('HDD1 ', '1TB Seagate ', 1),

('HDD1 ', 'Seagate Label', 2);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Thom A (1/10/2017)
Welcome to SSC, however, have you actually asked a question in your above post?
Also, can you supply DDL and DLM statements for your tables. At first glance you have more headers than you do data columns. See table layout below:
Pegging SKU QTY RDD Component Avilable Qty Avlbl Dte Material Available Date
SO1 Notebook1 2 6-Feb-17 Keyboard 2 6-Feb-17 7-Feb-16
Where are the values for Dte, Material, Available and Date. Is Avilable spelt incorrectly the first time to avoid duplication of column names, yet the column QTY exists twice? If you have case sensivitivity enabled, having two columns with the same name with different cases is very bad practice.
Have a look at the link in my signature on how to post questions on the SSC forum.

Here's a guess on what his columns really are.

Pegging

SKU

QTY

RDD

Component

Avilable_Qty

Avlbl_Dte

Material_Available_Date

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Thom A SSC Guru Points: 99130 More actions · Answer 5

Luis Cazares (1/10/2017)
Here's a guess on what his columns really are.
Pegging
SKU
QTY
RDD
Component
Avilable_Qty
Avlbl_Dte
Material_Available_Date

Makes more sense. Having column names with white space in and no column delimiter in your sample data? Not the best of ideas >_<

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

skmoh2 SSChasing Mays Points: 625 More actions · Answer 6

Here is the Table1 Structure.

CREATE TABLE Table1(

Pegging varchar(20),

SKU VARCHAR(20),

QTY INT,

RDD DATE,

Component varchar(100),

Avilable_Qty INT,

Avlbl_Dte DATE,

Material_Available_Date DATE

);

INSERT INTO Table1 VALUES

('SO1','Notebook1', 2,'2017-02-06','Keyboard',2,'2017-02-06','2017-02-07'),

('SO1','Notebook1',2,'2017-02-06','DisplayPanel',2,'2017-02-06','2017-02-07'),

('SO1','Notebook1',2,'2017-02-06','PowerCard',2,'2017-02-06','2017-02-07'),

('SO1','Notebook1',2,'2017-02-06','MotherBoard',2,'2017-02-06','2017-02-07'),

('SO1','Notebook1',2,'2017-02-06','Ram',4,'2017-02-06','2017-02-07'),

('SO1','Notebook1',2,'2017-02-06','MousePad',2,'2017-02-06','2017-02-07'),

('SO1','Notebook1',2,'2017-02-06','1TB Seagate',2,'2017-02-06','2017-02-07')

('SO1','Notebook1',2,'2017-02-06','Seagate Label',4,'2017-02-07','2017-02-07');

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

OK, let's take it slowly.

You have Table1 which is your orders table. You need to be sure you have all the rows needed as defined in your components table which is Table2. To be able to get that, you need an OUTER JOIN (not a loop). OUTER JOINs can be LEFT, RIGHT or FULL. The most common that you'll find is LEFT. For further information, you can read the following:

https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

However, in this case you need to get more information from Table1 to fill the rows to be added from Table2. For this, we'll just take a sample row of Table1. Note that this is missing a way to get the exact Order needed.

SELECT

ISNULL( t1.Pegging , x.Pegging ) AS Pegging ,

ISNULL( t1.SKU , t2.SKU ) AS SKU ,

ISNULL( t1.QTY , x.QTY * t2.Qty_Per ) AS QTY ,

ISNULL( t1.RDD , x.RDD ) AS RDD ,

ISNULL( t1.Component , t2.Component ) AS Component ,

ISNULL( t1.Avilable_Qty , x.Avilable_Qty ) AS Avilable_Qty ,

ISNULL( t1.Avlbl_Dte , x.Avlbl_Dte ) AS Avlbl_Dte ,

ISNULL( t1.Material_Available_Date, x.Material_Available_Date) AS Material_Available_Date

FROM Table1 t1

RIGHT JOIN Table2 t2 ON t1.SKU = t2.SKU AND t1.Component = t2.Component

CROSS APPLY (SELECT TOP 1 *

FROM Table1 i

--WHERE i.Pegging = @Pegging

) x;

Here, we get 7 rows which are the rows where the SKU matches in both tables. I figured that Table2 is some sort of hierarchy in which Components can become SKU with more Components. To work with these structures, a common option is to use a recursive CTE. This way, we can find out which is the root SKU and assign it to all dependent components.

WITH rCTE AS(

SELECT t2.SKU AS MainSKU,

*

FROM Table2 t2

WHERE SKU NOT IN (SELECT i.Component FROM Table2 i)

UNION ALL

SELECT r.MainSKU, t2.*

FROM rCTE r

JOIN Table2 t2 ON r.Component = t2.SKU

)

SELECT *

FROM rCTE;

Now, you would just need to use both options together and you'll get the 9 rows as expected, joining by the root SKU and component. If you understand how things work, it should be easy. If not, you might want to ask more questions or do some additional research.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

skmoh2 SSChasing Mays Points: 625 More actions · Answer 8

Thanks Luis for this.i have done this scenario with SQL and is working fine.But i have to implement it using procedure/function with LOOP .As may be tomorrow there will be more sales orders with same component.

Like Exp

Table1

SO2Notebook126-Feb-17Keyboard26-Feb-177-Feb-16

Thom A SSC Guru Points: 99130 More actions · Answer 9

skmoh2 (1/10/2017)
Thanks Luis for this.i have done this scenario with SQL and is working fine.But i have to implement it using procedure/function with LOOP .As may be tomorrow there will be more sales orders with same component.
Like Exp
Table1
SO2Notebook126-Feb-17Keyboard26-Feb-177-Feb-16

Data within tables is always changing, that's the point. If you have more data in your table, then your query will bring back the extra data. A query you ran yesterday, won't return the same result set today, if the data is different. It will represent the new data.

Using a LOOP structure is a programming language solution, which SQL is not.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

skmoh2 (1/10/2017)
Thanks Luis for this.i have done this scenario with SQL and is working fine.

Can you share that code with us?

But i have to implement it using procedure/function with LOOP .As may be tomorrow there will be more sales orders with same component.
Like Exp
Table1
SO2Notebook126-Feb-17Keyboard26-Feb-177-Feb-16

You'd just need to code correctly to handle multiple sales orders withing the same table and query. As I previously mentioned, this is more problematic due to a bad design in your tables.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

skmoh2 SSChasing Mays Points: 625 More actions · Answer 11

Luis Cazares - Tuesday, January 10, 2017 9:43 AM
skmoh2 (1/10/2017)
Thanks Luis for this.i have done this scenario with SQL and is working fine.
Can you share that code with us?
But i have to implement it using procedure/function with LOOP .As may be tomorrow there will be more sales orders with same component.Like ExpTable1SO2Notebook126-Feb-17Keyboard26-Feb-177-Feb-16
You'd just need to code correctly to handle multiple sales orders withing the same table and query. As I previously mentioned, this is more problematic due to a bad design in your tables.

Hi Luis,

i ran the code with CTE clause and it is working but it is missing last two records which is having sku hdd1.And last two columns must be like. HDD! is the

SO1	HDD1	2	6-Feb-17	1TB Seagate	2	6-Feb-17	7-Feb-16
SO1	HDD1	2	6-Feb-17	Seagate Label	4	7-Feb-17	7-Feb-16

Luis Cazares - Tuesday, January 10, 2017 9:43 AM
skmoh2 (1/10/2017)
Thanks Luis for this.i have done this scenario with SQL and is working fine.
Can you share that code with us?
But i have to implement it using procedure/function with LOOP .As may be tomorrow there will be more sales orders with same component.Like ExpTable1SO2Notebook126-Feb-17Keyboard26-Feb-177-Feb-16
You'd just need to code correctly to handle multiple sales orders withing the same table and query. As I previously mentioned, this is more problematic due to a bad design in your tables.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

skmoh2 - Thursday, January 12, 2017 4:19 AM
Hi Luis,
i ran the code with CTE clause and it is working but it is missing last two records which is having sku hdd1.And last two columns must be like. HDD! is the
SO1 HDD1 2 6-Feb-17 1TB Seagate 2 6-Feb-17 7-Feb-16
SO1 HDD1 2 6-Feb-17 Seagate Label 4 7-Feb-17 7-Feb-16

I'll just repeat what's at the end of my post. Apparently you didn't read it.

Luis Cazares - Tuesday, January 10, 2017 8:51 AM
Now, you would just need to use both options together and you'll get the 9 rows as expected, joining by the root SKU and component. If you understand how things work, it should be easy. If not, you might want to ask more questions or do some additional research.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

skmoh2 SSChasing Mays Points: 625 More actions · Answer 13

Luis Cazares - Thursday, January 12, 2017 6:33 AM
skmoh2 - Thursday, January 12, 2017 4:19 AM
Hi Luis,
i ran the code with CTE clause and it is working but it is missing last two records which is having sku hdd1.And last two columns must be like. HDD! is the
SO1 HDD1 2 6-Feb-17 1TB Seagate 2 6-Feb-17 7-Feb-16
SO1 HDD1 2 6-Feb-17 Seagate Label 4 7-Feb-17 7-Feb-16
I'll just repeat what's at the end of my post. Apparently you didn't read it.
Luis Cazares - Tuesday, January 10, 2017 8:51 AM
Now, you would just need to use both options together and you'll get the 9 rows as expected, joining by the root SKU and component. If you understand how things work, it should be easy. If not, you might want to ask more questions or do some additional research.

Hi Luis,

The code is working.....But even if i combine both the options its generating Duplicates.However i am getting all the required values except 2 column values from Option 1.

STEP-1
SELECT

ISNULL( t1.Pegging , x.Pegging ) AS Pegging ,

ISNULL( t1.SKU , t2.SKU ) AS SKU ,

ISNULL( t1.QTY , x.QTY ) AS QTY ,

ISNULL( t1.RDD , x.RDD ) AS RDD ,

ISNULL( t1.Component , t2.Component ) AS Component ,

ISNULL( t1.[Available Qty] , t2. QTY *x.[Available Qty] ) AS Avilable_Qty ,

ISNULL( t1.[Available Date] , x.[Available Date] ) AS Avlbl_Dte ,

ISNULL( t1.[Material Available Date], x.[Material Available Date]) AS Material_Available_Date

FROM SAP_IBP t1

RIGHT JOIN BOM t2 ON t1.SKU = t2.SKU AND t1.Component = t2.Component

CROSS APPLY (SELECT TOP 1 *

FROM SAP_IBP i

) x;

Output:

Pegging SKU QTY RDD Component Avilable_Qty Avlbl_Dte Material_Available_Date

SO1 Notebook1 2 6-Feb-17 Keyboard 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 DisplayPanel 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 PowerCard 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 MotherBoard 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 Ram 4 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 HDD1 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 MousePad 2 6-Feb-17 7-Feb-2017

SO1 HDD1 2 6-Feb-17 1TBSeagate 2 6-Feb-17 7-Feb-2017

SO1 HDD1 2 6-Feb-17 SeagateLabel 4 6-Feb-17 7-Feb-2017

Note : in the above output only need to get forSeagate lable avlbl dte iscoming 6-feb-17 but actually we have to get 7-Feb-2017.

STEP-2
when combining both the option and option 2 we are getting extra records .We are getting 11 instead of 9.

WITH rCTE AS(

SELECT--t2.SKU AS MainSKU,

*

FROM SAP_IBP t2

WHERE SKU NOT IN (SELECT i.Component FROM SAP_IBP i)

UNION ALL

SELECT--r.MainSKU,

t2.*

FROM rCTE r

JOIN SAP_IBP t2 ON r.Component = t2.SKU

)

SELECT *

FROM rCTE

union

SELECT

ISNULL( t1.Pegging , x.Pegging ) AS Pegging ,

ISNULL( t1.SKU , t2.SKU ) AS SKU ,

ISNULL( t1.QTY , x.QTY ) AS QTY ,

ISNULL( t1.RDD , x.RDD ) AS RDD ,

ISNULL( t1.Component , t2.Component ) AS Component ,

ISNULL( t1.[Available Qty] , t2. QTY *x.[Available Qty] ) AS Avilable_Qty ,

ISNULL( t1.[Available Date] , x.[Available Date] ) AS Avlbl_Dte ,

ISNULL( t1.[Material Available Date], x.[Material Available Date]) AS Material_Available_Date

FROM SAP_IBP t1

RIGHT JOIN BOM t2 ON t1.SKU = t2.SKU AND t1.Component = t2.Component

CROSS APPLY (SELECT TOP 1 *

FROM SAP_IBP i

) x;

OUTPUT:

Pegging SKU QTY RDD Component Available Qty Available Date MaterialAvailable Date

SO1 HDD1 2 6-Feb-17 1TBSeagate 2 6-Feb-17 7-Feb-2017

SO1 HDD1 2 6-Feb-17 SeagateLabel 4 6-Feb-17 7-Feb-2017 -----Not required

SO1 Notebook1 2 6-Feb-17 1TB Seagate 2 6-Feb-17 7-Feb-2017 -----Not required

SO1 Notebook1 2 6-Feb-17 DisplayPanel 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 HDD1 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 Keyboard 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 MotherBoard 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 MousePad 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 PowerCard 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 Ram 4 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 Seagate Label 4 7-Feb-17 7-Feb-2017

NOTE: In this we are getting component ITB Segate andSegate Label twice.

STEP:3
------------

WITH rCTE AS(

SELECT--t2.SKU AS MainSKU,

*

FROM SAP_IBP t2

WHERE SKU NOT IN (SELECT i.Component FROM SAP_IBP i)

UNION ALL

SELECT--r.MainSKU,

t2.*

FROM rCTE r

JOIN SAP_IBP t2 ON r.Component = t2.SKU

)

SELECT *

FROM rCTE

Output:

Pegging SKU QTY RDD Component Available Qty Available Date MaterialAvailable Date

SO1 Notebook1 2 6-Feb-17 Keyboard 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 DisplayPanel 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 PowerCard 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 MotherBoard 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 Ram 4 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 MousePad 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 1TB Seagate 2 6-Feb-17 7-Feb-2017

SO1 Notebook1 2 6-Feb-17 Seagate Label 4 7-Feb-17 7-Feb-2017

Note: In this output we are getting Notebook1 for component1TB segate and Segate Label as it is needed actually HDD1

Could you please check with Step 1 and if we can modify little bit we should get the avaliable date correctly .That code is working nicely but not getting the avalaible date correctly.