Solved

Using Cursor to update record

Posted on 2009-05-19
5
504 Views
Last Modified: 2012-05-07
I have a series of records that look like the sample below:
tblID  tkID         execQuantity   RemainingShares   QuantityFilled
-----  --------     ------------        ---------------           ---------------
1      71345691     400                100                          NULL
2      71345691     500                    0                            NULL
3      71345697     100                 400                          NULL
4      71345697     400                 100                          NULL
5      71345697     500                     0                          NULL
6      71345699     300                 200                          NULL
7      71345699     500                     0                         NULL
8      71345700     300                 700                         NULL
9      71345700     700                 300                        NULL
10     71345700     900                100                        NULL
11     71345700    1000                   0                        NULL
How can I use a cursor to update the quantityFilled column to look like this: for each group of tkID the first QuantityFilled = execQuantity (400 for tblID 1) and the second QuantityFilled = (execQuantity for tblID 2 minus execQuantity for tblID 1) 500 - 400 = 100 (this example referes to tkID 71345691
Than we do the same for tkID 71345697.
I need help to complete the code that is below
Declare @tblID Varchar(50)

Declare @tkID varchar(50)

Declare @execQuantity varchar(50)

Declare @RemainingShares varchar(50)

Declare @QuantityFilled varchar(50)
 
 

Declare GetQuantity CURSOR FOR
 

select  tblID,tkID,

execQuantity,RemainingShares,QuantityFilled

from OrderJournalGroupWorksheet
 
 

OPEN GetQuantity

FETCH NEXT FROM GetQuantity

INTO @tblID,@tkID,@execQuantity,@RemainingShares,@QuantityFilled
 

WHILE @@FETCH_STATUS = 0
 

BEGIN

Need help here	
 
 

     FETCH NEXT FROM GetQuantity

     INTO @tblID, @tkID,@execQuantity,@RemainingShares,@QuantityFilled

END
 

CLOSE GetQuantity

DEALLOCATE GetQuantity

Open in new window

0
Comment
Question by:bookspan
  • 3
5 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24422994
no reason for a cursor...

create table #t(tblID int , tkID int,         execQuantity int,   RemainingShares int,   QuantityFilled int)

insert into #t
select 1  ,    71345691  ,   400          ,      100,                          NULL union all
select 2  ,    71345691  ,   500         ,           0,                            NULL union all
select 3  ,    71345697  ,   100        ,         400  ,                        NULL union all
select 4  ,    71345697  ,   400       ,          100   ,                       NULL union all
select 5  ,    71345697  ,   500      ,               0 ,                         NULL union all
select 6  ,    71345699  ,   300     ,            200   ,                       NULL union all
select 7  ,    71345699  ,   500    ,                 0 ,                        NULL union all
select 8  ,    71345700  ,   300   ,              700   ,                      NULL union all
select 9  ,    71345700  ,   700  ,               300   ,                     NULL union all
select 10 ,    71345700  ,   900 ,               100    ,                    NULL union all
select 11 ,    71345700  ,  1000,                   0   ,                     NULL


update t
set QuantityFilled = calc
from #t t
join
(
select top 100 percent *,
calc = execquantity - (select SUM(execQuantity)
from #t i where i.tkID = o.tkid
and i.tblid < o.tblid
)
from #t o
order by tblID asc
) r on t.tblid = r.tblid
0
 
LVL 23

Expert Comment

by:adathelad
ID: 24423109
I originally came up with something very similar to chapmandew which  I think was wrong as it gave negative QuantityFilled values in the case of tkID 71345700. So, assuming the SUM is wrong and you do only want to substract the execQuantity from the PREVIOUS tblID *only* for a given tkID then try this:


CREATE TABLE #tmp_OrderJournalGroupWorksheet

(

tblID INTEGER,

tkID INTEGER,

execQuantity INTEGER,

RemainingShares INTEGER,

QuantityFilled INTEGER

)
 

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (1,71345691,400,100,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (2,71345691,500,0,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (3,71345697,100,400,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (4,71345697,400,100,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (5,71345697,500,0,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (6,71345699,300,200,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (7,71345699,500,0,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (8,71345700,300,700,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (9,71345700,700,300,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (10,71345700,900,100,NULL)

INSERT #tmp_OrderJournalGroupWorksheet (tblID, tkID, execQuantity, RemainingShares, QuantityFilled) VALUES (11,71345700,1000,0,NULL)
 

UPDATE w1

SET w1.QuantityFilled = w1.execQuantity - ISNULL((SELECT TOP 1 w2.execQuantity FROM #tmp_OrderJournalGroupWorksheet w2 WHERE w2.tkID = w1.tkID AND w2.tblID < w1.tblID ORDER BY w2.tblID DESC), 0)

FROM #tmp_OrderJournalGroupWorksheet w1
 

SELECT * FROM #tmp_OrderJournalGroupWorksheet
 

DROP TABLE #tmp_OrderJournalGroupWorksheet

Open in new window

0
 
LVL 23

Expert Comment

by:adathelad
ID: 24423133
The results for this were:
tblID       tkID        execQuantity RemainingShares QuantityFilled
----------- ----------- ------------ --------------- --------------
1           71345691    400          100             400
2           71345691    500          0               100
3           71345697    100          400             100
4           71345697    400          100             300
5           71345697    500          0               100
6           71345699    300          200             300
7           71345699    500          0               200
8           71345700    300          700             300
9           71345700    700          300             400
10          71345700    900          100             200
11          71345700    1000         0               100
0
 

Author Comment

by:bookspan
ID: 24423344
Adathelad Solution works better.
What is the name of that type of update?
0
 
LVL 23

Accepted Solution

by:
adathelad earned 500 total points
ID: 24423387
There isn't a specific name for this type of UPDATE - it's just a set-based operation using a subquery to set the QuantityFilled column.

Basically you should always try to do things as set-based operations like this, instead of using cursors to update rows one at a time as it's much more performant.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now