Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL GETTING ERROR ON THE OUTPUT RE: GETTING ERROR ON THE OUTPUT

  • January 16, 2017 at 10:25 pm

    #1923102

    Thom A - Monday, January 16, 2017 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;

    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