quote:

---

Originally posted by SHIVPREET2K1

dear slimt slimt i like ur post but can u make it more clear as i m very new to the if and loop statements in sql server

secondly i want to set off order accoring to order no in a sorted way.


thankd for your posts once again

---

DECLARE @dispatched int,@item_code varchar(3),@order_date datetime,@Order_no varchar(10),@allocated int

SELECT DISTINCT TOP 1 @item_code=Item_code
FROM dispatch
ORDER BY Item_code

WHILE @item_code IS NOT NULL
BEGIN
SELECT @dispatched=SUM(dispatch_Qty)
FROM dispatch
WHERE Item_code=@item_code

SELECT TOP 1 @order_date=order_Date,
@Order_no= Order_no
FROM Orders
WHERE item_code=@Item_code

UPDATE Orders
SET @allocated=dispatch_qty= CASE WHEN Order_Qty< @dispatched THEN Order_Qty ELSE @dispatched END,    
    @order_date=order_Date,
    @Order_no = Order_no,
    @dispatched=@dispatched-@allocated
FROM Orders  (TABLOCKX) 
WHERE Item_code=@item_code
OPTION (MAXDOP 1)

SELECT DISTINCT TOP 1 @item_code=Item_code
FROM dispatch
WHERE Item_code>@item_code
ORDER BY Item_code
END

SELECT * FROM orders 

CREATE TABLE OrderStock
(OrderNo varchar(10),
 OrderDate datetime,
 Item_Code varchar(10),
 OrderQty int,
 DespatchedQty int
 )
 CREATE TABLE DEspatch
 (
 DespatchNo  varchar(10),
 Item_Code varchar(10),
 Despatch_Qty int
 )


INSERT INTO OrderStock
VALUES ('abc','2010-01-23','Item1',2500,0),
('mno','2010-01-15','Item1',3000,0),
('pqr','2010-02-20','Item2',1350,0),
('daa','2010-02-23','Item1',3300,0),
('dwq','2010-02-22','Item2',4400,0),
('dfg','2010-01-26','Item1',3000,0),
('dbb','2010-03-03','Item2',2350,0),
('xcv','2010-02-18','Item1',1300,0)

INSERT INTO DEspatch
VALUES ('dd1','Item1',300),
('dd2','Item2',1000),
('dd3','Item1',400),
('dd4','Item2',2000),
('dd5','Item1',300),
('dd7','Item2',500),
('dd8','Item1',10000)
CREATE CLUSTERED INDEX IDX_OrderStock ON OrderStock(Item_Code,OrderDate,OrderNo)

SELECT * FROM OrderStock

DECLARE @Item_Code varchar(10), @OrderNo varchar(10), @OrderDate datetime,@Allocated int,@despatched int,@AllocatedSum int

SELECT TOP 1 @Item_Code =Item_Code, @OrderNo= OrderNo, @OrderDate = OrderDate
FROM OrderStock
ORDER BY OrderDate

UPDATE o
SET @Despatched=CASE WHEN COALESCE(@Allocated,0) = 0 THEN TotalDespatched ELSE TotalDespatched -@AllocatedSum END,
	@Allocated=o.DespatchedQty=CASE WHEN @Despatched > OrderQty THEN OrderQty ELSE  @Despatched END,
	@AllocatedSum= CASE WHEN o.OrderDate=LatestOrderDate THEN 0 ELSE COALESCE(@AllocatedSum,0) + @Allocated END,
    @Item_Code =Item_Code,@OrderNo= OrderNo, @OrderDate = OrderDate
    
FROM OrderStock o (TABLOCKX)
CROSS APPLY(SELECT SUM(Despatch_Qty) AS TotalDespatched
            FROM DEspatch
            WHERE Item_Code=o.Item_Code
            )d
CROSS APPLY(SELECT MAX(OrderDate) AS LatestOrderDate
			FROM OrderStock
			WHERE Item_Code=o.Item_Code
)e
OPTION (MAXDOP 1)
            
SELECT * FROM OrderStock

DROP TABLE OrderStock
DROP TABLE DEspatch

output
---------------------------------------------
before update
OrderNo	OrderDate	       Item_Code	OrderQty	DespatchedQty
mno	2010-01-15 00:00:00.000	Item1	        3000	0
abc	2010-01-23 00:00:00.000	Item1	        2500	0
dfg	2010-01-26 00:00:00.000	Item1	        3000	0
xcv	2010-02-18 00:00:00.000	Item1	        1300	0
daa	2010-02-23 00:00:00.000	Item1	        3300	0
pqr	2010-02-20 00:00:00.000	Item2	        1350	0
dwq	2010-02-22 00:00:00.000	Item2	        4400	0
dbb	2010-03-03 00:00:00.000	Item2	        2350	0

after update

OrderNo	OrderDate	       Item_Code	OrderQty	DespatchedQty
mno	2010-01-15 00:00:00.000	Item1	        3000	3000
abc	2010-01-23 00:00:00.000	Item1	        2500	2500
dfg	2010-01-26 00:00:00.000	Item1	        3000	3000
xcv	2010-02-18 00:00:00.000	Item1	        1300	1300
daa	2010-02-23 00:00:00.000	Item1	        3300	1200
pqr	2010-02-20 00:00:00.000	Item2	        1350	1350
dwq	2010-02-22 00:00:00.000	Item2	        4400	2150
dbb	2010-03-03 00:00:00.000	Item2	        2350	0