Hi Gina

just back from work...so sorry for delay.

I must admit that I am struggling to understand your reqs.

there are rows for exactly same date/time/lotid...with a qualifying column of lotidbalseq...have you not got a sequential uniquie ID ref?

in your last spreadsheet you are now presenting data that is different than before... in that the PUDIDPct column is often zero where as before this was always populated (tab PUDID751)

I am still not sure what bits of the data come from the db and what you have already calcultated in your query....me thinks it may be useful to provide data based on what exists in the app and start from there....and to provide some sample data that mimic a start from the beginning rather than jumping into a set of transactions half way thro....eg start from zero PUDID and zero LOTId and walk thro what you need....we can worry about opening balances later on.

sorry to sound so negative :sick:

an idea possible maybe to create a temptable and join back to the originating data using 2012 sliding windows...depends really on what you need as final output...are you determined to have every row with the results or just a summary (your spreadsheet tab 1479 has four rows for LotId 7183 all with the same PUDIDAsOfBal...11056..??)

not sure if the following demo code will help/hinder/inspire.....but take a look..especially with ref to

"ROWS 2 PRECEDING"

--- windows sum over for running total http://msdn.microsoft.com/en-us/library/ms189461.aspx

USE [tempdb]

GO

CREATE TABLE [dbo].[TAB1](

[trandid] [int] NULL,

[lotid] [int] NULL,

[lotidseq] [int] NULL,

[qty] [int] NULL

) ON [PRIMARY]

GO

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (1, 1, 1, 100)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (2, 2, 1, 50)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (3, 3, 1, 75)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (4, 1, 2, 44)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (5, 2, 2, 56)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (6, 3, 2, 25)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (7, 1, 3, 12)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (8, 2, 3, 0)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (9, 3, 3, 0)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (10, 1, 4, 56)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (11, 2, 4, 18)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (12, 3, 4, 94)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (13, 1, 5, 68)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (14, 2, 5, 45)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (15, 3, 5, 66)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (16, 1, 6, 45)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (17, 2, 6, 87)

INSERT [dbo].[TAB1] ([trandid], [lotid], [lotidseq], [qty]) VALUES (18, 3, 6, 12)

SELECT trandid

, lotid

, lotidseq

, qty

, sum (qty) over (partition by lotid order by trandid) lotidRT

, sum (qty) over ( partition by lotid order by trandid ROWS 2 PRECEDING ) lastRT

, sum (qty) over ( order by trandid) RT

FROM TAB1;