Lowest of the two values... - 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)

Lowest of the two values...

Author

Topic

Nick
Posting Yak Master

155 Posts

Posted - 2002-01-11 : 10:10:55

Here is the challenge.. I have three tables.. Each with the same two columns (itemID, qty)... In two of the tables I want to compare equal itemID's quantities. I would like to put the itemID and LOWEST quantity into the third table.. For Example:

If I had
Table 1:
item-1 42

Table 2:
item-1 75

I would want to put
item-1 42

into the third table. I need to do this for each item that shows up in the first table. If an item is in table 2, but not table one, it does not get put into the third table. No item can appear in table one that does not appear in table two.

Suggestions?

izaltsman
A custom title

1139 Posts

Posted - 2002-01-11 : 10:22:35

I think this should do it:


INSERT INTO table3
SELECT t1.itemID, 
     CASE WHEN t1.qty<t2.qty THEN t1.qty ELSE t2.qty END
FROM 
     table1 t1 INNER JOIN table2 t2
        ON t1.itemID = t2.itemID

Nick
Posting Yak Master

155 Posts

Posted - 2002-01-11 : 10:31:39

Thank you very much. I also need to change the value of qty in table 2. It should be t2.qty-t1.qty or zero, which ever is greater. Would I do this with a seperate update statement with a case???

Thanks again!

quote:

I think this should do it:


INSERT INTO table3
SELECT t1.itemID, 
     CASE WHEN t1.qty<t2.qty THEN t1.qty ELSE t2.qty END
FROM 
     table1 t1 INNER JOIN table2 t2
        ON t1.itemID = t2.itemID

izaltsman
A custom title

1139 Posts

Posted - 2002-01-11 : 11:08:21

Yes, an UPDATE statement using CASE would work.

Nick
Posting Yak Master

155 Posts

Posted - 2002-01-11 : 11:11:37

And, am I to assume that If I put both of those statements withing a

BEGIN TRANSACTION...COMMIT TRANSACTION, there will be no chance for the data to get screwed up by somebody changing the value of the qty in either table1 or table2?? I guess I'm asking if transactions lock access to the database or not..

Thanks so much!

quote:

Yes, an UPDATE statement using CASE would work.

Nazim
A custom title

1408 Posts

Posted - 2002-01-11 : 11:17:53

Its a good idea to put your code in a block

begin trans

INSERT INTO table3 SELECT t1.itemID, CASE WHEN t1.qty<t2.qty THEN t1.qty ELSE t2.qty ENDFROM table1 t1 INNER JOIN table2 t2 ON t1.itemID = t2.itemID

update t2 set t2.qty =abs(isnull(t1.qty-t2.qty,0)) FROM table2 t2 INNER JOIN table1 t1 ON t1.itemID = t2.itemID

end trans

HTH

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Edited by - Nazim on 01/11/2002 11:20:40

Nick
Posting Yak Master

155 Posts

Posted - 2002-01-11 : 11:32:31

Much Thanks!

quote:

Its a good idea to put your code in a block

begin trans

INSERT INTO table3 SELECT t1.itemID, CASE WHEN t1.qty<t2.qty THEN t1.qty ELSE t2.qty ENDFROM table1 t1 INNER JOIN table2 t2 ON t1.itemID = t2.itemID

update t2 set t2.qty =abs(isnull(t1.qty-t2.qty,0)) FROM table2 t2 INNER JOIN table1 t1 ON t1.itemID = t2.itemID

end trans

HTH

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Edited by - Nazim on 01/11/2002 11:20:40

izaltsman
A custom title

1139 Posts

Posted - 2002-01-11 : 12:22:35

There is a couple of issues with Nazim's code.

1. begin trans/end trans isn't gonna work. Should be BEGIN TRAN/COMMIT TRAN. And it isn't always a good idea to do this -- only when you need to make sure that several statements are executed as a single unit of work. If you need to make sure that no one gets to update the data in table1 and table2 for the full duration of your transaction you have to use HOLDLOCK hint on your first statement -- otherwise SQL Server will release the shared locks as soon as it is done reading the data from tables 1 and 2.
2. If you are going to do things in a transaction don't forget to put error traps after your insert and update statements (check @@ERROR and ROLLBACK).
3. And if I understood the requirements for the UPDATE statement correctly, the ABS function isn't gonna work for you... You'd want something like:

 
UPDATE t2 
SET t2.qty = CASE WHEN t2.qty-t1.qty < 0 THEN 0 ELSE t2.qty-t1 END 
FROM  	table2 t2 
	INNER JOIN table1 t1    
		ON t1.itemID = t2.itemID

Nazim
A custom title

1408 Posts

Posted - 2002-01-11 : 23:32:45

Thanx, Ilya for correcting it. In UAE weekends are on thursday and Fridays in those holidays mood just screwed up the whole thing(. will be very careful next time when am giving a solution.

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources