• ChrisM@Work

    SSC Guru

    Points: 186043

    Here's a mod which works across different products with different numbers of ingredients:

    USE tempdb

    GO

    CREATE TABLE Transactions(

    trnKey INTEGER NOT NULL,

    trnTranDate DATETIME NOT NULL,

    trnProductID INTEGER NOT NULL,

    trnShipRecv CHAR(1) NOT NULL,

    trnTranQty DECIMAL(18,5) NOT NULL,

    trnTranUOM CHAR(2) NOT NULL,

    PRIMARY KEY (trnKey)

    )

    CREATE TABLE Ingredients(

    ingKey INTEGER NOT NULL,

    ingIngrientDesc VARCHAR(20) NOT NULL,

    PRIMARY KEY (ingKey)

    )

    CREATE TABLE Receipts(

    recKey INTEGER NOT NULL,

    recTrnKey INTEGER NOT NULL,

    recIngKey INTEGER NOT NULL,

    recIngPct DECIMAL(18,5) NOT NULL,

    PRIMARY KEY (recKey)

    )

    INSERT INTO Ingredients(ingKey, ingIngrientDesc) SELECT 1,'Ingredient A'

    INSERT INTO Ingredients(ingKey, ingIngrientDesc) SELECT 2,'Ingredient B'

    INSERT INTO Ingredients(ingKey, ingIngrientDesc) SELECT 3,'Ingredient C'

    INSERT INTO Ingredients(ingKey, ingIngrientDesc) SELECT 4,'Ingredient D'

    INSERT INTO Transactions(trnKey, trnTranDate, trnProductID, trnShipRecv, trnTranQty, trnTranUOM)

    SELECT 1, '1/1/2011', 123, 'R', 1000, 'GA' UNION ALL

    SELECT 2, '1/2/2011', 123, 'S', -100, 'GA' UNION ALL

    SELECT 3, '1/2/2011', 123, 'S', -200, 'GA' UNION ALL

    SELECT 4, '1/3/2011', 123, 'R', 500, 'GA' UNION ALL

    SELECT 5, '1/4/2011', 123, 'S', -100, 'GA' UNION ALL

    SELECT 6, '1/1/2011', 124, 'R', 100, 'GA' UNION ALL

    SELECT 7, '1/2/2011', 124, 'S', -50, 'GA' UNION ALL

    SELECT 8, '1/3/2011', 124, 'R', 100, 'GA' UNION ALL

    SELECT 9, '1/4/2011', 124, 'S', -150, 'GA'

    INSERT INTO Receipts(recKey, recTrnKey, recIngKey, recIngPct)

    SELECT 1, 1, 1, 75 UNION ALL

    SELECT 2, 1, 2, 25 UNION ALL

    SELECT 3, 4, 1, 80 UNION ALL

    SELECT 4, 4, 2, 10 UNION ALL

    SELECT 5, 4, 3, 10 UNION ALL

    SELECT 6, 6, 1, 78 UNION ALL

    SELECT 7, 6, 2, 22 UNION ALL

    SELECT 8, 8, 1, 80 UNION ALL

    SELECT 9, 8, 2, 10 UNION ALL

    SELECT 10, 8, 3, 5 UNION ALL

    SELECT 11, 8, 4, 5;

    IF(OBJECT_ID('TempDB..#PreparedData') IS NOT NULL) DROP TABLE #PreparedData;

    -- Prepare a temporary table which has everything we need in place to perform the calculation

    ;WITH ABC AS (

    SELECT

    seq = ROW_NUMBER() OVER(ORDER BY t.trnProductID, t.trnKey, ingIngrientDesc),

    NewKey = DENSE_RANK() OVER (PARTITION BY t.trnProductID ORDER BY t.trnProductID, t.trnTranDate, t.trnShipRecv),

    ingCount = COUNT(*) OVER(PARTITION BY t.trnProductID, t.trnKey),

    t.trnKey,

    t.trnTranDate,

    t.trnProductID,

    t.trnShipRecv,

    t.trnTranQty,

    t.trnTranUOM,

    i.ingIngrientDesc,

    r.recIngPct

    FROM Transactions t

    INNER JOIN (

    SELECT trnProductID, ingKey, ingIngrientDesc

    FROM Transactions t

    INNER JOIN Receipts r ON r.recTrnKey = t.trnKey

    INNER JOIN Ingredients i ON i.ingKey = r.recIngKey

    GROUP BY trnProductID, ingKey, ingIngrientDesc

    ) i ON i.trnProductID = t.trnProductID

    LEFT JOIN Receipts r ON r.recTrnKey = t.trnKey AND i.ingKey = r.recIngKey

    )

    SELECT

    seq,

    NewKey,

    ingCount,

    trnKey,

    trnTranDate,

    trnProductID,

    trnShipRecv,

    trnTranQty,

    trnTranUOM,

    ingIngrientDesc,

    recIngPct = CASE WHEN trnShipRecv = 'R' THEN ISNULL(recIngPct,0) END,

    [Ingr Qty] = CAST(CASE WHEN trnShipRecv = 'R' THEN ISNULL(trnTranQty*recIngPct,0)/100.00000 END AS DECIMAL(18,5)),

    [Ingr Pct] = CAST(CASE WHEN trnShipRecv = 'R' THEN ISNULL(recIngPct,0) END AS DECIMAL(9,5)),

    [Inventory Qty] = CAST(CASE WHEN NewKey = 1 THEN ISNULL(trnTranQty*recIngPct,0)/100.00000 ELSE NULL END AS DECIMAL(18,5)),

    [Inventory Pct] = CAST(CASE WHEN NewKey = 1 THEN ISNULL(recIngPct,0) ELSE NULL END AS DECIMAL(9,5)),

    TotalVolume = CASE WHEN NewKey = 1 THEN trnTranQty ELSE NULL END

    INTO #PreparedData

    FROM ABC

    ORDER BY trnProductID, trnKey, ingIngrientDesc

    -- This index is required for sensible performance

    CREATE UNIQUE CLUSTERED INDEX [CX_seq] ON #PreparedData (seq ASC)

    --SELECT * FROM #PreparedData -- 31 rows

    -- run the calculation: the result is an output set.

    ;WITH Calculator AS (

    SELECT

    seq, NewKey, ingCount, trnKey, trnTranDate, trnProductID,

    trnShipRecv, trnTranQty, trnTranUOM, ingIngrientDesc, recIngPct,

    [Ingr Qty],

    [Ingr Pct],

    [Inventory Qty],

    [Inventory Pct],

    TotalVolume

    FROM #PreparedData

    WHERE trnKey = 1 -- seq IN (1,2,3) -- first 3 rows are first block for this product

    UNION ALL

    SELECT

    tr.seq, tr.NewKey, tr.ingCount, tr.trnKey, tr.trnTranDate, tr.trnProductID,

    tr.trnShipRecv, tr.trnTranQty, tr.trnTranUOM, tr.ingIngrientDesc, tr.recIngPct,

    [Ingr Qty] = CAST(CASE

    WHEN tr.trnShipRecv = 'R' THEN tr.[Ingr Qty]

    WHEN tr.trnShipRecv = 'S' THEN tr.trnTranQty*lr.[Inventory Pct]/100.00000

    END AS DECIMAL(18,5)),

    [Ingr Pct] = CAST(CASE

    WHEN tr.trnShipRecv = 'R' THEN tr.[Ingr Pct]

    WHEN tr.trnShipRecv = 'S' THEN lr.[Inventory Pct]

    END AS DECIMAL(9,5)),

    [Inventory Qty] = CAST(CASE

    WHEN tr.trnShipRecv = 'R' AND tr.NewKey = 1 THEN tr.[Ingr Qty]

    WHEN tr.trnShipRecv = 'R' THEN lr.[Inventory Qty]+tr.[Ingr Qty]

    WHEN tr.trnShipRecv = 'S' THEN lr.[Inventory Qty]+(tr.trnTranQty*lr.[Inventory Pct]/100.00000)

    END AS DECIMAL(18,5)),

    [Inventory Pct] = CAST(CASE

    WHEN tr.trnShipRecv = 'R' AND tr.NewKey = 1 THEN tr.[Inventory Pct]

    WHEN tr.trnShipRecv = 'R' THEN 100.00000*(lr.[Inventory Qty]+tr.[Ingr Qty]) / (tr.trnTranQty+lr.[TotalVolume])

    WHEN tr.trnShipRecv = 'S' THEN lr.[Inventory Pct]

    END AS DECIMAL(9,5)),

    TotalVolume = CAST(CASE

    WHEN tr.trnShipRecv = 'R' AND tr.NewKey = 1 THEN tr.trnTranQty

    ELSE tr.trnTranQty+lr.[TotalVolume]

    END AS DECIMAL(18,5))

    FROM #PreparedData tr -- this set

    INNER JOIN Calculator lr -- last set

    ON lr.seq + tr.ingCount = tr.seq -- n rows at a time, where n = max number of ingredients for this product

    ) SELECT

    [Date] = trnTranDate,

    [Ship/Recv] = trnShipRecv,

    [Product] = trnProductID,

    [Tran Qty] = trnTranQty,

    [UOM] = trnTranUOM,

    [Ingredient]= ingIngrientDesc,

    [Ingr Pct],

    [Ingr Qty],

    [Inventory Qty],

    [Inventory Pct]

    FROM Calculator

    WHERE [Ingr Qty] <> 0

    ORDER BY seq

    -- About 3000 rows/s

    DROP TABLE Receipts

    DROP TABLE Transactions

    DROP TABLE Ingredients

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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