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 )
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.

----- 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 ----------------------------