Update Table for records up to a specified quantity
-
Again, David. One thing in advance: I think we all know Celko's nice SQL puzzles, here for example calculating running sums or ordering records using plain SQL.
I'm a supporter of plain SQL ... but your solution uses some implicit assumptions that are not given or do apply to the sample data only (e.g. ordering by date and by Orderref CAN be contradictionary): try the following data: (pay attention to Material: it's always MATERIAL1)
tblOrder Orderref Date Quantity Material ScheduledAmount 3 2006-03-01 150 MATERIAL1 0 4 2006-03-01 100 MATERIAL1 0 5 2006-03-03 100 MATERIAL1 0 7 2006-03-01 100 MATERIAL1 0 8 2006-03-01 200 MATERIAL1 0 your result would be:
tblOrder Orderref Date Quantity Material ScheduledAmount 3 2006-03-01 150 MATERIAL1 150 4 2006-03-01 100 MATERIAL1 100 5 2006-03-03 100 MATERIAL1 75 7 2006-03-01 100 MATERIAL1 75 8 2006-03-01 200 MATERIAL1 0 and this is wrong ...
_/_/_/ paramind _/_/_/ -
Apart from the above mentioned problem, you'll always update the values, whether they've changed or not. This should not happen either. Think of this in terms of performance.
There's still another error in your script: if the amount to be scheduled is greater than the sum 'needed by the orders', the update should fail, but it doesn't.
In my own SQL there several syntax errors, since I didn't type it in QA. Here's the corrected version:
ALTER PROCEDURE adsp_OrderSchedule_01
(@Material NVARCHAR(32),
@myAmounttoSchedule FLOAT)
AS
BEGIN TRAN
DECLARE @OrderRef INT,
@Quantity FLOAT,
@ScheduledAmount FLOAT
WHILE @myAmounttoSchedule > 0
BEGIN
SET @OrderRef = NULL
SET @Quantity = NULL
SET @ScheduledAmount = NULL
SELECT
@OrderRef = OrderRef,
@Quantity = Quantity,
@ScheduledAmount = ScheduledAmount
FROM tblOrder
WHERE
OrderRef = (SELECT Min(OrderRef)
FROM tblOrder
WHERE Material = @Material
AND ScheduledAmount < Quantity
AND [Date] = (SELECT MIN([Date])
FROM tblOrder
WHERE Material = @Material
AND ScheduledAmount < Quantity))
IF @OrderRef IS NULL -- not enough Orders to accept amount to schedule
BEGIN
ROLLBACK TRAN
PRINT 'R'
RETURN 0
END
IF @myAmounttoSchedule > @Quantity - @ScheduledAmount
BEGIN
SET @myAmounttoSchedule = @myAmounttoSchedule - (@Quantity - @ScheduledAmount)
SET @ScheduledAmount = @Quantity
END
ELSE IF @myAmounttoSchedule <= @Quantity - @ScheduledAmount
BEGIN
SET @ScheduledAmount = @ScheduledAmount + @myAmounttoSchedule
SET @myAmounttoSchedule = 0
END
UPDATE tblOrder
SET scheduledAmount = @ScheduledAmount
WHERE OrderRef = @OrderRef
END
COMMIT TRAN
RETURN
_/_/_/ paramind _/_/_/ -
...but your solution uses some implicit assumptions that are not given ... But your observations are assumptions also, implying possible unecessary code not required or implied in the first post, but good thinking out of the box
My revised solution
DECLARE @Qty Decimal(14,3), @Material NVarChar(32)
SET @Qty = 350
SET @Material = N'Material1'
CREATE TABLE #temp ([ID] int IDENTITY(1,1) PRIMARY KEY CLUSTERED, Orderref Int, Available Decimal(14,3))
INSERT INTO #temp (Orderref, Available)
SELECT Orderref, Quantity - [Scheduled Amount] AS [Available]
FROM
WHERE Quantity > [Scheduled Amount]
AND Material = @Material
ORDER BY [Date] ASC, Orderref ASC
CREATE TABLE #temp2 (Orderref Int PRIMARY KEY CLUSTERED, Scheduled Decimal(14,3))
INSERT INTO #temp2 (Orderref, Scheduled)
SELECT t.OrderRef, CASE WHEN (@Qty - s.Summ) > t.Available THEN t.Available ELSE (@Qty - s.Summ) END
FROM #temp t
INNER JOIN (SELECT t1.[ID], SUM(ISNULL(t2.Available,0)) AS [Summ]
FROM #temp t1
LEFT OUTER JOIN #temp t2 ON t2.[ID] < t1.[ID]
GROUP BY t1.[ID]) s
ON s.[ID] = t.[ID] AND @Qty > s.Summ
ORDER BY t.OrderRef ASC
IF (SELECT SUM(Scheduled) FROM #temp2) < @qty
RAISERROR('Insufficient Quantity Available',16,1)
ELSE
UPDATE a
SET a.[Scheduled Amount] = a.[Scheduled Amount] + b.Scheduled
FROM
a
INNER JOIN #temp2 b ON b.OrderRef = a.OrderRef
DROP TABLE #temp
DROP TABLE #temp2
Far away is close at hand in the images of elsewhere.
Anon. -
>But your observations are assumptions also<
True - I always assume the worst. Advisable practice, as far as reality is concerned
Do we start a new thread on temp tables vs. table valued functions? I don't think we have to
_/_/_/ paramind _/_/_/
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply