InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 04:56:30 GMT20http://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspx[quote][b]Jeff Moden (3/3/2009)[/b][hr]<REDACTED>Now, just in case anyone gets any smart ideas about using the clustered index in SELECTs to create an "order" without an ORDER BY... [font="Arial Black"]don't bloody well do it[/font]. Even though it looks like it works, there are times when it doesn't and I'll have the proof in the article I'm rewritting. For now, just take my word for it... the "orderless" ordering ONLY works in the "quirky" UPDATE and ONLY when it's properly formed with all the goodies like the "anchor" and forcing the clustered index scan. <REDACTED>I've gotta say it one more time... if you want a correct order out of a SELECT, you MUST use an ORDER BY. But, for the "quirky" UPDATE we did, you can quite literally "bank" on it. ;)[/quote]I am curious about something. I've also seen this "pseudo-ordering" resulting from use of a ROW_NUMBER()/ORDER BY construct in SELECTs. I hesitate to rely on it but it is seems pretty consistent yet does have some quirks, so I continue to use ORDER BY.I'm wondering if you have some thoughts about it or whether you might cover it in the article you mentioned.I've been meaning to test how it might work in a QU scenario but haven't gotten around to it.Mon, 17 Sep 2012 22:19:32 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspxBeen there, done that; Celko's solution works with a simple running total, but I wasn't able to make it work correctly with the WAC calculation unless I ran three separate times - once for QtyOnHand, once to calculate the Weighted Average of Unit Cost, and once to multiply those together to get the Extended Cost. I just wanted to express my thanks for giving me a more elegant solution.Thu, 13 Sep 2012 08:37:32 GMTrc_stone_1http://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspx[quote][b]rc_stone_1 (9/13/2012)[/b][hr]Actually, I am using 2012; ... [/quote]May I suggest that you post your question in a more appropriate forum in the future? You did post this in a SQL Server 2005 forum, so you are more likely to receive a solution that will work in SQL Server 2005. Using SQL Server 2012 opens doors to other options to help solve your problem that could be more efficient.Thu, 13 Sep 2012 08:25:46 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspxActually, I am using 2012; but I'm not doing a simple running total calculation, I'm doing a weighted average cost calculation, and I have to keep track of three aggregate values that are inter-related but do not always change at the same time:QtyOnHand, which is the running total of Qty; This changes on all TranTypes except Verifications (VTI, VC) UnitCost, which is an average based on the equation ((Qty_1 * UnitCost_1)..+(Qty_n * UnitCost_n))/SUM(Qty) which changes with Verifications, and may also change on onther incoming TranTypes if the UnitCost is known (sometimes it is, sometimes it isn't)ExtendedCost, which is the QtyOnHand * UnitCost (but remember that UnitCost is being calculated at the same time).The code I posted above doesn't include my group by, as I'm still working on that one. My parts are grouped by:Branch (store branch)PartNo (part number)SKey (a subset of the part number inventory that is tagged and put aside for an order); Inventory not currently tagged has an SKey of 0.And everything is ordered by date. The fun part comes in because even though the WAC (Weighted Average Cost) is calculated at the Branch level, sometimes SKeys include more than one branch's running totals - for example, Branch 12 may order parts from Branches 11 and 13 to fill an order, and the unit cost on parts from Branch 11 can be different from the unit cost for parts from Branch 13. So far, so good, but wait - there's more.Just to make things interesting, the unit cost of the parts from Branch 13 may change AFTER they were sent, because their original unit cost had not been verified yet. (We receive parts on an ongoing basis from our vendors, and may not know about a price increase until we get the invoice at the end of the month). I'm still working on how to get this change into my calculations in the proper order.Thu, 13 Sep 2012 08:11:17 GMTrc_stone_1http://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspx[quote][b]CELKO (9/11/2012)[/b][hr]The short answer is that this is a running total problem which can be done with the SUM () OVER (PARTITION BY .. ORDER BY .. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) The better answer is that the DDL you attached is full of fundamental design errors. I also loved the leading commas, which I use as a symptom to diagnosis bad SQL. Your mindset is still stuck in punch cards and sequential magnetic tape files. Trust me; I have been at this for a few decades. Why is your vague “<nothing in particular>_qty” a FLOAT? Is it the on hand quantity, the back order quantity, the sold quantity? A fifty character “<nothing in particular>_status”? Invite crap data like this and it will come. You have no keys, so this is not a table at all. CREATE TABLE Stockroom (item_name VARCHAR(25) NOT NULL), trans_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, transaction_qty INTEGER NOT NULL CHECK (onhand_qty >= 0)); The “id” and “seq” were obviously redundant, caused by an attempt to mimic a mag tape record number in in SQL. But so is the transaction type. Issuing something is a negative quantity; stocking it is positive. Use the sign of the quantity to put that fact into one column. INSERT INTO StockroomVALUES('ACTIFED', '2004-01-01', +100), ('ACTIFED', '2004-03-28', -5), ('ACTIFED', '2004-04-01', -5), ('ACTIFED', '2004-05-05', -5), ('ACTIFED', '2004-05-10', -5), ('AGIOLAX', '2004-01-01', +50), ('AGIOLAX', '2004-02-12', -10), ('AGIOLAX', '2004-03-28', +50), ('AGIOLAX', '2004-05-05', -25), ('OTRIVIN CHILD 0.5% NDrops', '2004-01-01', +200), ('OTRIVIN CHILD 0.5% NDrops', '2004-03-28', +50), ('OTRIVIN CHILD 0.5% NDrops', '2004-04-01', -15), ('OTRIVIN CHILD 0.5% NDrops', '2004-05-05', -10);SELECT item_name, trans_date, SUM (transaction_qty) OVER (PARTITION BY item_name ORDER BY trans_date) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ohhand_qty FROM Stock_Room;[/quote]Quite true Mr. Celko, IF (and the operative word here is IF) we were using SQL Server 2012. This is a SQL Server 2005 forum so I would suggest, sir, that keep your solutions to something that will work in the version of SQL Server that the individual is currently using. It might be nice to show someone what they are missing, but it doesn't help solve the current problem, now does it?Tue, 11 Sep 2012 13:27:20 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspxThe short answer is that this is a running total problem which can be done with the SUM () OVER (PARTITION BY .. ORDER BY .. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) The better answer is that the DDL you attached is full of fundamental design errors. I also loved the leading commas, which I use as a symptom to diagnosis bad SQL. Your mindset is still stuck in punch cards and sequential magnetic tape files. Trust me; I have been at this for a few decades. Why is your vague “<nothing in particular>_qty” a FLOAT? Is it the on hand quantity, the back order quantity, the sold quantity? A fifty character “<nothing in particular>_status”? Invite crap data like this and it will come. You have no keys, so this is not a table at all. CREATE TABLE Stockroom (item_name VARCHAR(25) NOT NULL), trans_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, transaction_qty INTEGER NOT NULL CHECK (onhand_qty >= 0)); The “id” and “seq” were obviously redundant, caused by an attempt to mimic a mag tape record number in in SQL. But so is the transaction type. Issuing something is a negative quantity; stocking it is positive. Use the sign of the quantity to put that fact into one column. INSERT INTO StockroomVALUES('ACTIFED', '2004-01-01', +100), ('ACTIFED', '2004-03-28', -5), ('ACTIFED', '2004-04-01', -5), ('ACTIFED', '2004-05-05', -5), ('ACTIFED', '2004-05-10', -5), ('AGIOLAX', '2004-01-01', +50), ('AGIOLAX', '2004-02-12', -10), ('AGIOLAX', '2004-03-28', +50), ('AGIOLAX', '2004-05-05', -25), ('OTRIVIN CHILD 0.5% NDrops', '2004-01-01', +200), ('OTRIVIN CHILD 0.5% NDrops', '2004-03-28', +50), ('OTRIVIN CHILD 0.5% NDrops', '2004-04-01', -15), ('OTRIVIN CHILD 0.5% NDrops', '2004-05-05', -10);SELECT item_name, trans_date, SUM (transaction_qty) OVER (PARTITION BY item_name ORDER BY trans_date) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ohhand_qty FROM Stock_Room;Tue, 11 Sep 2012 13:14:31 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspxHey, just wanted to thank you guys for this thread. I have been working on a weighted average cost calculation for two or three days, and stumbled across this link. I've been trying to avoid doing a cursor calculation as I have over 300,000 different inventory items I have to walk through (over 73 million rows total). As you can see from the comments I left in, I shamelessly copied Jeff's code and modified it to fit my needs. Thanks a million.Randy Stone[code="sql"]CREATE TABLE #WACTran( [TranOrder] INT PRIMARY KEY CLUSTERED, [TranType] VARCHAR(3) NULL, [Qty] INT NULL, [UnitCost] DECIMAL(14, 4) NULL, [ExtendedCost] DECIMAL(14, 4) NULL, [QtyOnHand] INT) INSERT INTO [#WACTran](TranOrder, TranType, Qty, UnitCost, ExtendedCost)VALUES (0 ,'BB',100 ,1.5000, 150.0000 ),(1 ,'S',-50 ,0.0000, 0.0000 ),(2 ,'RCT',15 ,0.0000, 0.0000 ),(3 ,'S',-5 ,0.0000, 0.0000 ),(4 ,'VC',15 ,1.6000, 0.0000 ),(5 ,'S',-5 ,0.0000, 0.0000 ),(6 ,'TO',-4 ,0.0000, 0.0000 ),(7 ,'TI',2 ,0.0000, 0.0000 ),(8 ,'VTI',2 ,1.6500, 0.0000 ),(9 ,'RCT',10 ,0.0000, 0.0000 ),(10,'S',-5 ,0.0000, 0.0000 ),(11,'VC',10 ,1.7500, 0.0000 ),(12,'S',-5 ,0.0000, 0.0000 ),(13,'TO',-4 ,0.0000, 0.0000 ),(14,'TI',2 ,0.0000, 0.0000 );--===== Declare some obviously named variablesDECLARE @PrevTranOrder INT, @PrevQtyOnHand INT, @PrevUnitCost DECIMAL(14,4), @PrevExtendedCost DECIMAL(14,4)--===== Do the "quirky" update using a very high speed pseudo-cursor, -- This is very similar to what you would do in a language like "C" except the -- "Read a row/Write a row" is built into the update. UPDATE #WACTran SET @PrevQtyOnHand = QtyOnHand = CASE WHEN TranType = 'BB' THEN Qty WHEN TranType IN ('VTI','VC') THEN @PrevQtyOnHand ELSE @PrevQtyOnHand + Qty END, @PrevUnitCost = UnitCost = CASE WHEN TranType = 'BB' THEN UnitCost WHEN TranType IN ('VTI','VC') THEN (((@PrevQtyOnHand - Qty)*@PrevUnitCost)+(Qty*UnitCost))/@PrevQtyOnHand ELSE @PrevUnitCost END, @PrevExtendedCost = ExtendedCost = CASE WHEN TranType = 'BB' THEN ExtendedCost ELSE @PrevUnitCost*@PrevQtyOnHand END, @PrevTranOrder = TranOrder --Just an "anchor", but gotta have it to guarantee things. FROM [#WACTran] WITH (INDEX(0), TABLOCKX)--===== Display the results SELECT * FROM [#WACTran] ORDER BY TranOrderDROP TABLE #WACTran;[/code]Mon, 10 Sep 2012 14:56:07 GMTrc_stone_1http://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspx[quote][b]Lynn Pettis (3/3/2009)[/b][hr][quote][b]Jeff Moden (3/3/2009)[/b][hr][quote][b]Lynn Pettis (3/3/2009)[/b][hr]Oh, wow. Looks like I may have beat Jeff. But of course, he is probably setting up a million row example to demonstrate his code, where I just took what was presented and went from there. Jeff's code will also be heavily commented as well.[/quote]Heh... you know me all to well, Brother Lynn! :D Although Mathew did a very good job of attaching sample data, the column names are quite a bit different than those posted and I also wanted to show a shorter way to post sample data. And, as you so very well put it, I was playing with my typical million row example and then saw you beat me to answering this. The only thing you forgot was the TABLOCKX to keep people out of the table while the update is occuring. :)If anyone wants to see the million row update to make a running total, you'll just have to wait until I can publish the rewritten article. :hehe:[/quote]You can also find the same code in my [url=http://www.sqlservercentral.com/articles/T-SQL/65522/]article[/url], but it lacks all the detail behind it, as my work was based on Jeff's. (Sorry, shameless plugs.) :hehe:[/quote]Shameless or not, it's a great article... people should go take a look!Tue, 03 Mar 2009 21:57:58 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspx[quote][b]Samuel Vella (2/26/2009)[/b][hr]mh, your solution is fine but when you test it with a lot of data against a cursor, the cursor is orders of magnitude faster[/quote]Man, you have absolutely the correct idea... you put your money where your mouth was with code! Well done!Just to share a few tricks with someone who actually tests code for performance, here's your test code generator...[code]drop table salesCREATE TABLE Sales (DayCount smallint, channel varchar(50), Sales money)CREATE CLUSTERED INDEX ndx_DayCount ON Sales(DayCount)goSET NOCOUNT ONDECLARE @DayCount smallint, @Sales money, @channel varchar(50)SET @DayCount = 0SET @Sales = 10set @channel = 'a'WHILE @DayCount < 10000BEGIN INSERT Sales VALUES (@DayCount,@channel, @Sales) SET @DayCount = @DayCount + 1 SET @Sales = @Sales + 15 set @channel = case when right(cast(@daycount as varchar), 1) = 0 then 'a' when right(cast(@daycount as varchar), 1) = 1 then 'b' when right(cast(@daycount as varchar), 1) = 2 then 'c' when right(cast(@daycount as varchar), 1) = 3 then 'd' when right(cast(@daycount as varchar), 1) = 4 then 'e' when right(cast(@daycount as varchar), 1) = 5 then 'f' when right(cast(@daycount as varchar), 1) = 6 then 'g' when right(cast(@daycount as varchar), 1) = 7 then 'h' when right(cast(@daycount as varchar), 1) = 8 then 'i' when right(cast(@daycount as varchar), 1) = 9 then 'j' endEND[/code]It does a fine job of making 10,000 rows in somewhere just over 3 seconds on my 6 year old box... but, what if you wanted a million rows of that same example just to make sure? The following code makes the same kind of data example, but it makes 100 times more rows in only about 4-5 seconds more...[code]--===== Conditionaly drop the test table IF OBJECT_ID('TempDB..#Sales','U') IS NOT NULL DROP TABLE #Sales--===== Populate the table with a million rows of data similar to yours. -- This ISNULL is to make the resulting DayCount column NOT NULL so -- we can put a primary key on it later. SELECT TOP 1000000 ISNULL(CAST(ROW_NUMBER() OVER (ORDER BY sc1.ID) - 1 AS INT),0) AS DayCount, CAST(ROW_NUMBER() OVER (ORDER BY sc1.ID) *15 AS MONEY) AS Sales, CHAR(CAST(ROW_NUMBER() OVER (ORDER BY sc1.ID) - 1 AS INT)%10+ASCII('a')) AS Channel INTO dbo.#Sales FROM Master.sys.SysColumns sc1 CROSS JOIN Master.sys.SysColumns sc2--===== Add the primary key -- I don't name these on temp tables because they must be unique. ALTER TABLE dbo.#Sales ADD PRIMARY KEY CLUSTERED (DayCount)[/code]Like I said, well done on the testing... now you have a way to do some really heavy duty testing that no one will be able to argue with.Tue, 03 Mar 2009 21:56:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspx[quote][b]Jeff Moden (3/3/2009)[/b][hr][quote][b]Lynn Pettis (3/3/2009)[/b][hr]Oh, wow. Looks like I may have beat Jeff. But of course, he is probably setting up a million row example to demonstrate his code, where I just took what was presented and went from there. Jeff's code will also be heavily commented as well.[/quote]Heh... you know me all to well, Brother Lynn! :D Although Mathew did a very good job of attaching sample data, the column names are quite a bit different than those posted and I also wanted to show a shorter way to post sample data. And, as you so very well put it, I was playing with my typical million row example and then saw you beat me to answering this. The only thing you forgot was the TABLOCKX to keep people out of the table while the update is occuring. :)If anyone wants to see the million row update to make a running total, you'll just have to wait until I can publish the rewritten article. :hehe:[/quote]You can also find the same code in my [url=http://www.sqlservercentral.com/articles/T-SQL/65522/]article[/url], but it lacks all the detail behind it, as my work was based on Jeff's. (Sorry, shameless plugs.) :hehe:Tue, 03 Mar 2009 21:26:07 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspx[quote][b]beezell (3/3/2009)[/b][hr]Hmmm... there are very few scenarios where I've seen cursors faster than CTE or non-RBAR (row by agonizing row) methods. Just curious how many rows you're running through?Cheers,Brian[/quote]That's because what looked like set based code is actually RBAR on steroids known as a "Triangular Join" and it will cripple a server on very low rowcounts even as low as 20,000 rows. Read all about in the following link...[url]http://www.sqlservercentral.com/articles/T-SQL/61539/[/url]Tue, 03 Mar 2009 21:20:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspx[quote][b]Lynn Pettis (3/3/2009)[/b][hr]Oh, wow. Looks like I may have beat Jeff. But of course, he is probably setting up a million row example to demonstrate his code, where I just took what was presented and went from there. Jeff's code will also be heavily commented as well.[/quote]Heh... you know me all to well, Brother Lynn! :D Although Mathew did a very good job of attaching sample data, the column names are quite a bit different than those posted and I also wanted to show a shorter way to post sample data. And, as you so very well put it, I was playing with my typical million row example and then saw you beat me to answering this. The only thing you forgot was the TABLOCKX to keep people out of the table while the update is occuring. :)Anyway, here's the short, heavily commented version... do read the comments as they make a point about this method of updating...[code]--===== Conditional drop the temporary test table (just so we can rerun the test, if we want) IF OBJECT_ID('TempDB..#TempStk','U') IS NOT NULL DROP TABLE #TempStk--===== Creat the temporary test table with a clustered PK on Seq CREATE TABLE #TempStk ( ID INT PRIMARY KEY CLUSTERED, --Can be an IDENTITY, as well Code INT, Name NVARCHAR(300), Txn_Type NVARCHAR(50), Date DATETIME, Quantity FLOAT, Balance FLOAT --===== Populate the test table with data. Notice the data is pretty much out of order so -- far as the clustered PK is concerned. If the ID can't be maintained in the correct -- order, not to worry... put the clustered index on Code, Name, and Date. Notice I -- said nothing about that having to be a PK. ;-) INSERT INTO #TempStk (ID, Code, Name, Txn_Type, Date, Quantity) SELECT '13','8112','OTRIVIN CHILD 0.5% NDrops','Issued','May 5 2004 12:00AM','10' UNION ALL SELECT '6','8110','AGIOLAX','Receipt','Jan 1 2004 12:00AM','50' UNION ALL SELECT '1','8100','ACTIFED','Receipt','Jan 1 2004 12:00AM','100' UNION ALL SELECT '3','8100','ACTIFED','Issued','Apr 1 2004 12:00AM','5' UNION ALL SELECT '4','8100','ACTIFED','Issued','May 5 2004 12:00AM','5' UNION ALL SELECT '10','8112','OTRIVIN CHILD 0.5% NDrops','Receipt','Jan 1 2004 12:00AM','200' UNION ALL SELECT '5','8100','ACTIFED','Issued','May 10 2004 12:00AM','5' UNION ALL SELECT '8','8110','AGIOLAX','Receipt','Mar 28 2004 12:00AM','50' UNION ALL SELECT '9','8110','AGIOLAX','Issued','May 5 2004 12:00AM','25' UNION ALL SELECT '11','8112','OTRIVIN CHILD 0.5% NDrops','Receipt','Mar 28 2004 12:00AM','50' UNION ALL SELECT '7','8110','AGIOLAX','Issued','Feb 12 2004 12:00AM','10' UNION ALL SELECT '12','8112','OTRIVIN CHILD 0.5% NDrops','Issued','Apr 1 2004 12:00AM','15' UNION ALL SELECT '2','8100','ACTIFED','Issued','Mar 28 2004 12:00AM','5'--===== Declare some obviously named variablesDECLARE @PrevID INT, @PrevCode INT, @PrevName NVARCHAR(300), @PrevBal FLOAT--===== Do the "quirky" update using a very high speed pseudo-cursor, -- This is very similar to what you would do in a language like "C" except the -- "Read a row/Write a row" is built into the update. UPDATE #TempStk SET @PrevBal = Balance = CASE WHEN Code = @PrevCode AND Name = @PrevName THEN @PrevBal + (Quantity * CASE WHEN Txn_Type = 'Issued' THEN -1 ELSE 1 END) ELSE Quantity * CASE WHEN Txn_Type = 'Issued' THEN -1 ELSE 1 END END, @PrevCode = Code, @PrevName = Name, @PrevID = ID --Just an "anchor", but gotta have it to guarantee things. FROM #TempStk WITH (INDEX(0), TABLOCKX)--===== Display the results SELECT * FROM #TempStk ORDER BY ID[/code]Now, just in case anyone gets any smart ideas about using the clustered index in SELECTs to create an "order" without an ORDER BY... [font="Arial Black"]don't bloody well do it[/font]. Even though it looks like it works, there are times when it doesn't and I'll have the proof in the article I'm rewritting. For now, just take my word for it... the "orderless" ordering ONLY works in the "quirky" UPDATE and ONLY when it's properly formed with all the goodies like the "anchor" and forcing the clustered index scan. The TABLOCKX is to keep people from pulling off an update or delete of the data we're trying to make the running total for. You might think that's a bad thing, but consider this... this method will update a million rows in less than 7 seconds and because it's gonna do the whole table, guess what it's eventually gonna do during those 7 seconds... you guessed it... it's going to lock the whole table, so just get it out of the way and do it up front.I've gotta say it one more time... if you want a correct order out of a SELECT, you MUST use an ORDER BY. But, for the "quirky" UPDATE we did, you can quite literally "bank" on it. ;)If anyone wants to see the million row update to make a running total, you'll just have to wait until I can publish the rewritten article. :hehe:Tue, 03 Mar 2009 21:14:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspxOh, wow. Looks like I may have beat Jeff. But of course, he is probably setting up a million row example to demonstrate his code, where I just took what was presented and went from there. Jeff's code will also be heavily commented as well.[code]--drop table #TempStk CREATE TABLE #TempStk ( [SEQ] INT ,[ID] INT NOT NULL ,[Pricelist] nvarchar(300),[Status] NVARCHAR(50) NULL ,[Date] DATETIME ,[Quantity] INT,[Balance] INT NULL) GO INSERT INTO #TempStk( [SEQ] ,[ID] ,[Pricelist] ,[Status] ,[Date] ,[Quantity] ) values(1,8100,'ACTIFED','Receipt','01/01/2004',100);GOINSERT INTO #TempStk( [SEQ] ,[ID] ,[Pricelist] ,[Status] ,[Date] ,[Quantity] ) values(2,8100,'ACTIFED','Issued','03/28/2004',5);GOINSERT INTO #TempStk( [SEQ] ,[ID] ,[Pricelist] ,[Status] ,[Date] ,[Quantity] ) values(3,8100,'ACTIFED','Issued','04/01/2004',5);GOINSERT INTO #TempStk( [SEQ] ,[ID] ,[Pricelist] ,[Status] ,[Date] ,[Quantity] ) values(4,8100,'ACTIFED','Issued','05/05/2004',5);GOINSERT INTO #TempStk( [SEQ] ,[ID] ,[Pricelist] ,[Status] ,[Date] ,[Quantity] ) values(5,8100,'ACTIFED','Issued','05/10/2004',5);GOINSERT INTO #TempStk( [SEQ] ,[ID] ,[Pricelist] ,[Status] ,[Date] ,[Quantity] ) values(6,8110,'AGIOLAX','Receipt','01/01/2004',50);GOINSERT INTO #TempStk( [SEQ] ,[ID] ,[Pricelist] ,[Status] ,[Date] ,[Quantity] ) values(7,8110,'AGIOLAX','Issued','02/12/2004',10);GOINSERT INTO #TempStk( [SEQ] ,[ID] ,[Pricelist] ,[Status] ,[Date] ,[Quantity] ) values(8,8110,'AGIOLAX','Receipt','03/28/2004',50);GOINSERT INTO #TempStk( [SEQ] ,[ID] ,[Pricelist] ,[Status] ,[Date] ,[Quantity] ) values(9,8110,'AGIOLAX','Issued','05/05/2004',25);GOINSERT INTO #TempStk( [SEQ] ,[ID] ,[Pricelist] ,[Status] ,[Date] ,[Quantity] ) values(10,8112,'OTRIVIN CHILD 0.5% NDrops','Receipt','01/01/2004',200);GOINSERT INTO #TempStk( [SEQ] ,[ID] ,[Pricelist] ,[Status] ,[Date] ,[Quantity] ) values(11,8112,'OTRIVIN CHILD 0.5% NDrops','Receipt','03/28/2004',50);GOINSERT INTO #TempStk( [SEQ] ,[ID] ,[Pricelist] ,[Status] ,[Date] ,[Quantity] ) values(12,8112,'OTRIVIN CHILD 0.5% NDrops','Issued','04/01/2004',15);GOINSERT INTO #TempStk( [SEQ] ,[ID] ,[Pricelist] ,[Status] ,[Date] ,[Quantity] ) values(13,8112,'OTRIVIN CHILD 0.5% NDrops','Issued','05/05/2004',10);GOselect * from #TempStkcreate clustered index IX_RunningBal on #TempStk ( ID, Date);declare @Balance int, @ID int;set @ID = 0;update #TempStk set @Balance = [Balance] = case when @ID <> ts.ID then 0 else @Balance end + (cast(case when [Status] = 'Receipt' then 1 when [Status] = 'Issued' then -1 end as int) * ts.[Quantity]), @ID = ts.[ID]from #TempStk ts with (index = 1)select * from #TempStk;[/code]Tue, 03 Mar 2009 20:53:09 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspx[quote][b]Samuel Vella (2/26/2009)[/b][hr]mh, your solution is fine but when you test it with a lot of data against a cursor, the cursor is orders of magnitude fasterI adapted your query to fit some different test data [font="Courier New"]drop table salesCREATE TABLE Sales (DayCount smallint, channel varchar(50), Sales money)CREATE CLUSTERED INDEX ndx_DayCount ON Sales(DayCount)goDECLARE @DayCount smallint, @Sales money, @channel varchar(50)SET @DayCount = 0SET @Sales = 10set @channel = 'a'WHILE @DayCount < 10000 BEGIN INSERT Sales VALUES (@DayCount,@channel, @Sales) SET @DayCount = @DayCount + 1 SET @Sales = @Sales + 15 set @channel = case when right(cast(@daycount as varchar), 1) = 0 then 'a' when right(cast(@daycount as varchar), 1) = 1 then 'b' when right(cast(@daycount as varchar), 1) = 2 then 'c' when right(cast(@daycount as varchar), 1) = 3 then 'd' when right(cast(@daycount as varchar), 1) = 4 then 'e' when right(cast(@daycount as varchar), 1) = 5 then 'f' when right(cast(@daycount as varchar), 1) = 6 then 'g' when right(cast(@daycount as varchar), 1) = 7 then 'h' when right(cast(@daycount as varchar), 1) = 8 then 'i' when right(cast(@daycount as varchar), 1) = 9 then 'j' end ENDselect * from saleswith cte as( select *, Row_Number() OVER(PARTITION BY channel ORDER BY channel) as RowNum from sales)select a.rownum, a.daycount,a.channel, a.sales, SUM(b.sales ) - a.sales as Balfrom cte aLEFT outer join cte bon a.channel = b.channel and a.RowNum > B.RowNumgroup by a.rownum, a.channel, a.daycount, a.salesorder by a.channel, a.daycountCREATE TABLE #Sales (DayCount smallint, Channel varchar(50), Sales money, RunningTotal money)DECLARE @RunningTotal moneydeclare @old_channel varchar(50)SET @RunningTotal = 0set @old_channel = ''DECLARE rt_cursor CURSORFORSELECT DayCount, Channel, SalesFROM Salesorder by channel, daycountDECLARE @DayCount smallint, @Sales money, @channel varchar(50)OPEN rt_cursorFETCH NEXT FROM rt_cursor INTO @DayCount,@Channel, @SalesWHILE @@FETCH_STATUS = 0 BEGIN SET @RunningTotal = @RunningTotal + @Sales INSERT #Sales VALUES (@DayCount,@channel, @Sales, @RunningTotal) set @old_channel = @channel FETCH NEXT FROM rt_cursor INTO @DayCount,@channel, @Sales if @old_channel <> @channel set @runningtotal = 0 ENDCLOSE rt_cursorDEALLOCATE rt_cursorSELECT * FROM #Sales ORDER BY channel,DayCountDROP TABLE #Sales[/font][/quote]Just an FYI, your cursor routine is going to be blown out of the water shortly. I don't know if Jeff's article has been rewritten yet, but if you want to see the type of code he is putting together I have another article on Running Totals that you may want to check out, as it does contain some of Jeff's code in it as well. You can read my article [url=http://www.sqlservercentral.com/articles/T-SQL/65522/]here[/url].I have a link to Jeff's article below in my signature block regarding Running Totals.Tue, 03 Mar 2009 20:32:36 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspx[quote][b]Samuel Vella (2/24/2009)[/b][hr]The problem with these sorts of calculations is that a row of data in a database is completely independant. It does not know or care about its neighbours and so trying to force a calculation like this is going to force the database engine into a realm it doesn't much care for.[/quote]Actually.... totally untrue if you know how updates with pseudo-cursors actually work... provided a correctly defined clustered index scan comes into play, the order of the update will ALWAYS be in the correct column order described by the clustered index no matter how badly fragmented or split it may be. Despite all the expert opinions against it, not one of them has been able to break a correctly formed update on a running total problem and it's worked since SQL Server was known as "Sybase" and it still works in all versions of SQL Server including 2k8. In fact, they even have a special version of update and, yes, that special version is documented in Books Online. Code comin' up for this problem in a minute... ;)Tue, 03 Mar 2009 20:19:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspxHmmm... there are very few scenarios where I've seen cursors faster than CTE or non-RBAR (row by agonizing row) methods. Just curious how many rows you're running through?Cheers,BrianTue, 03 Mar 2009 09:39:54 GMTbeezellhttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspxmh, your solution is fine but when you test it with a lot of data against a cursor, the cursor is orders of magnitude fasterI adapted your query to fit some different test data [font="Courier New"]drop table salesCREATE TABLE Sales (DayCount smallint, channel varchar(50), Sales money)CREATE CLUSTERED INDEX ndx_DayCount ON Sales(DayCount)goDECLARE @DayCount smallint, @Sales money, @channel varchar(50)SET @DayCount = 0SET @Sales = 10set @channel = 'a'WHILE @DayCount < 10000 BEGIN INSERT Sales VALUES (@DayCount,@channel, @Sales) SET @DayCount = @DayCount + 1 SET @Sales = @Sales + 15 set @channel = case when right(cast(@daycount as varchar), 1) = 0 then 'a' when right(cast(@daycount as varchar), 1) = 1 then 'b' when right(cast(@daycount as varchar), 1) = 2 then 'c' when right(cast(@daycount as varchar), 1) = 3 then 'd' when right(cast(@daycount as varchar), 1) = 4 then 'e' when right(cast(@daycount as varchar), 1) = 5 then 'f' when right(cast(@daycount as varchar), 1) = 6 then 'g' when right(cast(@daycount as varchar), 1) = 7 then 'h' when right(cast(@daycount as varchar), 1) = 8 then 'i' when right(cast(@daycount as varchar), 1) = 9 then 'j' end ENDselect * from saleswith cte as( select *, Row_Number() OVER(PARTITION BY channel ORDER BY channel) as RowNum from sales)select a.rownum, a.daycount,a.channel, a.sales, SUM(b.sales ) - a.sales as Balfrom cte aLEFT outer join cte bon a.channel = b.channel and a.RowNum > B.RowNumgroup by a.rownum, a.channel, a.daycount, a.salesorder by a.channel, a.daycountCREATE TABLE #Sales (DayCount smallint, Channel varchar(50), Sales money, RunningTotal money)DECLARE @RunningTotal moneydeclare @old_channel varchar(50)SET @RunningTotal = 0set @old_channel = ''DECLARE rt_cursor CURSORFORSELECT DayCount, Channel, SalesFROM Salesorder by channel, daycountDECLARE @DayCount smallint, @Sales money, @channel varchar(50)OPEN rt_cursorFETCH NEXT FROM rt_cursor INTO @DayCount,@Channel, @SalesWHILE @@FETCH_STATUS = 0 BEGIN SET @RunningTotal = @RunningTotal + @Sales INSERT #Sales VALUES (@DayCount,@channel, @Sales, @RunningTotal) set @old_channel = @channel FETCH NEXT FROM rt_cursor INTO @DayCount,@channel, @Sales if @old_channel <> @channel set @runningtotal = 0 ENDCLOSE rt_cursorDEALLOCATE rt_cursorSELECT * FROM #Sales ORDER BY channel,DayCountDROP TABLE #Sales[/font]Thu, 26 Feb 2009 02:10:23 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspxHow about this one?with cte as( select *, Row_Number() OVER(PARTITION BY ID ORDER BY ID) as RowNum from #TempStk)select a.rownum, a.id, a.status, a.date, a.quantity, CASE WHEN a.Status <> 'Receipt' THEN SUM(case when b.Status = 'Receipt' Then b.quantity Else -b.quantity END) - a.quantity ELSE ISNULL(SUM(case when b.Status = 'Receipt' Then b.quantity Else -b.quantity END),0) + a.quantity END as Balfrom cte aLEFT outer join cte b on a.id = b.id and a.RowNum > B.RowNumgroup by a.rownum, a.id, a.status, a.date, a.quantityorder by a.id, a.rownumWed, 25 Feb 2009 16:52:47 GMTMNHhttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspxThe problem with these sorts of calculations is that a row of data in a database is completely independant. It does not know or care about its neighbours and so trying to force a calculation like this is going to force the database engine into a realm it doesn't much care for.Best solution is to work out the running total in the application that takes the data, be it reporting services, excel or whatever. Failing that then this is one of those cases where, from a database perspective, a cursor will be the best solution.There are other solutions which avoid cursor use, however they have their own draw backs and are not always very efficient.Try running both the set based and cursor solutions with your own data and see which you prefer(TIP: when using the cursor solution make sure you filter the select statement that forms the cursor declaration, not the select on the temp table used to create the output)Tue, 24 Feb 2009 06:11:27 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspxAnother RUNNING TOTAL problem.., See these articles on how to achieve this...http://www.sqlservercentral.com/articles/Advanced+Querying/61716/http://www.sqlteam.com/article/calculating-running-totalsThu, 19 Feb 2009 02:44:02 GMTRamesh Saivehttp://www.sqlservercentral.com/Forums/Topic660107-1291-1.aspxI have a table as shown below and would like to have the balance quantity which should be dynamically calucalted depending upon receipt(as *1)/issued(as *-1)ID Code Name Txn_Type Date Qty dsiredOutput(BAL) 1 8100 ACTIFED Receipt 1-Jan-04 100 1002 8100 ACTIFED Issued 28-Mar-04 5 953 8100 ACTIFED Issued 1-Apr-04 5 904 8100 ACTIFED Issued 5-May-04 5 855 8100 ACTIFED Issued 10-May-04 5 806 8110 AGIOLAX Receipt 1-Jan-04 50 507 8110 AGIOLAX Issued 12-Feb-04 10 408 8110 AGIOLAX Receipt 28-Mar-04 50 909 8110 AGIOLAX Issued 5-May-04 25 6510 8112 OTRIVIN Receipt 1-Jan-04 200 20011 8112 OTRIVIN Receipt 28-Mar-04 50 25012 8112 OTRIVIN Issued 1-Apr-04 15 23513 8112 OTRIVIN Issued 5-May-04 10 225PLEASE NOTE ID COLUMN is an IDENTITY column.I am attaching a sample script.Please some help please.Thu, 19 Feb 2009 01:49:35 GMTmathewspsimon