Here is the sql I'm using for my testing, JLS. Let me know if this answers your question:
TRUNCATE TABLE testLotTrans
-- GET LOT TRANSACTIONS FOR EACH PudID
INSERT INTO testLotTrans
SELECTDISTINCT
cmpPudIDtmpPudID,
wkhHdrIDtmpHdrID,
IntKeytmpIntKey,
IntPodKeytmpPodKey,
IntLotKeytmpLotKey,
IntTranDatetmpTranDate,
IntTranQtytmpTranQty
FROMInventoryTran
INNER JOIN
ProdOrdDtl ON PodKey = IntPodKey
INNER JOIN
Operation ON OprKey = PodOprKey
INNER JOIN
DataWhse.dbo.WorkHdr ON wkhLotKey = IntLotKey AND
wkhLocKey = IntLocKey
INNER JOIN
(SELECT DISTINCT
cmpHdrID,
cmpPudID,
cmpPodKey
FROMtestWkpCompQtys) CMP ON CMP.cmpHdrID = wkhHdrID AND
CMP.cmpPodKey = IntPodKey
INNER JOIN
PurchOrderDtl ON PudID = CmpPudID
INNER JOIN
Item PURCHITEM ON PURCHITEM.ItmItemCode = PudItemID
WHEREOprType NOT IN ('BOTTLE','SHIP','RECEIVE') AND-- BECAUSE WE WANT TO KNOW THE TOTAL GALLONS PRODUCED FROM THE TONS EXCLUDING SHIPMENTS/BOTTLING/RECEIVING
IntTranQty <> 0 AND
IntTranUOM = 'GA' AND
PURCHITEM.ItmType <> 'B' AND-- DON'T INCLUDE PURCHASED BULK JUICE OR WINE FOR CALCULATING YIELDS
cmpPudID <> 0
ORDER BY IntTranDate
-- GET LOT TRANSACTIONS THAT HAPPENED PRIOR TO THE PUDID COMING INTO THE PICTURE (IF ANY)
INSERT INTO testLotTrans
SELECTDISTINCT
RB.tmpPudIDtmpPudID,
WKH.wkhHdrIDtmpHdrID,
IT.IntKeytmpIntKey,
IT.IntPodKeytmpPodKey,
IT.IntLotKeytmpLotKey,
IT.IntTranDatetmpTranDate,
IT.IntTranQtytmpTranQty
FROMtestLotTrans RB
INNER JOIN
InventoryTran IT ON IT.IntLotKey = RB.tmpLotKey
LEFT JOIN
testLotTrans RB2 ON RB2.tmpPudID = RB.tmpPudID AND
RB2.tmpIntKey = IT.IntKey
INNER JOIN
ProdOrdDtl ON PodKey = IT.IntPodKey
INNER JOIN
Operation ON OprKey = PodOprKey
INNER JOIN
DataWhse.dbo.WorkHdr WKH ON WKH.wkhLotKey = IT.IntLotKey AND
WKH.wkhLocKey = IT.IntLocKey
INNER JOIN
PurchOrderDtl ON PudID = RB.tmpPudID
INNER JOIN
Item PURCHITEM ON PURCHITEM.ItmItemCode = PudItemID
WHEREOprType NOT IN ('BOTTLE','SHIP','RECEIVE') AND-- BECAUSE WE WANT TO KNOW THE TOTAL GALLONS PRODUCED FROM THE TONS DISREGARDING SHIPMENTS/BOTTLING/RECEIVING
IT.IntTranQty <> 0 AND
IT.IntTranUOM = 'GA' AND
PURCHITEM.ItmType <> 'B' AND-- DON'T INCLUDE PURCHASED BULK JUICE OR WINE FOR CALCULATING YIELDS
RB.tmpPudID <> 0 AND
RB2.tmpIntKey IS NULL -- ONLY GET ROWS THAT DON'T EXIST YET IN THE testLotTrans TABLE
ORDER BY IT.IntTranDate
--SELECT RESULTS
SELECT AsOfPudID
, AsOfTranDate
, AsOfTranQty
, AsOfLotKey
, AsOfLotBalSeq
, tmpLotRunBal
, ISNULL(AsOfPudIDPct,0)AsOfPudIDPct
, CAST( AsOfTranQty * AsOfPudIDPct / 100 as INT) AS jlsqty
, CAST (SUM ( AsOfTranQty * AsOfPudIDPct / 100 ) OVER (PARTITION BY AsOfPudID ORDER BY AsOfPudID , AsOfTranDate , AsOfLotKey , AsOfLotBalSeq ) AS INT) AS jlstot
, CAST (SUM ( AsOfTranQty * AsOfPudIDPct / 100 ) OVER (PARTITION BY AsOfPudID ORDER BY AsOfPudID , AsOfTranDate ) AS INT) AS jlstot2
, ROUND(ISNULL(tmpLotRunBal * AsOfPudIDPct / 100, 0),0)AsOfPudIDLotBal
FROM
(SELECTWORK.tmpPudIDAsOfPudID,
WORK.tmpTranDateAsOfTranDate,
WORK.tmpTranQtyAsOfTranQty,
WORK.tmpLotKeyAsOfLotKey,
CQ.cmpCompQty / CT.totTotQty * 100AsOfPudIDPct,
tmpLotRunBal,
WORK.tmpLotRunBal * CQ.cmpCompQty / CT.totTotQtyAsOfLotBal,
ROW_NUMBER() OVER
(PARTITION BY WORK.tmpPudID, WORK.tmpLotKey
ORDER BY WORK.tmpTranDate)AsOfLotBalSeq
FROM
(SELECTtmpPudID,
tmpHdrID,
tmpIntKey,
tmpPodKey,
tmpLotKey,
tmpTranDate,
tmpTranQty,
SUM(tmpTranQty) OVER (PARTITION BY tmpPudID, tmpLotKey ORDER BY tmpTranDate) tmpLotRunBal
FROMtestLotTrans
--where tmppudid = 1479-- and tmplotkey = 71871
--ORDER BYtmpPudID, tmpTranDate
) WORK
LEFT JOIN
testWkpCompTotals CT ON CT.totHdrID = WORK.tmpHdrID AND
CT.totPodKey = WORK.tmpPodKey AND
CT.totTotQty <> 0
LEFT JOIN
testWkpCompQtys CQ ON CQ.cmpHdrID = WORK.tmpHdrID AND
CQ.cmpPudID = WORK.tmpPudID AND
CQ.cmpPodKey = WORK.tmpPodKey
--ORDER BY AsOfPudID , AsOfTranDate , AsOfLotKey , AsOfLotBalSeq;
) WORK2
WHEREAsOfPudID = 1479 and AsOfTranDate between '2011-09-06 18:15:29.000' and '2011-09-17 17:22:54.000'--AND AsOfLotKey = 71871
--AND (AsOfPudID = 751 AND AsOfLotKey = 72459)
ORDER BY AsOfPudID , AsOfTranDate , AsOfLotKey , AsOfLotBalSeq;
My experiment with using the percents of the Input side of the transaction rather than the resulting percent of the output lot did not work. I'm now considering a For/While loop to separate the lots :crying: