Update query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update query

Author

Topic

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-03-24 : 09:35:04

CREATE TABLE [ProdLog] (
	[ProdLevel] [tinyint] NOT NULL , -- what level in are we at
	[ProdId] [bigint] NOT NULL , -- id from the 'productionstep'
	[ArticleId] [int] NOT NULL , -- id of article used/produced
	[Input_Output] [char] (1) , -- is article in or output in productionstep
	[Percentage] [float] NOT NULL , -- percentage of particular article in step
	[PrevId] [bigint] NOT NULL, -- which step is this article made of
	[Amount] [float] NOT NULL  -- amount of article
	
) 

INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (1,11693,7,2,1,0,0)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (1,11693,302,1,0.47,0,11544)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (1,11693,6,1,0.53,0,11545)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (2,11545,6,2,1,0,0)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (2,11544,302,2,1,0,0)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (2,11544,224,1,1,0,11455)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (2,11545,228,1,1,0,11456)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (3,11455,224,2,1,0,0)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (3,11456,228,2,1,0,0)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (3,11456,1,1,1,0,0)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (3,11455,21,1,1,0,0)

Every distinct 'ProdId' is a step in our production. Every 'ArticleId' is an article
used in the 'productionstep'. An Article can be Input in the step, or output. When there
are multiple articles used as input 'percentage' indicates how many of each article is used.

When the production process is finished, the following update statement is run:



UPDATE 	ProdLog
SET 	Amount = 450 * Percentage 
WHERE 	ProdId = 11693 and Input_Output = 'O'

This means the 'last' step in the production process (lowest ProdLevel) has an
output amount of 450 kgs. This was made up of 47% article 302 and 53% article 6.
I need to insert the amount of every article of the table.
PrevId and ArticleId can be used as a pointer to the previous step in the process

If anyone can think of a solution, or give me some pointers in the right direction,
I'd really appreciate it! Can this be done without a cursor?

Thanks in advance

Peter

p.s. the real situation is a bit more complex, but I hope I can figure that myself when I get this to work. I didn't want to confuse you more than this...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-24 : 13:50:42

Thanks for the DDL ... i am still quite confused .. can you post what the desired outcome is? I am not following exactly what you are tying to accomplish.

Also, you state in your where clause "WHERE Input_Output = 'O'" but in your data, that field has 1's and 2's.

Give me a little more info and what you expect to see with your sample data and I'll take a look.

- Jeff

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-03-25 : 02:40:01

Sorry about the mixup between 1 and 2 and I and O. 2 stands for 'O'utput, 1 stands for 'I'nput.

I'll try to explain what's going on.

Please take a look at the first three INSERT statements. They have all the same 'ProdId', this

means this is one 'step' in our production process. This particular step has Id 11693. This

step has 2 input articles (6 & 302) that are made into one new article (7). This article is the

final output of the productionprocess.

As I said, the new article 7 consist of two articles, 6 (for 53%) and 302 (for 47%). The update

statement I showed in the first post sets the output KGs for article 7 to 450. This means

there's made 450KGs of article 7.

Now I know this, I know how many of each input article was needed:

Article 302: 450 * 47% = 211.50
Article 6: 450 * 53% = 238.50

When you take a look at the second insert statement, you see a 'PrevId' of 11544. This means

the ArticleId 302 was made in step 11544 (it was output in that particular step). So I know the

'amount' for ProdId 11544, Article 302, Input_Output = 2.

I could use the following statement:

UPDATE ProdLog
SET Amount = 211.50
WHERE ProdId = 11544 AND ArticleId = 302 AND Input_Output = 2 *)

When I know this, I can set the amount of input articles in step 11544:

UPDATE ProdLog
SET Amount = 211.50 * Percentage
WHERE ProdId = 11544 AND Input_Output = 1

And this needs to be done until I'm 'done'
(done = there's no record anymore where Input_Output = 1 and PrevId > 0)

The following set of insert statements gives the desired output:



DELETE FROM ProdLog

INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (1,11693,7,2,1,450,0)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (1,11693,302,1,0.47,211.50,11544)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (1,11693,6,1,0.53,238.50,11545)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (2,11545,6,2,1,238.50,0)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (2,11544,302,2,1,211.50,0)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (2,11544,224,1,1,211.50,11455)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (2,11545,228,1,1,238.50,11456)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (3,11455,224,2,1,238.50,0)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (3,11456,228,2,1,211.50,0)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (3,11456,1,1,1,211.50,0)
INSERT INTO ProdLog (ProdLevel, ProdId, ArticleId, Input_Output,Percentage, Amount, PrevId)
       VALUES (3,11455,21,1,1,238.50,0)

An image of the process can be found here: www.heiploeg.com/download/Image.JPG maybe that makes it a little more clear.

I hope I explained it good enough, I really appreciate your help!

Thanks

Peter

p.s. One more thing: in this examples the amount for each leaf (?) is the same, however this is not always the case. It's very well possible that a certain step has a total input of 100 KGs and an output of 110Kgs, but I didn't want to confuse you anymore.

*) I know I don't need both ArticleId and ProdId in this example, but I do need it in the 'real' situation.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-26 : 10:10:15

So, you are updating the output value ... and after doing that, you would like to have the inputs for that output calculated and updated accordingly?

First, update all of your ouputs as you know how.

Then, run this:

UPDATE ProdLog
SET Amount = Percentage *
(SELECT SUM(Amount) FROM ProdLog A WHERE A.ProdID = ProdLog.ProdID and A.Input_OutPut = 2)
WHERE
ProdLog.Input_OutPut = 1

Does that do what you want? Just run this every time any output amounts have been updated, and it should update all of the input amounts. If this isn't exactly what you need, it should give you a good starting point.

- Jeff

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-03-27 : 02:14:32

Thanks Jeff for your reply.

It's not exactly what I meant, but I'll try to work from here.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-03-28 : 01:58:57

Peter,

I am assuming here that Input_Output = 2 is a unique condition for each ProdID.

AND that you already have least 1 Amount (Your initial update of amount to 450..

Here is a looping solution that requires 2 distinct UPDATE statements... It "seems" to work


WHILE EXISTS (Select 1 from ProdLog where Amount = 0.0)
BEGIN

UPDATE P
SET Amount = (Select Amount from ProdLog where ProdID = P.ProdID and Input_OutPut = 2) * Percentage
FROM ProdLog P
Where Input_OutPut = 1 and Amount = 0.0

UPDATE P
SET Amount = COALESCE((Select Amount from ProdLog where P.ProdID = PrevID and ArticleID = P.ArticleID) * Percentage,0)
FROM ProdLog P
Where  Input_OutPut = 2 and Amount = 0.0

END

DavidM

"SQL-3 is an abomination.."

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-03-28 : 02:16:15

Hmm, David you seem to be on track!

It works for the data I posted, unfortunately there are more complicated situations. I'll try if I can come up with a solution.

I'll post back here next week (leaving early today for weekend

)

Thanks again!

Peter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources