Hi JLS,

I know it's not really straight forward. I've add comments to the attached spreadsheet to explain why some transactions have the same date/time and why I have to include the transactions that have 0 percent for the PudID. Basically, when we move wine from one tank to another, the transaction is a 2-sided transaction and both sides have the same TranDate because they happen simultaneously. I need to include those movements because we can gain/lose gallons in the move and I'm keeping a running balance for each LotID so I can calculate how much of each LotIDBal belongs to the PudID at each TranDate.

The end goal: We purchase grapes on purchase order (PudID) 1479 and as we process those grapes I need to know how many gallons have been produced from those specific grapes so I can calculate the yield as of any transaction. Since there can be multiple lots that make up the inventory for the PudID, I need to sum up the last balance for each lot as of the selected TranDate.

I'm looking at some of the other Window functions in SQL Server 2012 to see if I can come up with an answer. If I could just include a WHERE statement in the SUM OVER PARTITION and limit the SUM(PudIDLotBal) results to the last TranDate for each LotID up to the As of TranDate, it would work, but I don't see that as an option. I've also considered trying to PIVOT the data somehow, but that would be a challenge since there can be any number of LotIDs for each PudID.

It would be a monumental task to try to recreate the data as it exists in our database to present here instead of just presenting the simplified results, and I fear it would cause even more confusion. I appreciate your efforts and I hope I've cleared up most of your questions.

Thanks for all your help,

Gina