set off multiple rows with one value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

set off multiple rows with one value

Author

Topic

SHIVPREET2K1
Starting Member

32 Posts

Posted - 2010-03-07 : 07:30:44

Dear friends

Please help me to sort out this simple problem

I have a two variables which is getting a values from a cursor. its working fine. Suppose currently that variables has value
@dispatched=5000
@item_code='xxx'
now i have a table with following sturcture


Order_no order_Date Item_code Order_Qty dispatch_qty  
abc      12-jan-10    xxx        2000      0
xyz      15-feb-10    xxx        4000      0

what i need is to set off my dispatch value in the above mentioned table in the dispatch_qty column. Pleast tell me how can i do it.

The final result for those values should be


Order_no order_Date Item_code Order_Qty dispatch_qty  
abc      12-jan-10    xxx        2000      2000
xyz      15-feb-10    xxx        4000      3000

Thanks in advance for your help

Shivpreet2k1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-07 : 08:36:15

I don't like cursors but I would do that in a cursor.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 09:17:27

[code]

--now i have a table with following sturcture

CREATE TABLE Orders
(Order_no varchar(10),
order_Date datetime,
Item_code varchar(3),
Order_Qty int,
dispatch_qty int
)

CREATE CLUSTERED INDEX IDX_Order_no ON Orders(order_Date,Order_no)
INSERT INTO Orders
SELECT 'abc','12-jan-10','xxx',2000,0 UNION ALL
SELECT 'xyz','15-feb-10','xxx',4000,0) UNION ALL
SELECT 'wer','30-Jan-10','xxx',5000,0)

DECLARE @dispatched int,@item_code varchar(3),@Order_no varchar(10),@allocated int
SELECT @dispatched=10000

SELECT * FROM Orders

SELECT TOP 1 @Order_no= Order_no,@item_code=Item_code
FROM Orders

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

SELECT * FROM Orders

DROP Table Orders

output
--------------------------------
before update
Order_no order_Date Item_code Order_Qty dispatch_qty
abc 2010-01-12 00:00:00.000 xxx 2000 0
wer 2010-01-30 00:00:00.000 xxx 5000 0
xyz 2010-02-15 00:00:00.000 xxx 4000 0

after update

Order_no order_Date Item_code Order_Qty dispatch_qty
abc 2010-01-12 00:00:00.000 xxx 2000 2000
wer 2010-01-30 00:00:00.000 xxx 5000 5000
xyz 2010-02-15 00:00:00.000 xxx 4000 3000

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-07 : 13:33:28

cool