SQLServerCentral.com / SQL Server 2012 / SQL Server 2012 - T-SQL / Help with complicated running balance / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 08:30:21 GMT20RE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxHere is the sql I'm using for my testing, JLS. Let me know if this answers your question:[code="sql"]TRUNCATE TABLE testLotTrans-- GET LOT TRANSACTIONS FOR EACH PudID INSERT INTO testLotTransSELECT DISTINCT cmpPudID tmpPudID, wkhHdrID tmpHdrID, IntKey tmpIntKey, IntPodKey tmpPodKey, IntLotKey tmpLotKey, IntTranDate tmpTranDate, IntTranQty tmpTranQtyFROM InventoryTran 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 FROM testWkpCompQtys) CMP ON CMP.cmpHdrID = wkhHdrID AND CMP.cmpPodKey = IntPodKey INNER JOIN PurchOrderDtl ON PudID = CmpPudID INNER JOIN Item PURCHITEM ON PURCHITEM.ItmItemCode = PudItemIDWHERE OprType NOT IN ('BOTTLE','SHIP','RECEIVE') AND -- BECAUSE WE WANT TO KNOW THE TOTAL GALLONS PRODUCED FROM THE TONS EXCLUDING SHIPMENTS/BOTTLING/RECEIVING IntTranQty &lt;&gt; 0 AND IntTranUOM = 'GA' AND PURCHITEM.ItmType &lt;&gt; 'B' AND -- DON'T INCLUDE PURCHASED BULK JUICE OR WINE FOR CALCULATING YIELDS cmpPudID &lt;&gt; 0ORDER BY IntTranDate -- GET LOT TRANSACTIONS THAT HAPPENED PRIOR TO THE PUDID COMING INTO THE PICTURE (IF ANY) INSERT INTO testLotTransSELECT DISTINCT RB.tmpPudID tmpPudID, WKH.wkhHdrID tmpHdrID, IT.IntKey tmpIntKey, IT.IntPodKey tmpPodKey, IT.IntLotKey tmpLotKey, IT.IntTranDate tmpTranDate, IT.IntTranQty tmpTranQtyFROM testLotTrans 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 = PudItemIDWHERE OprType NOT IN ('BOTTLE','SHIP','RECEIVE') AND -- BECAUSE WE WANT TO KNOW THE TOTAL GALLONS PRODUCED FROM THE TONS DISREGARDING SHIPMENTS/BOTTLING/RECEIVING IT.IntTranQty &lt;&gt; 0 AND IT.IntTranUOM = 'GA' AND PURCHITEM.ItmType &lt;&gt; 'B' AND -- DON'T INCLUDE PURCHASED BULK JUICE OR WINE FOR CALCULATING YIELDS RB.tmpPudID &lt;&gt; 0 AND RB2.tmpIntKey IS NULL -- ONLY GET ROWS THAT DON'T EXIST YET IN THE testLotTrans TABLE ORDER BY IT.IntTranDate--SELECT RESULTSSELECT 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 ( SELECT WORK.tmpPudID AsOfPudID, WORK.tmpTranDate AsOfTranDate, WORK.tmpTranQty AsOfTranQty, WORK.tmpLotKey AsOfLotKey, CQ.cmpCompQty / CT.totTotQty * 100 AsOfPudIDPct, tmpLotRunBal, WORK.tmpLotRunBal * CQ.cmpCompQty / CT.totTotQty AsOfLotBal, ROW_NUMBER() OVER (PARTITION BY WORK.tmpPudID, WORK.tmpLotKey ORDER BY WORK.tmpTranDate) AsOfLotBalSeq FROM ( SELECT tmpPudID, tmpHdrID, tmpIntKey, tmpPodKey, tmpLotKey, tmpTranDate, tmpTranQty, SUM(tmpTranQty) OVER (PARTITION BY tmpPudID, tmpLotKey ORDER BY tmpTranDate) tmpLotRunBal FROM testLotTrans --where tmppudid = 1479-- and tmplotkey = 71871 --ORDER BY tmpPudID, tmpTranDate ) WORK LEFT JOIN testWkpCompTotals CT ON CT.totHdrID = WORK.tmpHdrID AND CT.totPodKey = WORK.tmpPodKey AND CT.totTotQty &lt;&gt; 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; ) WORK2WHERE AsOfPudID = 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;[/code]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:Wed, 24 Apr 2013 12:26:27 GMTgcresseRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxGinain the examples you give code/spreadsheet can you please clarify what pre exists as data and what you are calculating.sorry for being thick :-PWed, 24 Apr 2013 11:39:14 GMTJ Livingston SQLRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxJLS, 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 PudIDPercent for the input side of the transaction. I'll let you know if that works. fingers crossed.GinaWed, 24 Apr 2013 10:11:48 GMTgcresseRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxHi 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,GinaTue, 23 Apr 2013 19:53:41 GMTgcresseRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxHi Ginajust 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"[code="sql"]--- windows sum over for running total http://msdn.microsoft.com/en-us/library/ms189461.aspxUSE [tempdb]GOCREATE TABLE [dbo].[TAB1]( [trandid] [int] NULL, [lotid] [int] NULL, [lotidseq] [int] NULL, [qty] [int] NULL) ON [PRIMARY]GOINSERT [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;[/code]Tue, 23 Apr 2013 14:58:23 GMTJ Livingston SQLRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxI 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:[code="sql"]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 jlstot2FROM( SELECT 1479 PudID, '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) WORKORDER BY PudID , TranDate , LotID , LotIDBalSeq;[/code]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,GinaMon, 22 Apr 2013 19:05:11 GMTgcresseRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxYes, as I was driving to meet a friend for lunch, it dawned on me that I will need to partition by PudID when I include them all. Thanks for the confirmation.Sat, 20 Apr 2013 15:44:27 GMTgcresseRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspx[quote][b]gcresse (4/20/2013)[/b][hr]Perfect! That's exactly the answer I was looking for. I tried all sorts of SUM OVER PARTITION BY combinations with no luck, but I see the PARTITION wasn't required. I guess I was over-thinking it.Thank you so much, JLS. If ever you want a really good bottle of wine, just let me know :-DGina[/quote]Thanks Gina....would suggest that you examine exec plans for larger data sets and index where applicable.good luck and thanks for the offer (Châteauneuf-du-Pape 1998_&lt;grin&gt;)edit ...for multiple PudId use partition byeg[code="sql"]OVER ( PARTITION BY PUDID ORDER BY PudID , TranDate )[/code]Sat, 20 Apr 2013 13:49:26 GMTJ Livingston SQLRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxPerfect! That's exactly the answer I was looking for. I tried all sorts of SUM OVER PARTITION BY combinations with no luck, but I see the PARTITION wasn't required. I guess I was over-thinking it.Thank you so much, JLS. If ever you want a really good bottle of wine, just let me know :-DGinaSat, 20 Apr 2013 13:37:02 GMTgcresseRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxis the following any where near what you are looking for?note...rounding issues...used for simplicity[code="sql"]SELECT PudID , TranDate , TranQty , LotID , 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 jlstot2FROM( SELECT 751 PudID,'2011-09-19 18:21:09.000' TranDate, 64298 TranQty, 64298 RunBal, 56.477600 PudIDPct, '42011025' LotID, 36314 PudIDLotBal, 1 LotIDBalSeq UNION SELECT 751,'2011-09-20 03:20:08.000', -45938, 64298, 56.477600, '42011025', 36314, 2 UNION SELECT 751,'2011-09-20 03:20:08.000', 45938, 64298, 56.477600, '42011025', 36314, 3 UNION SELECT 751,'2011-09-20 03:21:08.000', -18360, 60346, 56.483300, '42011025', 25947, 4 UNION SELECT 751,'2011-09-20 03:21:08.000', 14408, 60346, 56.477600, '42011025A',8137, 1 UNION SELECT 751,'2011-09-20 18:16:49.000', 27122, 87468, 100.00000, '42011027', 27122, 1 UNION SELECT 751,'2011-09-20 19:51:29.000', 35454, 122922, 19.805900, '42011026', 7021, 1 UNION SELECT 751,'2011-09-20 21:30:22.000', 12757, 121271, 10.055800, '42011022A',1282, 1 UNION SELECT 751,'2011-09-20 21:30:22.000', -14408, 121271, 56.477600, '42011025A',0, 2 UNION SELECT 751,'2011-09-21 02:09:25.000', 23515, 121271, 19.805900, '42011026', 7021, 2 UNION SELECT 751,'2011-09-21 02:09:25.000', -23515, 121271, 19.805900, '42011026', 7021, 3 UNION SELECT 751,'2011-09-21 02:11:00.000', -11939, 121271, 19.800000, '42011026', 4655, 4 UNION SELECT 751,'2011-09-21 02:11:00.000', 11939, 121271, 19.805900, '42011026A',2364, 1) WORKORDER BY PudID , TranDate , LotID , LotIDBalSeq;[/code]Sat, 20 Apr 2013 11:56:37 GMTJ Livingston SQLRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxHi Chris,That other post was an attempt to see if I could use some sort of "fast" running balance to calculate blend percentages (variety of grape, vintage of grape, appellation of grape) on-the-fly for a single lot rather than having to maintain a data warehouse that updates during a nightly job. I was never able to create a view that was fast enough to calculate the percentages so we continue to use the data warehouse.This current post is about using the percent data from that same data warehouse but needing to group and sort those results according the purchase order that sourced the grapes that ultimately became wine. Thanks,GinaSat, 20 Apr 2013 10:41:30 GMTgcresseRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspx[quote][b]gcresse (4/15/2013)[/b][hr]I have a challenge that I was hoping to solve using the new SQL Server 2012 running balance capabilities, but it's a bit more complicated than I realized. I've attached a small spreadsheet with some sample data.[/quote]Hi GinaI remember a [url=http://www.sqlservercentral.com/Forums/Topic1168462-392-1.aspx]similar requirement from a year or two ago[/url] - how did you solve it then, and how different is the requirement now?Sat, 20 Apr 2013 10:29:44 GMTChrisM@homeRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxThe results I've included in this post are from a query. I've just tried to simplify the issue since I'm really only interested in getting the correct running balance for the PudIDLotBal. Let me see if I can clarify this a bit.These transactions are for wine lots (LotID). We purchase grapes from various growers using purchase orders (PudID) then we crush and process the grapes on inventory transactions (TranDate, TranQty). The resulting wine gets blended into multiple lots, and wine from multiple purchase orders can be included in a single lot. What I'm trying to do is determine how many gallons of wine were produced from each purchase order as of any transaction date.In the spreadsheet, the RunBal is simply a running balance of the total transaction qty, and is really of no use for what I'm trying to do. I should have left it out of the spreadsheet to avoid confusion. Sorry about that. The PudIDPct is the portion of the resulting lot balance that was sourced from the purchase order (PudID) and that is included simply for auditing purposes. The PudIDLotBal is the amount of wine for that Lot as of that transaction date/time that was sourced from the purchase order and that's the column that is important to me. As more and more lots of wine that have portions sourced from the purchase order enter the picture, I need to keep track of how many gallons those grapes from that purchase order have produced. Does that make sense? As I drifted off to sleep last night, I had the idea that perhaps I should try to pivot the results so each LotID becomes a column. I haven't tried it yet, but that will probably be my next experiment, unless someone has a brilliant idea for me to try.Thanks,GinaSat, 20 Apr 2013 08:54:15 GMTgcresseRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspx[code="sql"]SELECT PudID, TranDate, TranQty, RunBal, PudIDPct, LotID, PudIDLotBal, LotIDBalSeq, 'help?' PudIDAsOfBal[/code]may be I am being particulalary thick this morning but I cant seem to follow your spreadsheet for "Runbal" and "PudIDLotBal"....are these columns in your database table that are already populated by the application or are they columns that you created by some previous query?[code="plain"][font="Courier New"]PudID TranDate TranQty RunBal751 9/19/11 18:21 64298 64298751 9/20/11 3:20 -45938 64298751 9/20/11 3:20 45938 64298751 9/20/11 3:21 -18360 60346751 9/20/11 3:21 14408 60346751 9/20/11 18:16 27122 87468751 9/20/11 19:51 35454 122922751 9/20/11 21:30 12757 121271751 9/20/11 21:30 -14408 121271751 9/21/11 2:09 23515 121271751 9/21/11 2:09 -23515 121271751 9/21/11 2:11 -11939 121271751 9/21/11 2:11 11939 121271[/font][/code][code="plain"][font="Courier New"]PudID TranDate TranQty JLS runtot751 9/19/11 18:21 64298 751 9/20/11 3:20 -45938 18360751 9/20/11 3:20 45938 64298751 9/20/11 3:21 -18360 45938751 9/20/11 3:21 14408 60346751 9/20/11 18:16 27122 87468751 9/20/11 19:51 35454 122922751 9/20/11 21:30 12757 135679751 9/20/11 21:30 -14408 121271751 9/21/11 2:09 23515 144786751 9/21/11 2:09 -23515 121271751 9/21/11 2:11 -11939 109332751 9/21/11 2:11 11939 121271[/font][/code]Sat, 20 Apr 2013 03:40:07 GMTJ Livingston SQLRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxThanks Jeff. Yes, I want to use the 2012 functionality, but I guess I don't quite understand what you're suggesting. I can create a running total on the balances (which will be wrong) using SUM OVER PARTITION... but after that, I'm not sure what you mean by marking the duplicates.Fri, 19 Apr 2013 17:01:34 GMTgcresseRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxI thought you said you were using 2012 functionality. It now appears that you've used a Triangular Join ( See the following link for why they are so slow http://www.sqlservercentral.com/articles/T-SQL/61539/ ). Like I suggested, first do a "normal" running total using the 2012 functionality and then mark the dupes with the max.Fri, 19 Apr 2013 16:55:46 GMTJeff ModenRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxThe Plan B query is excrutiatingly slow :(Any suggestions on how I can get the same results but with better performance?Wed, 17 Apr 2013 19:08:54 GMTgcresseRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxOK, I'm trying plan b but the running balance is not correct. It seems to ignore my request for only the highest seq for each lot. I must be missing something obvious, but I just can't see it. Any ideas?[code="sql"]SELECT AsOfPudID, AsOfTranDate, AsOfLotKey, AsOfLotBal, AsOfLotBalSeq, (SELECT SUM(B.AsOfLotBal) FROM testAsOfProdGalsByLot B WHERE B.AsOfPudID = A.AsOfPudID AND B.AsOfTranDate &lt;= A.AsOfTranDate AND B.AsOfLotBalSeq = ( SELECT TOP 1 C.AsOfLotBalSeq FROM testAsOfProdGalsByLot C WHERE C.AsOfPudID = B.AsOfPudID AND C.AsOfTranDate &lt;= B.AsOfTranDate AND C.AsOfLotKey = B.AsOfLotKey ORDER BY AsOfLotBalSeq DESC))FROM( SELECT PudID AsOfPudID, TranDate AsOfTranDate, LotID AsOfLotKey, PudIDLotBal AsOfLotBal, LotIDBalSeq AsOfLotBalSeq FROM ( SELECT 751 PudID,'2011-09-19 18:21:09.000' TranDate, 64298 TranQty, 64298 RunBal, 56.477600 PudIDPct, '42011025' LotID, 36314 PudIDLotBal, 1 LotIDBalSeq UNION SELECT 751,'2011-09-20 03:20:08.000', -45938, 64298, 56.477600, '42011025', 36314, 2 UNION SELECT 751,'2011-09-20 03:20:08.000', 45938, 64298, 56.477600, '42011025', 36314, 3 UNION SELECT 751,'2011-09-20 03:21:08.000', -18360, 60346, 56.483300, '42011025', 25947, 4 UNION SELECT 751,'2011-09-20 03:21:08.000', 14408, 60346, 56.477600, '42011025A',8137, 1 UNION SELECT 751,'2011-09-20 18:16:49.000', 27122, 87468, 100.00000, '42011027', 27122, 1 UNION SELECT 751,'2011-09-20 19:51:29.000', 35454, 122922, 19.805900, '42011026', 7021, 1 UNION SELECT 751,'2011-09-20 21:30:22.000', 12757, 121271, 10.055800, '42011022A',1282, 1 UNION SELECT 751,'2011-09-20 21:30:22.000', -14408, 121271, 56.477600, '42011025A',0, 2 UNION SELECT 751,'2011-09-21 02:09:25.000', 23515, 121271, 19.805900, '42011026', 7021, 2 UNION SELECT 751,'2011-09-21 02:09:25.000', -23515, 121271, 19.805900, '42011026', 7021, 3 UNION SELECT 751,'2011-09-21 02:11:00.000', -11939, 121271, 19.800000, '42011026', 4655, 4 UNION SELECT 751,'2011-09-21 02:11:00.000', 11939, 121271, 19.805900, '42011026A',2364, 1 ) WORK) A[/code]UPDATE: I found my issue. This code seems to work for me now:[code="sql"]SELECT AsOfPudID, AsOfTranDate, AsOfLotKey, AsOfLotBal, AsOfLotBalSeq, (SELECT SUM(B.AsOfLotBal) FROM testAsOfProdGalsByLot B WHERE B.AsOfPudID = A.AsOfPudID AND B.AsOfTranDate &lt;= A.AsOfTranDate AND B.AsOfLotBalSeq = ( SELECT TOP 1 C.AsOfLotBalSeq FROM testAsOfProdGalsByLot C WHERE C.AsOfPudID = A.AsOfPudID AND C.AsOfTranDate &lt;= A.AsOfTranDate AND C.AsOfLotKey = B.AsOfLotKey ORDER BY AsOfLotBalSeq DESC))FROM( SELECT PudID AsOfPudID, TranDate AsOfTranDate, LotID AsOfLotKey, PudIDLotBal AsOfLotBal, LotIDBalSeq AsOfLotBalSeq FROM ( SELECT 751 PudID,'2011-09-19 18:21:09.000' TranDate, 64298 TranQty, 64298 RunBal, 56.477600 PudIDPct, '42011025' LotID, 36314 PudIDLotBal, 1 LotIDBalSeq UNION SELECT 751,'2011-09-20 03:20:08.000', -45938, 64298, 56.477600, '42011025', 36314, 2 UNION SELECT 751,'2011-09-20 03:20:08.000', 45938, 64298, 56.477600, '42011025', 36314, 3 UNION SELECT 751,'2011-09-20 03:21:08.000', -18360, 60346, 56.483300, '42011025', 25947, 4 UNION SELECT 751,'2011-09-20 03:21:08.000', 14408, 60346, 56.477600, '42011025A',8137, 1 UNION SELECT 751,'2011-09-20 18:16:49.000', 27122, 87468, 100.00000, '42011027', 27122, 1 UNION SELECT 751,'2011-09-20 19:51:29.000', 35454, 122922, 19.805900, '42011026', 7021, 1 UNION SELECT 751,'2011-09-20 21:30:22.000', 12757, 121271, 10.055800, '42011022A',1282, 1 UNION SELECT 751,'2011-09-20 21:30:22.000', -14408, 121271, 56.477600, '42011025A',0, 2 UNION SELECT 751,'2011-09-21 02:09:25.000', 23515, 121271, 19.805900, '42011026', 7021, 2 UNION SELECT 751,'2011-09-21 02:09:25.000', -23515, 121271, 19.805900, '42011026', 7021, 3 UNION SELECT 751,'2011-09-21 02:11:00.000', -11939, 121271, 19.800000, '42011026', 4655, 4 UNION SELECT 751,'2011-09-21 02:11:00.000', 11939, 121271, 19.805900, '42011026A',2364, 1 ) WORK) A[/code]Wed, 17 Apr 2013 12:10:18 GMTgcresseRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxI'm thinking that we should simplify the requirements a bit. From what I see, it boils down to two things.1. Do a normal running total first.2. Update any tied date/times of the first result set with the MAX value from that date/time group.You might be able to do Step 2 in the same code as Step 1 if you preaggregate the data so that you can create a "single previous row" for each datetime group and then join the result set back to the original data. I've not done a deep dive on this but it looks like you'll need two passes on the table one way or another. I've also not worked with the new capabilites of the Windowing Functions in 2012 so I could certainly be wrong there.Tue, 16 Apr 2013 06:52:33 GMTJeff ModenRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxSorry about that. I must've grabbed the wrong file. I've re-uploaded the correct attachment.Mon, 15 Apr 2013 23:46:30 GMTgcresseRE: Help with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspx[quote][b]gcresse (4/15/2013)[/b][hr]I've included some comments on how that number is calculated. [/quote]Where? There's a whole lot of attachments.Mon, 15 Apr 2013 21:22:11 GMTJeff ModenHelp with complicated running balancehttp://www.sqlservercentral.com/Forums/Topic1442557-3077-1.aspxI have a challenge that I was hoping to solve using the new SQL Server 2012 running balance capabilities, but it's a bit more complicated than I realized. I've attached a small spreadsheet with some sample data.Basically these are inventory transactions for multiple LotIDs that are all part of a single PudID group and I need to be able to calculate the running balance for the PudID, which is the PudIDAsOfBal column in the spreadsheet. I've included some comments on how that number is calculated. I added the "LotIDBalSeq" column thinking I could somehow sum up the last PudIDLotBal for each LotID as of the TranDate. The following code will return the results I have to work with. What I need is one more column with the PudIDAsOfBal:[code="sql"]SELECT PudID, TranDate, TranQty, RunBal, PudIDPct, LotID, PudIDLotBal, LotIDBalSeq, 'help?' PudIDAsOfBalFROM( SELECT 751 PudID,'2011-09-19 18:21:09.000' TranDate, 64298 TranQty, 64298 RunBal, 56.477600 PudIDPct, '42011025' LotID, 36314 PudIDLotBal, 1 LotIDBalSeq UNION SELECT 751,'2011-09-20 03:20:08.000', -45938, 64298, 56.477600, '42011025', 36314, 2 UNION SELECT 751,'2011-09-20 03:20:08.000', 45938, 64298, 56.477600, '42011025', 36314, 3 UNION SELECT 751,'2011-09-20 03:21:08.000', -18360, 60346, 56.483300, '42011025', 25947, 4 UNION SELECT 751,'2011-09-20 03:21:08.000', 14408, 60346, 56.477600, '42011025A',8137, 1 UNION SELECT 751,'2011-09-20 18:16:49.000', 27122, 87468, 100.00000, '42011027', 27122, 1 UNION SELECT 751,'2011-09-20 19:51:29.000', 35454, 122922, 19.805900, '42011026', 7021, 1 UNION SELECT 751,'2011-09-20 21:30:22.000', 12757, 121271, 10.055800, '42011022A',1282, 1 UNION SELECT 751,'2011-09-20 21:30:22.000', -14408, 121271, 56.477600, '42011025A',0, 2 UNION SELECT 751,'2011-09-21 02:09:25.000', 23515, 121271, 19.805900, '42011026', 7021, 2 UNION SELECT 751,'2011-09-21 02:09:25.000', -23515, 121271, 19.805900, '42011026', 7021, 3 UNION SELECT 751,'2011-09-21 02:11:00.000', -11939, 121271, 19.800000, '42011026', 4655, 4 UNION SELECT 751,'2011-09-21 02:11:00.000', 11939, 121271, 19.805900, '42011026A',2364, 1) WORK[/code]I thought I could use some variation of SUM(PudIDLotBal) OVER (PARTITION... but having the multiple lots and needing the last balance for each lot as of the TranDate is over my head. Any help woud be greatly appreciated.Thanks,GinaMon, 15 Apr 2013 19:46:06 GMTgcresse