February 5, 2010 at 1:03 pm
Guess I'm a bit unclear about what your asking, do you want to know what I would expect the results of SELECT * FROM InventoryThatNeedsReorders before or after the logic was implemented on it.
February 5, 2010 at 1:04 pm
mbender (2/5/2010)
Guess I'm a bit unclear about what your asking, do you want to know what I would expect the results of SELECT * FROM InventoryThatNeedsReorders before or after the logic was implemented on it.
Show what the results of the query would be, don't describe it.
February 5, 2010 at 1:07 pm
mbender (2/5/2010)
Guess I'm a bit unclear about what your asking, do you want to know what I would expect the results of SELECT * FROM InventoryThatNeedsReorders before or after the logic was implemented on it.
If the insert statement you provided did not exactly describe the status of that table for either before or after the logic, then yes, both results please.
February 5, 2010 at 1:23 pm
Results of
SELECT * FROM InventoryThatNeedsReorders
PartNumber LocationQuantityReorderPoint
Widget 123 GrandRapids 2 1
Widget 456 Chicago 2 1
Widget 457 Detroit 2 1
February 5, 2010 at 1:55 pm
Based on your result set you don't have to do anything because the data are already there (at least when looking at the data you provided).
So we elaborated that you provided the expected result data in your sample data.
What would be the SELECT statement before the logic?
February 5, 2010 at 2:28 pm
Ok I think i know what you want now so let me try this again.
This is the select statement before the logic:
Select PartNumber, Location, Quantity from InventoryThatNeedsReorders Where ((Quantity-ReorderPoint)>=1)
Which would give me this result
PartNumber Location Quantity
Widget 123 GrandRapids 2
Widget 456 Chicago 2
Widget 457 Detroit 2
After the Logic I would expect to see from
SELECT * FROM InventoryThatNeedsReorders
PartNumber Location Quantity ReorderPoint
Widget 123 GrandRapids 2 1
Widget 456 Chicago 2 0
Widget 457 Detroit 2 0
Widget 743 Chicago 2 1
Widget 767 Detroit 2 1
hope this helps
February 5, 2010 at 2:49 pm
David or someone,
Could you help break this down for me
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);
I tried running this with my sample data and i got 0 rows affected. Maybe it would help if I understood more of what this was trying to do.
February 5, 2010 at 3:06 pm
Here's another question, not sure if its possible. I can write this code without a problem in asp. My problem is this code needs to run without anyone hitting the page, thats why i'm trying to do it in SQL. Is there a way using a SQL scheduled task I can have it run an asp page?
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply