August 9, 2009 at 2:48 pm
I generate a table with the current inventory in and out information. The columns are:
Seq is the sequence ID of the row. This is used when the table is extracted to Excel etc so items can be un-sorted. Also it is used for tracking the rows while calculating inventory.
Part is the part number of the item
Qty is the current transaction quanity
InOut is the mulitplier for the Qty column (-1 or 1)
TranDate is the date of the transaction.
OHplusA is the On Hand plus Alloctaed quantity the part at the time of this transaction.
Net is the net quantity of the part in stock after this transaction.
When the table is created only the first occurence of each part in the table has the OHplusA and Net values set. All others are set to zero. When my update query runs I update the OHplusA with the Net value from the previous row. I then multiply InOut by Qty and add this to the OHplusA value and place it in the Net column.
This is the table format:
CREATE TABLE [dbo].[plantable](
[Seq] [int] IDENTITY(1,1) NOT NULL,
[InOut] [int] NOT NULL,
[Part] [char](20) NOT NULL,
[Qty] [int] NULL,
[TranDate] [datetime] NOT NULL,
[OHplusA] [int] NOT NULL,
[Net] [int] NOT NULL
)
I run this update query on the table. It assumes the table is sorted by Seq.
UPDATE t1
SET t1.OHPlusA = t2.Net, t1.Net = t2.Net + (t1.InOut*t1.Qty)
FROM plantable AS t1 inner JOIN
plantable AS t2 ON (t1.Seq - 1) = t2.Seq and t1.Part = t2.Part
This gives me the following results (I can't get this to format properly on the page so it's a little hard to match columns):
SeqInOutPartQtyTranDate OHplusANet
1-110013 102009-08-259383
2-110013 82009-08-288375
3-110013 302009-09-280-30
4-110013 322009-09-300-32
5110039 2002009-04-059451145
6-110039 152009-09-2511451130
7110040 5002009-07-248881388
8-110040 22009-08-1113881386
9-110040 202009-08-120-20
10-110040 102009-08-250-10
11-110040 252009-08-310-25
12110040 5002009-09-040500
13-110040 142009-09-090-14
14-110040 252009-09-100-25
15-110040 302009-09-280-30
16-110040 322009-09-300-32
17-110040 252009-10-080-25
I highlighted the first row for each part. This first row for an item is untouched as it should be. The second row for an item has the correct values. But the rest of the rows for the part lose the previous rows net value so end up with wrong values. I guess that's because it doesn't record the updates until the query is done. I tried in vain for hours to set up a recursive query and failed miserably. Can anyone help me get this to work as a recursive query in SQL Server 2000?
August 9, 2009 at 4:47 pm
RichSmith (8/9/2009)
Can anyone help me get this to work as a recursive query in SQL Server 2000?
Nope... but I can show you the right way. 😉 Gimme a couple minutes and I'll be back.
By the way... what is the clustered index on this table?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2009 at 6:01 pm
First of all, take a look at the article at the first link in my signature line at the bottom of this post. Then take a look at the test data I built into the following code. If you want really good, fully tested answers in a hurry, take the time to format the code that way so that the data is readily consumable. Many folks will simply skip a request like yours if it doesn't have such code.
Next, the code I'm using is controversial. Many other MVP's don't trust it and others condem it but... none of them have been able to make it break if they follow the rules. If that makes you nervous, the only other way to do a "running total" in SQL Server like what you want is to use a Cursor or While Loop. Except to demo how slow RBAR Cursors and While Loops are, I won't write one that someone may put into production.
Also, I'm making an assumption because you didn't say... I'm going to assume that the SEQ column isn't always going to depict the correct order by Part and TranDate... if it does and its the clustered index as well, then you can just drop the part in the code that creates the clustered index and you can ignore the comments about making a Temp Table.
Here's the code... most of what you need to know is in the comments. This technique will correctly update a million rows in less than 7 seconds provided that the clustered index is correct. If you make any changes to the code, you will cause it to give incorrect answers as well as destroying the data in the OHplusA for the starter rows for each Part. That includes references to the @Dummy variable. I know it goes without saying, but never test code on original data, period.
--===== Create a demo table in a nice safe place.
-- This is not a part of the solution unless the original
-- table doesn't have the required clustered index.
USE TempDB
IF OBJECT_ID('TempDB.dbo.PlanTable','U') IS NOT NULL
DROP TABLE dbo.PlanTable
GO
CREATE TABLE dbo.PlanTable
(
Seq INT IDENTITY(1,1),
InOut INT NOT NULL,
Part CHAR(20) NOT NULL,
Qty INT NULL,
TranDate DATETIME NOT NULL,
OHplusA INT NOT NULL,
Net INT NOT NULL
)
--===== This index is absolutely essential to the problem.
-- If the clustered index is already used, then you need
-- to copy the data to a temp table and work on it there
-- with this clustered index on that table.
CREATE CLUSTERED INDEX IXC_PlanTable_RunningTotal
ON dbo.PlanTable (Part,TranDate,Seq)
--===== Populate the demo table with data.
SET IDENTITY_INSERT dbo.PlanTable ON
INSERT INTO dbo.PlanTable
(Seq,InOut,Part,Qty,TranDate,OHplusA,Net)
SELECT '1','-1','10013', '10','2009-08-25', '93', '0' UNION ALL --starter
SELECT '2','-1','10013', '8','2009-08-28', '0', '0' UNION ALL
SELECT '3','-1','10013', '30','2009-09-28', '0', '0' UNION ALL
SELECT '4','-1','10013', '32','2009-09-30', '0', '0' UNION ALL
SELECT '5', '1','10039','200','2009-04-05','945', '0' UNION ALL --starter
SELECT '6','-1','10039', '15','2009-09-25', '0', '0' UNION ALL
SELECT '7', '1','10040','500','2009-07-24','888', '0' UNION ALL --starter
SELECT '8','-1','10040', '2','2009-08-11', '0', '0' UNION ALL
SELECT '9','-1','10040', '20','2009-08-12', '0', '0' UNION ALL
SELECT '10','-1','10040', '10','2009-08-25', '0', '0' UNION ALL
SELECT '11','-1','10040', '25','2009-08-31', '0', '0' UNION ALL
SELECT '12', '1','10040','500','2009-09-04', '0', '0' UNION ALL
SELECT '13','-1','10040', '14','2009-09-09', '0', '0' UNION ALL
SELECT '14','-1','10040', '25','2009-09-10', '0', '0' UNION ALL
SELECT '15','-1','10040', '30','2009-09-28', '0', '0' UNION ALL
SELECT '16','-1','10040', '32','2009-09-30', '0', '0' UNION ALL
SELECT '17','-1','10040', '25','2009-10-08', '0', '0'
SET IDENTITY_INSERT dbo.PlanTable OFF
--===== Create and preset some obviously named variables for the running totals
DECLARE @PrevPart CHAR(20),
@PrevNet INT,
@Dummy INT
SELECT @PrevPart = 0,
@PrevNet = 0
--===== Do a magic little update known as the "quirky update"
-- or "pseudo-cursor" update. If you make any changes,
-- you will get incorrect results and possibly destroy
-- the original data in the OHplusA column for the
-- starter rows for each Part.
UPDATE pt
SET @Dummy = OHplusA = CASE
WHEN Part = @PrevPart
THEN @PrevNet
ELSE OHplusA
END,
@PrevNet = Net = CASE
WHEN Part = @PrevPart
THEN @PrevNet + (InOut*QTY)
ELSE OHplusA + (InOut*QTY)
END,
@PrevPart = Part --Provides an "anchor" for 2k5 compatability
FROM dbo.PlanTable pt WITH (TABLOCKX)
OPTION (MAXDOP 1)
--===== Display the results
SELECT * FROM dbo.PlanTable
Let me know if you have any questions and remember... test this on separate data first. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2009 at 7:00 pm
This worked beautifully. The results of the query were exactly what was expected. Thank you so much. Now, I need to go through it all and figure out what's happening (and why it works). I modified the clustered index order because the Seq column is very important (for very messy legacy reasons I won't go into). I've been working with SQL Server for a couple years. But an expert I'm not. I've always gotten by with Googling a problem. This was my first stumper.
Thanks again.
August 9, 2009 at 7:19 pm
RichSmith (8/9/2009)
This worked beautifully. The results of the query were exactly what was expected. Thank you so much. Now, I need to go through it all and figure out what's happening (and why it works). I modified the clustered index order because the Seq column is very important (for very messy legacy reasons I won't go into). I've been working with SQL Server for a couple years. But an expert I'm not. I've always gotten by with Googling a problem. This was my first stumper.Thanks again.
Thanks for the feedback. I'm actually reworking a previously published article on this subject.
The keys are knowing how to use the 3 part SET statements of SET @variable = column = expression, when they should only be two part SET @variable = column or column = expression, and understanding the fact that a Joinless UPDATE will always update in the same order as the clustered index.
Other than that, it works pretty much a like any program would work... read a row, update the running total variables from that row, write the updates to the row, read the next row, etc, etc. Behind the scenes, SQL Server really runs on loops just like any file handling program would run.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply