I ran into a bit of a glitch with the solution, JLS. We have some situations where there was already gallons of wine in a lot prior to introducing gallons for a particular PudID. Here is a sample:

SELECT PudID

, TranDate

, TranQty

, LotID

, LotIDBalSeq

, LotRunBal

, PudIDPct

, CAST( TranQty * PudIDPct / 100 as INT) AS jlsqty

, CAST (SUM ( TranQty * PudIDPct / 100 ) OVER ( ORDER BY PudID , TranDate , LotID , LotIDBalSeq ) AS INT) AS jlstot

, CAST (SUM ( TranQty * PudIDPct / 100 ) OVER ( ORDER BY PudID , TranDate ) AS INT) AS jlstot2

FROM

(SELECT 1479PudID,'2011-09-06 18:15:29.000' TranDate,15586 TranQty,71871 LotID,1 LotIDBalSeq,15586 LotRunBal,0.0000000000000000 PudIDPct

UNION SELECT 1479,'2011-09-07 08:02:49.000',10168,71871,2,25754,20.9598514468311413

UNION SELECT 1479,'2011-09-07 17:43:48.000',23750,71909,1,23750,24.5894491788983578

UNION SELECT 1479,'2011-09-10 04:57:00.000',-39114,71813,2,38204,0.0000000000000000

UNION SELECT 1479,'2011-09-10 04:57:00.000',38204,71813,3,38204,0.0000000000000000

UNION SELECT 1479,'2011-09-10 16:45:13.000',25382,71871,3,25382,20.9598514523353064

UNION SELECT 1479,'2011-09-10 16:45:13.000',-25754,71871,4,25382,21.0000000000000000

UNION SELECT 1479,'2011-09-11 15:11:35.000',23327,71871,5,48709,22.6980833670217669

UNION SELECT 1479,'2011-09-11 15:11:35.000',-23750,71909,2,0,24.5894491788983578

UNION SELECT 1479,'2011-09-12 20:06:06.000',-38204,71813,4,37505,0.0000000000000000

UNION SELECT 1479,'2011-09-12 20:06:06.000',37505,71813,5,37505,0.0000000000000000

UNION SELECT 1479,'2011-09-17 04:49:58.000',34282,71813,6,71787,0.0000000000000000

UNION SELECT 1479,'2011-09-17 04:51:51.000',24250,71813,7,96037,0.0000000000000000

UNION SELECT 1479,'2011-09-17 17:22:54.000',46329,71813,8,142366,7.3864385186086848

UNION SELECT 1479,'2011-09-17 17:22:54.000',-48709,71871,6,0,22.6980833670217669

) WORK

ORDER BY PudID , TranDate , LotID , LotIDBalSeq;

If you run this, you'll see that we end up with a negative balance for the jlstot2 column at the end of the transactions. I've uploaded a new spreadsheet and on the PUDID1479 tab you can see what the PudIDAsOfBal should be (in the last column). I've identified each lot by color, and I've used a formula to calculate the PudIDAsOfBal. I'm back to my original thought that I need to grab the last LotIDBalSeq for each lot as of the tran date for figuring the PudIDAsOfBal.

The catch is that the PudIDPct is not the percent of the TranQty that belongs to the PudID, but rather it is the percent of the LotRunBal that belongs to the PudID at the end of that transaction.

Burning more brain cells now... Thanks for any input/ideas you may have.

Thanks,

Gina