February 4, 2010 at 4:17 pm
I need to create a stored procedure that basically looks at parts in our inventory that need to be reordered, checks to see if any of those parts have alternate parts in inventory, if so it changes the reorder point, if not it checks a different stock location and depending on the results it could also change reorder points.
Basically i have a select statement that I need to pull some information from then run some logic against it then either run a update statement or insert statement or possibly pass it into another sql statement to and check the data to see if i need to insert or update.
I am pretty sure I can do this in asp without much of a problem but i'm kind of new to t-sql and i've looked into cursors and even played around with them a little bit but i've read that they are also maybe not that efficient. I was curious if there was another way I could pull this off? Any help would be appreciated.
Thanks
February 4, 2010 at 4:25 pm
mbender (2/4/2010)
Basically i have a select statement that I need to pull some information from then run some logic against it then either run a update statement or insert statement or possibly pass it into another sql statement to and check the data to see if i need to insert or update.
There isn't much to go on here but there's nothing in your description that suggests you need a cursor. You can INSERT or UPDATE from a SELECT statement and you can make that conditional by using a WHERE clause or possibly an IF statement.
If you are new to SQL Server then steer clear of cursors. 99.9% of the time they are not a necessary or good solution for data manipulation tasks. Until you are expert enough to know otherwise it's best to assume that you will not need to use cursors.
February 4, 2010 at 6:18 pm
What he said.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 4, 2010 at 6:45 pm
here is an example of what i need to be able to do:
Select partnumber, qty, reorder, location from inventorytable
I need to pull the partnumber out of this table then look it up in this alternate parts table
Select alternatepart from alternatepartstable where alternatepart = partnumber
Then if the partnumber has an alternatepart then check to see if i have any instock
Select QoH from StockTable where StockPartNumber = alternatepart
If I do have any in stock then I need to update the reorder point for partnumber and check to see if alternatepart has a reorder point, if it doesn't then i need to set one.
If there are no alternate parts in local stock then I need to check our distrib center and if they have any instock then I need to update the reorder point for partnumber and check to see if alternatepart has a reorder point, if it doesn't then i need to set one.
I need to rinse and repeat this x number of times depending on how many parts i have to reorder.
I don't think I can do this in a conditional where, i could be wrong. I hope this helps shed a little more light.
February 5, 2010 at 3:48 am
Try not to think of it in terms of steps or repeating things X number of times. Instead think in sets. Think about the joins and end results you want.
The "optional" parts of the query are outer joins. You can create a query like this to return the relevant columns.
SELECT i1.partnumber, i1.qty, i1.reorder, i1.location
FROM inventorytable i1
LEFT JOIN alternatepartstable a
ON i1.partnumber = a.partnumber
LEFT JOIN inventorytable i2
ON a.alternatepart = i2.partnumber;
This query is updateable so you can put it in a MERGE statement or in a view and update the reorder values directly (you don't say what you want to update them to). It's possible that the whole thing could be done with one statement.
February 5, 2010 at 7:07 am
Setting the reorder point can either be to 0 or to 1 it depends on the results. I'm not quite sure I understand if i can pull this off in one statement(which would be awesome btw if i could) because I could have either 1 result or multiple results in the alternate table, also depend on what i find i might have to look other places
February 5, 2010 at 7:15 am
This could be done without a cursor. What would help us help you better would be if you could provide us with a setup that reflects the problem at hand. This would be table def(s) (CREATE TABLE statement for the table(s) involved), sample data (as a series of INSERT INTO statements for the table(s) involved), expected results based on the sample data to test against.
All of this does not have to contain actual company data or table structures as long as it properly reflects the problem.
February 5, 2010 at 11:39 am
Ok here is I believe what your asking for, this a simple version of the data.
Create Table InventoryThatNeedsReorders
(PartNumber nvarchar(50),
Location nvarchar(50),
Quantity int,
ReorderPoint int)
INSERT INTO InventoryThatNeedsReorders
(PartNumber, Location, Quantity)
VALUES ('Widget 123', N'GrandRapids', 2, 1)
INSERT INTO InventoryThatNeedsReorders
(PartNumber, Location, Quantity)
VALUES ('Widget 456', N'Chicago', 2, 1)
INSERT INTO InventoryThatNeedsReorders
(PartNumber, Location, Quantity)
VALUES ('Widget 457', N'Detroit', 2,1)
Create Table AlterNateParts
(PartNumber nvarchar(50),
AlternatePart nvarchar(50))
INSERT INTO AlterNateParts
(PartNumber, AlternatePart)
VALUES ('Widget 457', N'Widget 767')
INSERT INTO AlterNateParts
(PartNumber, AlternatePart)
VALUES ('Widget 456', N'Widget 743')
Create Table CurrentInventoryTable
(PartNumber nvarchar(50),
Location nvarchar(50),
CurrentQtyOnHand int)
INSERT INTO CurrentInventoryTable
(PartNumber, Location, CurrentQtyOnHand)
VALUES ('Widget 743', N'Chicago', 2)
INSERT INTO CurrentInventoryTable
(PartNumber, Location, CurrentQtyOnHand)
VALUES ('Widget 767', N'Detroit', 2)
INSERT INTO CurrentInventoryTable
(PartNumber, Location, CurrentQtyOnHand)
VALUES ('Widget 123', N'Distribution', 20)
Select PartNumber, Location, Quantity from InventoryThatNeedsReorders Where ((Quantity-ReorderPoint)>=1)
So what I need to do is for all the parts that are in the InventoryThatNeedsReorders table, I need to look to see if they have an alternate part from the AlterNateParts table, if they do then i need to check CurrentInventoryTable to see if we have stock if so I need to update the reorder point in the InventoryThatNeedsReorders to 0 and if the alternate part doesn't have a reorder point i need to add one. If there are no alternate parts I need to check the part in InventoryThatNeedsReorders in the CurrentInventoryTable if the part or any alternates of that part exist in the Distribution location. So what I would expect is something like this using the data provided.
If InventoryThatNeedsReorders.PartNumber=AlterNateParts.PartNumber Then
If AlterNateParts.PartNumber=CurrentInventoryTable.PartNumber Then
Update InventoryThatNeedsReorders.ReorderPoint=0 where CurrentInventoryTable.PartNumber=CurrentInventoryTable.PartNumber
If Exists AlterNateParts.PartNumber Then
Update InventoryThatNeedsReorders.ReorderPoint=1 where CurrentInventoryTable.PartNumber=AlterNateParts.PartNumber
Else
Insert into InventoryThatNeedsReorders ( AlterNateParts.PartNumber, InventoryThatNeedsReorders.ReorderPoint)
Value(AlterNateParts.PartNumber,1)
End If
End If
End If
Hope this helps, let me know if you need more info.
February 5, 2010 at 12:33 pm
Based on your sample data, what would be the expected output?
February 5, 2010 at 12:37 pm
I would expect for it to find in current inventory 2 alternates and then find a qty in stock of those 2 alternates. Then it will update the reorder point of the original part to 0 then set a reorder point of 1 to the alternate part. Then for the last part it will find it in Distribution then set the reorder point of the reorder point to 0 and set the alternate part to 1.
February 5, 2010 at 12:43 pm
Instead of your verbal description would you mind sharing the results you'd expect as the result of "SELECT * FROM InventoryThatNeedsReorders"?
February 5, 2010 at 12:48 pm
In the example if the updates and inserts were done then i pulled the query
Select PartNumber, Location, Quantity from InventoryThatNeedsReorders Where ((Quantity-ReorderPoint)>=1)
I would not expect it to show:
Widget 123, GrandRapids, 2
because the other parts Quantity-Reorder would be 0
February 5, 2010 at 12:56 pm
I think this might be close to what you want. Not sure about the INSERT bit. I probably haven't interpreted your requirements perfectly but maybe you will get the idea. I think you can do it with MERGE.
MERGE INTO InventoryThatNeedsReorders I
USING
(SELECT A.PartNumber, A.AlternatePart
FROM AlterNateParts A
JOIN CurrentInventoryTable C
ON A.PartNumber = C.PartNumber) P
ON I.PartNumber = P.PartNumber
OR I.PartNumber = P.AlternatePart
WHEN MATCHED THEN UPDATE SET ReorderPoint =
CASE I.PartNumber
WHEN P.PartNumber THEN 0
WHEN P.AlternatePart THEN 1
END
WHEN NOT MATCHED THEN
INSERT (PartNumber, ReorderPoint)
VALUES (P.PartNumber, 1);
February 5, 2010 at 12:59 pm
mbender (2/5/2010)
In the example if the updates and inserts were done then i pulled the querySelect PartNumber, Location, Quantity from InventoryThatNeedsReorders Where ((Quantity-ReorderPoint)>=1)
I would not expect it to show:
Widget 123, GrandRapids, 2
because the other parts Quantity-Reorder would be 0
Not what you were asked for here. Please reread what Lutz asked you for above.
February 5, 2010 at 1:00 pm
I think Merge is really close to what I want, i'm doing some reading on it myself. The only thing I see is that I could have multiple alternates for 1 part that could be in stock, but I only need to use 1 of them.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply