January 16, 2017 at 12:45 pm
My Source has 2 tables and i need to get all the data based on component wise like hierachical data.i am getting output as per the requirement except one column where i am facing issues.
Source1:
SAP IBP
Pegging SKU/Parent no_of_qty RDD Component Avilable_Qty Avlbl_Dte Material_Available_Date
SO1 Notebook1 2 6-Feb-17 Keyboard 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 DisplayPanel 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 PowerCard 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 MotherBoard 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 Ram 4 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 MousePad 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 1TB Seagate 2 6-Feb-17 7-Feb-16
SO1 Notebook1 2 6-Feb-17 Seagate Label 4 7-Feb-17 7-Feb-16
Source2:
SKU Component Qty_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
My Query:
SELECT
ISNULL( t1.Pegging , x.Pegging ) AS Pegging ,
ISNULL( t1.SKU , t2.SKU ) AS SKU ,
ISNULL( t1.QTY , x.QTY * t2. QTY ) AS QTY ,
ISNULL( t1.RDD , x.RDD ) AS RDD ,
ISNULL( t1.Component , t2.Component ) AS Component ,
ISNULL( t1.[Available 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;
from this i am getting output as :
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 1TB Seagate 2 6-Feb-17 7-Feb-2017
SO1 HDD1 2 6-Feb-17 Seagate Label 4 6-Feb-17 7-Feb-2017
Here everythis is correct but the Available date for the last record should come as 7-FEB-17 but its coming as 6-FEB-17 as in source 1the avalable_date for Seagate Label is 7-FEB-17 but query picking 6-FEB-17
January 16, 2017 at 1:28 pm
This is something you were asked to do last time, I have done it for you this time, but PLEASE provide CREATE and INSERT statements when asking questions. Considering that your sample data's columns don't match the column names in your query, this took me a few attempts:USE TestDB;
GO
CREATE TABLE SAP_IBP (Pegging VARCHAR(3),
SKU VARCHAR(20),
Qty INT,
RDD DATE,
Component VARCHAR(20),
[Available Qty] INT,
[Available Date] DATE,
[Material Available Date] DATE);
GO
CREATE TABLE BOM (SKU VARCHAR(20),
Component VARCHAR(20),
Qty INT);
GO
INSERT INTO SAP_IBP
VALUES('SO1','Notebook1',2,'6-Feb-17','Keyboard',2,'6-Feb-17','7-Feb-16'),
('SO1','Notebook1',2,'6-Feb-17','DisplayPanel',2,'6-Feb-17','7-Feb-16'),
('SO1','Notebook1',2,'6-Feb-17','PowerCard',2,'6-Feb-17','7-Feb-16'),
('SO1','Notebook1',2,'6-Feb-17','MotherBoard',2,'6-Feb-17','7-Feb-16'),
('SO1','Notebook1',2,'6-Feb-17','Ram',4,'6-Feb-17','7-Feb-16'),
('SO1','Notebook1',2,'6-Feb-17','MousePad',2,'6-Feb-17','7-Feb-16'),
('SO1','Notebook1',2,'6-Feb-17','1TB Seagate',2,'6-Feb-17','7-Feb-16'),
('SO1','Notebook1',2,'6-Feb-17','Seagate Label',4,'7-Feb-17','7-Feb-16');
GO
INSERT INTO BOM
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);
SELECT ISNULL(t1.Pegging,x.Pegging) AS Pegging,
ISNULL(t1.SKU,t2.SKU) AS SKU,
ISNULL(t1.QTY,x.QTY*t2.QTY) AS QTY,
ISNULL(t1.RDD,x.RDD) AS RDD,
ISNULL(t1.Component,t2.Component) AS Component,
ISNULL(t1.[Available 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) x;
GO
DROP TABLE SAP_IBP;
DROP TABLE BOM;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 16, 2017 at 1:52 pm
January 16, 2017 at 10:25 pm
Thom A - Monday, January 16, 2017 1:28 PMThis is something you were asked to do last time, I have done it for you this time, but PLEASE provide CREATE and INSERT statements when asking questions. Considering that your sample data's columns don't match the column names in your query, this took me a few attempts:USE TestDB;
GOCREATE TABLE SAP_IBP (Pegging VARCHAR(3),
SKU VARCHAR(20),
Qty INT,
RDD DATE,
Component VARCHAR(20),
[Available Qty] INT,
[Available Date] DATE,
[Material Available Date] DATE);
GOCREATE TABLE BOM (SKU VARCHAR(20),
Component VARCHAR(20),
Qty INT);
GOINSERT INTO SAP_IBP
VALUES('SO1','Notebook1',2,'6-Feb-17','Keyboard',2,'6-Feb-17','7-Feb-16'),
('SO1','Notebook1',2,'6-Feb-17','DisplayPanel',2,'6-Feb-17','7-Feb-16'),
('SO1','Notebook1',2,'6-Feb-17','PowerCard',2,'6-Feb-17','7-Feb-16'),
('SO1','Notebook1',2,'6-Feb-17','MotherBoard',2,'6-Feb-17','7-Feb-16'),
('SO1','Notebook1',2,'6-Feb-17','Ram',4,'6-Feb-17','7-Feb-16'),
('SO1','Notebook1',2,'6-Feb-17','MousePad',2,'6-Feb-17','7-Feb-16'),
('SO1','Notebook1',2,'6-Feb-17','1TB Seagate',2,'6-Feb-17','7-Feb-16'),
('SO1','Notebook1',2,'6-Feb-17','Seagate Label',4,'7-Feb-17','7-Feb-16');
GOINSERT INTO BOM
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);SELECT ISNULL(t1.Pegging,x.Pegging) AS Pegging,
ISNULL(t1.SKU,t2.SKU) AS SKU,
ISNULL(t1.QTY,x.QTY*t2.QTY) AS QTY,
ISNULL(t1.RDD,x.RDD) AS RDD,
ISNULL(t1.Component,t2.Component) AS Component,
ISNULL(t1.[Available 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) x;
GODROP TABLE SAP_IBP;
DROP TABLE BOM;
i have tried by combining both the output and it's not giving the output...if i do Union i am getting duplicates and the value not getting changed....
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 Material Available Date
SO1 HDD1 2 6-Feb-17 1TB Seagate 2 6-Feb-17 7-Feb-2017
SO1 HDD1 2 6-Feb-17 Seagate Label 4 6-Feb-17 7-Feb-2017
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 --------------not required
NOTE: In this we are getting component 1TB Seagate and Seagate Label twice and SKU=HDD1 and component=Seagate Label where the Available Date has to be 7-FEB-17 as in SAP table it is 7-FEB-17
January 17, 2017 at 1:59 am
You're not using the CTE as it was intended. it''s there to build you a real table you can do a proper join on, as your data is truly relational.
I'm giving you the answer, however, I suggest you have a look at what it does and try to understand the how and why. You're understanding of SQL will go up leaps and bounds:WITH rCTE AS(
SELECT t2.SKU AS MainSKU,
*
FROM BOM t2
WHERE SKU NOT IN (SELECT i.Component FROM BOM i)
UNION ALL
SELECT r.MainSKU, t2.*
FROM rCTE r
JOIN BOM t2 ON r.Component = t2.SKU
)
SELECT ISNULL(t1.Pegging,x.Pegging) AS Pegging,
ISNULL(t1.SKU,t2.SKU) AS SKU,
ISNULL(t1.QTY,x.QTY*t2.QTY) AS QTY,
ISNULL(t1.RDD,x.RDD) AS RDD,
ISNULL(t1.Component,t2.Component) AS Component,
ISNULL(t1.[Available 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 rCTE t2 ON t1.SKU = t2.MainSKU AND t1.Component = t2.Component
CROSS APPLY (SELECT TOP 1 *
FROM SAP_IBP) x;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 20, 2017 at 8:36 am
Thom A - Tuesday, January 17, 2017 1:59 AMYou're not using the CTE as it was intended. it''s there to build you a real table you can do a proper join on, as your data is truly relational.I'm giving you the answer, however, I suggest you have a look at what it does and try to understand the how and why. You're understanding of SQL will go up leaps and bounds:
WITH rCTE AS(
SELECT t2.SKU AS MainSKU,
*
FROM BOM t2
WHERE SKU NOT IN (SELECT i.Component FROM BOM i)
UNION ALL
SELECT r.MainSKU, t2.*
FROM rCTE r
JOIN BOM t2 ON r.Component = t2.SKU
)
SELECT ISNULL(t1.Pegging,x.Pegging) AS Pegging,
ISNULL(t1.SKU,t2.SKU) AS SKU,
ISNULL(t1.QTY,x.QTY*t2.QTY) AS QTY,
ISNULL(t1.RDD,x.RDD) AS RDD,
ISNULL(t1.Component,t2.Component) AS Component,
ISNULL(t1.[Available 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 rCTE t2 ON t1.SKU = t2.MainSKU AND t1.Component = t2.Component
CROSS APPLY (SELECT TOP 1 *
FROM SAP_IBP) x;
Thanks Thom for the solution but this is not working while we have multiple Pegging Values.This is giving Wrong Output as in the BOM table SKU and component has interrelated and have to get one records.i am giving all the DDL with required output here.Please help to get the required output.
i have attached the file which has all the components and the required output.Query used also attached.
January 20, 2017 at 11:31 am
skmoh2 - Friday, January 20, 2017 8:36 AMThanks Thom for the solution but this is not working while we have multiple Pegging Values.This is giving Wrong Output as in the BOM table SKU and component has interrelated and have to get one records.i am giving all the DDL with required output here.Please help to get the required output.i have attached the file which has all the components and the required output.Query used also attached.
You clearly didn't test your DLM, or you'd of noticed it doesn't work. None of your lines have comma's on your INSERT. I've fixed it my end, but I siggest you fix it yourself too, so other users don't have to.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 20, 2017 at 11:44 am
OK, I've looked at your data, you have a line in your output for SO4, Notebook3. Notebook 3 doesn't exist in BOM. it will never return as BOM is your root table (I don't know why you're still using that RIGHT JOIN, you should really do it the other way round and use a LEFT).
You keep on changing the goal here, so what are we missing? Everything worked for the picture you gave us. Instead we find out we were given one part of the jigsaw and that there's a much bigger picture, which we had no idea existed. Of course things fall over.
1. Supply a COMPLETE and WORKING DDL and DLM statement.
2. Let us know your full goal. Part of that means supplying a full DLM for all your scenarios.
3. Your root table is your BOM table, yet you expect to have data from a different table, if that data isn't in the BOM. Should we be using a FULL OUTER JOIN now?
4. Have a good think, and explain your logic again. It seems that your prior explanations have missed bits, so maybe we should start again, and see what else comes out of the woodwork, so that we don't have to keep coming back because you "forgot to mention this" or tell us "It doesn't work for this scenario I didn't tell you about".
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply