Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • Last Modified:

Using Cursor to update record

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
bookspan
Asked:
bookspan
  • 3
1 Solution
 
chapmandewCommented:
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
 
adatheladCommented:
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
 
adatheladCommented:
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
 
bookspanAuthor Commented:
Adathelad Solution works better.
What is the name of that type of update?
0
 
adatheladCommented:
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now