Thanks Luis. The Quirky Update worked perfectly 🙂

Going back to a sample where I'm using the DENSE_RANK, here is another simple example:

CREATE TABLE [dbo].[testGroup](

[testRowID] [int] IDENTITY(1,1) NOT NULL,

[testLotID] varchar(5) NOT NULL,

[testDateTime] [datetime] NULL,

[testDays]...

Thanks! This works.

Ah... there it is. Thanks!

Fantastic! I have never used ROWS UNBOUNDED PRECEDING but I'm going to start reading up on that functionality. Very useful.

Thanks, again!

Gina

One more small request 🙂 The Balance returned from the cte is the Ending balance. Is there a way I can also get the Beginning balance for each...

Wow! Both MickyT and TheSQLGuru results come back very fast with my live tran table. Thank you both so much for your help. This is exactly what...

Thanks MickyT, this looks closer to what I'm trying to accomplish. I'd need the IntTranQty summed up to 1 row per period so if there are multiple transactions in...

Thanks, Luis. When I originally posted the question, I didn't talk about the running balance, but since I added the test data I thought I'd go ahead and ask...

OK, sorry about that. I'll start over. Here's code to build a sample @InventoryTran table:

DECLARE@InventoryTran TABLE (

IntLotKeyINT,

IntTranDateDATE,

IntTranQtyINT)

INSERT INTO @InventoryTran

SELECT27153IntLotKey, '1-1-2013' IntTranDate, 10IntTranQty

UNIONSELECT27153IntLotKey, '3-10-2013' IntTranDate, 20IntTranQty

UNIONSELECT27153IntLotKey, '5-15-2013' IntTranDate, 30IntTranQty

UNIONSELECT28468IntLotKey,...

Thanks Igor. The link appears to be broken, but we ended up not uninstalling afterall. I believe I could have gone through the Control Panel > Add/Remove programs and...

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...

JLS, I'm going to see if I can go back to your original code but instead of using the final output percent, I will see if I can get the...

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...

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...