Update Table for records up to a specified quantity
-
Hi
Hope someone out there can help.
I have a table as follows
Orderref Int, Date DateTime, Quantity Decimal(14,3), Material NVarChar(32), Scheduled Amount
With the following Data
1, 2006/3/1, 100.000, MATERIAL1,0
2, 2006/3/2, 200.000, MATERIAL1,0
3, 2006/3/3, 150.000, MATERIAL1,0
4, 2006/3/1, 100.000, MATERIAL2,0
5, 2006/3/3, 100.000, MATERIAL2,0
I need to set up a stored procedure that will accept a Material and Quantity and update the scheduled Amount up to and including the quantity passed. Priority should be given to those orders with the earliest dates.
e.g If the values MATERIAL1 and a quantity of 350 were passed the records should then show
1, 2006/3/1, 100.000, MATERIAL1,100
2, 2006/3/2, 200.000, MATERIAL1,200
3, 2006/3/3, 150.000, MATERIAL1,50
4, 2006/3/1, 100.000, MATERIAL2,0
5, 2006/3/3, 100.000, MATERIAL2,0
I know this could be done using a cursor but is there a set based alternative ?
Thanks in advance
-
I wonder, why people always confuse CURSORS and LOOPS or whether they even know the difference...
One solution:
BEGIN TRAN
DECLARE @OrderID INT,
@OrderAmount FLOAT,
@OrderAmountScheduled FLOAT
WHILE @myAmounttoSchedule > 0
BEGIN
SELECT
@OrderID = OrderID,
@OrderAmount = OrderAmount,
@OrderAmountScheduled = OrderAmountScheduled
FROM myTable
WHERE
OrderID = (SELECT Min(OrderID)
FROM myTable
WHERE mymaterial = myMaterialToSchdeule
AND myScheduledAmount < myOrderAmount
AND OrderDate = SELECT MIN(OrderDate)
FROM myTable
WHERE mymaterial = myMaterialToSchdeule
AND myScheduledAmount < myOrderAmount)
IF OrderID IS NULL -- not enough Orders to accept amount to schedule
BEGIN
ROLLBACK TRAN
RETURN
END
IF @myAmounttoSchedule > @OrderAmount - @OrderAmountScheduled
BEGIN
SET @myAmounttoSchedule = @myAmounttoSchedule - (@OrderAmount - @OrderAmountScheduled)
SET @OrderAmountScheduled = @OrderAmount
END
ELSE IF @myAmounttoSchedule <= @OrderAmount - @OrderAmountScheduled
BEGIN
SET @OrderAmountScheduled = @OrderAmountScheduled + @myAmounttoSchedule
SET @myAmounttoSchedule = 0
END
UPDATE myTable
SET scheduledAmount = @OrderAmountScheduled
WHERE OrderID = @OrderID
END
COMMIT TRAN
RETURN
PS: you can of course work without variables (CASE(d) update statements), but for clarity I would not do so.
Comment on the select statement
SELECT
...
FROM myTable
WHERE
OrderID = (SELECT Min(OrderID)
FROM myTable
WHERE mymaterial = myMaterialToSchdeule
AND myScheduledAmount < myOrderAmount
AND OrderDate = SELECT MIN(OderDate)
FROM myTable
WHERE mymaterial = myMaterialToSchdeule
AND myScheduledAmount < myOrderAmount)
This can of course be done "more simple" in questionable manners, e.g.
"SELECT TOP 1 ... ORDER BY..." OR "SET ROWCOUNT 1 ... then issue the select statement" etc.
_/_/_/ paramind _/_/_/ -
a possible solution
DECLARE @Material NVarChar(32), @Qty int
SET @Material = 'MATERIAL1'
SET @Qty = 350
UPDATE a
SET a.[Scheduled Amount] = CASE WHEN (@Qty - b.Summ) > a.Quantity THEN a.Quantity ELSE (@Qty - b.Summ) END
FROM
a
INNER JOIN (SELECT s1.Orderref, SUM(ISNULL(s2.Quantity,0)) AS [Summ]
FROM
s1
LEFT OUTER JOIN
s2
ON s2.Orderref <> s1.Orderref
AND s2.Material = s1.Material
AND s2.[Date] < s1.[Date]
WHERE s1.Material = @Material
GROUP BY s1.Orderref
) b
ON b.Orderref = a.Orderref
WHERE a.Material = @Material
AND (@Qty - b.Summ) > 0
Far away is close at hand in the images of elsewhere.
Anon. -
I'm afraid you missed the goal
or do you think, i'm payed per line of code?
_/_/_/ paramind _/_/_/ -
Well, I prefer David's method. A little tweak to seems (to me) to meet all the requirements (including the priority one)...
SET @Material = 'MATERIAL1'
SET @Qty = 350
UPDATE a
SET a.[Scheduled Amount] = CASE WHEN (@Qty - b.Summ) > a.Quantity THEN a.Quantity ELSE (@Qty - b.Summ) END
FROM
a
INNER JOIN (SELECT s1.Orderref, SUM(ISNULL(s2.Quantity,0)) AS [Summ]
FROM (SELECT TOP 100 PERCENT * FROM
ORDER BY Date) s1
LEFT OUTER JOIN
s2
ON s2.Orderref <> s1.Orderref
AND s2.Material = s1.Material
AND s2.[Date] < s1.[Date]
WHERE s1.Material = @Material
GROUP BY s1.Orderref
) b
ON b.Orderref = a.Orderref
WHERE a.Material = @Material
AND (@Qty - b.Summ) > 0
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
This means, you didn't understand the goal either
_/_/_/ paramind _/_/_/ -
Thanks to you all
I've done some simple tests on both solutions and think both seem to meet the 'goal' bearing in mind the 'tweek' Ryan identified.
Whats your thoughts on the relative performances for larger data tables ?
-
I'm really not trying to define YOUR VERY OWN goals, but be very, very careful with the second solution
Drop me a message, when you're stuck
_/_/_/ paramind _/_/_/ -
paramind
What are your concerns regarding the other solution ?
Andrew
-
A little tweak to seems (to me) to meet all the requirements (including the priority one)... My solution caters for the priority requirement.
The only problem would be if there was duplicate dates. However if ascending Orderref correlates with ascending date then...
UPDATE a
SET a.[Scheduled Amount] = CASE WHEN (@Qty - b.Summ) > a.Quantity THEN a.Quantity ELSE (@Qty - b.Summ) END
FROM
a
INNER JOIN (SELECT s1.Orderref, SUM(ISNULL(s2.Quantity,0)) AS [Summ]
FROM
s1
LEFT OUTER JOIN
s2
ON s2.Orderref < s1.Orderref
AND s2.Material = s1.Material
AND s2.[Date] <= s1.[Date]
WHERE s1.Material = @Material
GROUP BY s1.Orderref
) b
ON b.Orderref = a.Orderref
WHERE a.Material = @Material
AND (@Qty - b.Summ) > 0
As for performance, try it and check Execution plans, it might work better by extracting by Material in a temp table, extracting the summary subquery from that temp table into another and then joining them. With correct indexes it may prove faster.
Far away is close at hand in the images of elsewhere.
Anon. -
Sorry David
Looking at it again and I can see it now.
Thanks
-
No problem
I had to look twice myself just to make sure
Far away is close at hand in the images of elsewhere.
Anon. -
Just have a look at what will happen when you restart with this situation being the starting point: (again 350 to schedule)
1, 2006/3/1, 100.000, MATERIAL1,50
2, 2006/3/3, 200.000, MATERIAL1,0
3, 2006/3/3, 150.000, MATERIAL1,0
4, 2006/3/3, 100.000, MATERIAL2,0
5, 2006/3/3, 100.000, MATERIAL2,0
_/_/_/ paramind _/_/_/ -
I see paramind - that's what you're thinking. In that case, I guess changing...
INNER JOIN (SELECT s1.Orderref, SUM(ISNULL(s2.Quantity,0)) AS [Summ]
...to...
INNER JOIN (SELECT s1.Orderref, SUM(ISNULL(s2.Quantity,0)-ISNULL(s2.[Scheduled Amount],0)) AS [Summ]
...might do the trick.
David: sorry about before - I got mixed up with my testing.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
UPDATE a
SET a.[Scheduled Amount] = a.[Scheduled Amount] + CASE WHEN (@Qty - b.Summ) > (a.Quantity - a.[Scheduled Amount]) THEN (a.Quantity - a.[Scheduled Amount]) ELSE (@Qty - b.Summ - a.[Scheduled Amount]) END
FROM @a a
INNER JOIN (SELECT s1.Orderref, SUM(ISNULL(s2.Quantity,0)-ISNULL(s2.[Scheduled Amount],0)) AS [Summ]
FROM @a s1
LEFT OUTER JOIN @a s2
ON s2.Orderref < s1.Orderref
AND s2.Material = s1.Material
AND s2.[Date] <= s1.[Date]
WHERE s1.Material = @Material
GROUP BY s1.Orderref
) b
ON b.Orderref = a.Orderref
WHERE a.Material = @Material
AND (@Qty - b.Summ) > 0
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply