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 2000 Forums  SQL Server Administration (2000)  SQL Update Query with leftover

Author  Topic  JK_A Starting Member 4 Posts Posted - 2004-03-25 : 12:11:53 Hi , I need your expert opinion to solve the problem. I have a table like one below ID NoRequested Participant_ID NumberAvailable ----------- ------------------------------ -------------- ------------------------------ 1 1000.000000000 X00001 10.000000000 2 2000.000000000 X00001 5000.000000000 3 1000.000000000 X00002 600.000000000 4 1000.000000000 X00002 7100.000000000 I want to update NoRequested in each row with updated NoRequested . The new NoRequested will derive using the following formula. If NoRequested > NumberAvailable then NoRequested = NumberAvailable Remain = (NoRequested - NumberAvailable ) else NoRequested = NoRequested The remaining amount will add into the next row of the same participant I spent lot of time by trying to solve the problem by using the query below without any luck. For the time being I am using a cursor to solve the problem but it is taking a long time to execute Is there any way to solve the problem with out using cursor ? Following is out put what I am expecting from the query ID NoRequested Participant_ID NumberAvailable ----------- ------------------------------ -------------- ------------------------------ 1 10.000000000 X00001 10.000000000 2 2990.000000000 X00001 5000.000000000 3 600.000000000 X00002 600.000000000 4 1400.000000000 X00002 7100.000000000 The query I am using is listed below. Thank you very much for your time. JK_A ----- Start of Query-------------------------------- Declare @MyTable table(ID int identity primary key, NoRequested decimal(28,9), Participant_ID nvarchar(6), NumberAvailable decimal(28,9) ) set nocount on Insert into @MyTable values(1000,'M00001',10) Insert into @MyTable values(2000,'M00001',5000) Insert into @MyTable values(1000,'M00002',600) Insert into @MyTable values(1000,'M00002',7100) Declare @Share decimal(28,9) set @Share=0 select * from @MyTable Update @Mytable set NoRequested =(Case when NoRequested >Numberavailable then NumberAvailable else NoRequested +@Share end), @Share=(Case when NoRequested >Numberavailable then NoRequested -Numberavailable else 0 end) select * from @MyTable ---------------End of Query ----------------------------