September 12, 2007 at 9:42 am
I need to update one table based on info from a second table. I'm trying to figure a way to do this without a cursor or without looping in the front end but haven't figued out a way yet. They are scanning items that will need to be relieved from inventory. A worktable holds each item scanned so there can be multiples of each item. They wanted to be able to scan x times to enter items, not scan once and enter # of items.
Sample data from workTable
ID WorkReq Partno Date
1 123 02-FIT-1 9/12/2007
2 123 02-FIT-1 9/12/2007
3 123 02-BRG-123 9/12/2007
4 123 02-BRG-432 9/12/2007
5 123 02-BRG-432 9/12/2007
6 123 02-BRG-432 9/12/2007
Here's select from first table that will need to be relieved from I_master table.
Select partno, count(*)
from workTable
where workReq = @workreq
and dateAdded = @dateAdd
Above query would return
02-FIT-1, 2
02-BRG-123, 1
02-BRG-432, 3
Here's the update statment I have so far
Update im
set im.qty_on_hand = case
when im.qty_on_hand - 1 < 0 then 0
else im.qty_on_hand - 1
end
from I_master im
right outer join worktable wt on im.inv_ID = wt.partno
where wt.workrequest = '654798'
and CONVERT(varchar, wt.DateAdded, 101) = '09/11/2007'
If subtracting any of qty's results in a negative number set it to 0 (I know inventory will be off at some point, inventory in maintenance shops tend to grow legs, that's part of the reason behind this project so they can track that.) I don't want negative inventory.
My above query is only subtracting one from the I_master qty_on_hand for each part even though I have multiple items in worktable for that part number in the worktable. I need to subtract one for each entry in the worktable. What am I missing here?
Thanks for your help
Thanks
September 12, 2007 at 9:56 am
UPDATE i
SET qty_on_hand = qty_on_hand - (SELECT COUNT(*) FROM @workTable w WHERE w.partno = i.inv_ID)
FROM @I_master i
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 12, 2007 at 9:57 am
Oh, and whatever you need in the WHERE clause.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 12, 2007 at 10:08 am
Why do you join inv_id with partnumber?
My above query is only subtracting one from the I_master qty_on_hand for each part even though I have multiple items in worktable for that part number in the worktable. I need to subtract one for each entry in the worktable. What am I missing here?
That is because you subtract 1 in your query not the count.
Regards,
gova
September 12, 2007 at 10:09 am
try this
SET NOCOUNT ON
DECLARE @workTable TABLE
(
[ID] INT,
WorkReq VARCHAR(16),
Partno VARCHAR(16),
dateAdded DATETIME
)
INSERT @workTable
SELECT 1, '123', '02-FIT-1', '9/12/2007' UNION
SELECT 2, '123', '02-FIT-1', '9/12/2007' UNION
SELECT 3, '123', '02-BRG-123', '9/12/2007' UNION
SELECT 4, '123', '02-BRG-432', '9/12/2007' UNION
SELECT 5, '123', '02-BRG-432', '9/12/2007' UNION
SELECT 6, '123', '02-BRG-432', '9/12/2007'
DECLARE @I_master TABLE
(
Partno VARCHAR(16),
qty_on_hand INT
)
INSERT @I_master
SELECT '02-BRG-432', 2 UNION
SELECT '02-BRG-123', 5 UNION
SELECT '02-FIT-1', 25
/*
SELECT Partno, COUNT(*) QtyOut
FROM
@workTable
WHERE
workReq = @workreq
AND dateAdded = @dateAdd
GROUP BY Partno
Partno QtyOut
02-BRG-123 1
02-BRG-432 3
02-FIT-1 2
*/
DECLARE @workreq VARCHAR(16)
DECLARE @dateAdd DATETIME
SET @workreq = '123'
SET @dateAdd = '9/12/2007'
SELECT * FROM @I_master
UPDATE im
SET im.qty_on_hand = CASE
WHEN im.qty_on_hand - QtyOut < 0 THEN 0
ELSE im.qty_on_hand - QtyOut
END
FROM
@I_master im
JOIN
(
SELECT Partno, COUNT(*) QtyOut
FROM
@workTable
WHERE
workReq = @workreq
AND dateAdded = @dateAdd
GROUP BY Partno) O
ON
im.Partno = O.Partno
SELECT * FROM @I_master
Regards,
gova
September 12, 2007 at 10:15 am
Sorry, I missed the part about making negative results equal 0.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 12, 2007 at 10:17 am
inv_id and partno are are what tie the two tables together. In I_master table, the INV_ID is our internal part number and key for the table. The partno can be i the workTable multiple times for that workrequest. Hope that answers that.
Thanks
September 12, 2007 at 10:20 am
I thought it is InvoiceID or something. Modify the table @I_master in my previous post as Inv_id instead of part number.
Regards,
gova
September 12, 2007 at 10:21 am
Thanks gova, I'll give it a shot and let you know how it works out. Thanks for your input too Jason.
Thanks
September 12, 2007 at 10:26 am
Jason's query is better it gives query cost 27.74 % and mine gives 36.18 %
Regards,
gova
September 12, 2007 at 10:30 am
Yeah, but I don't account for negative values .....
Gimmie a minute though .....
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 12, 2007 at 10:42 am
This will work ....
UPDATE i
SET qty_on_hand =
CASE WHEN qty_on_hand - (SELECT COUNT(*) FROM @workTable w WHERE w.partno = i.inv_ID) < 0 THEN 0
ELSE qty_on_hand - (SELECT COUNT(*) FROM @workTable w WHERE w.partno = i.inv_ID) END
FROM @I_master i
And according to my system gova's query is just a TAD more costly with a subtree cost of .044 and mine of .019
Now the interesting thing is that my query has no memory grant and the other has a value of 64. Can anyone reading this post give direction (links) to learn more about Memory Grant. BOL is pretty vague. and what does it mean exactly to not have a value for Memory Grant?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 12, 2007 at 11:43 am
I (you) got it working guys, thanks for all your help.
Allen
Thanks
September 12, 2007 at 12:00 pm
Jason -
Intersetingly your query costs double with the case. I guess that is due to double correlated queries with the case statement.
As of now it costs little more than mine.
Regards,
gova
September 12, 2007 at 6:42 pm
Allen,
as I can understand your workTable contains expense records: 1 record per item gone from the stock.
There must be another table holding same information about income.
There must be some another procedure which updates I_master when new items arrive.
And table I_master contains current qty for each part on stock.
Is it right?
If this is the case I would suggest to throw away updates and replace table I_master with a view:
SELECT I.PartNo, SUM(I.Quantity) - ISNULL(E.Spent, 0) as qty_on_hand
FROM dbo.StockIncome I
LEFT JOIN (SELECT PartNo, COUNT(ID) as Spent from dbo.worktable group by PartNo ) E ON I.PartNo = E.PartNo
GROUP BY I.PartNo, E.Spent
If you'll index this view it will materialize into a table.
It will speed up SELECTs but slow down inserts into source tables.
See what's more important for you.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply