Help with Cursor - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help with Cursor

Author

Topic

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-09 : 11:43:05

Table structure

Pol_Num SeqNo InsInd InsOnBene
F1111111 1 Y NULL
F1111111 2 N NULL
F1111111 3 Y NULL
G2222222 1 N NULL
G2222222 2 Y NULL
G2222222 3 Y NULL

The requirement is to increment InsOnBene with a numeric count where the InsInd = Y. The count should be reset for the next policy.
This is the expected output.
Pol_Numb SeqNo InsInd InsOnBene
F1111111 1 Y 1
F1111111 2 N 1
F1111111 3 Y 2
G2222222 1 N 0
G2222222 2 Y 1
G2222222 3 Y 2

Not sure if I've explained this enough. But I think the way to implement this is with a cursor, Whatever thae way, can somebody please help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-09 : 13:01:48

You're on SS2000, right?
Here's one way: (this won't be very efficient so if you have large table post again.)


declare @t table (Pol_Num varchar(10),  SeqNo int, InsInd char(1), InsOnBene int)
insert @t
select 'F1111111', 1, 'Y', NULL union all
select 'F1111111', 2, 'N', NULL union all
select 'F1111111', 3, 'Y', NULL union all
select 'G2222222', 1, 'N', NULL union all
select 'G2222222', 2, 'Y', NULL union all
select 'G2222222', 3, 'Y', NULL

update a set
       a.InsOnBene = d.InsOnBene
from   @t a
join   (
       select a.Pol_num
              ,a.seqNo
              ,(
              select count(*) 
              from @t 
              where pol_num = a.pol_num 
              and SeqNo <= a.seqNo 
              and InsInd = 'Y'
              ) as InsOnBene
       from @t a
       ) d 
       on d.Pol_num = a.Pol_num
       and d.seqNo = a.SeqNo       

select * from @t
OUTPUT:
Pol_Num    SeqNo       InsInd InsOnBene
---------- ----------- ------ -----------
F1111111   1           Y      1
F1111111   2           N      1
F1111111   3           Y      2
G2222222   1           N      0
G2222222   2           Y      1
G2222222   3           Y      2

Be One with the Optimizer
TG

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-09 : 13:38:17

Thank you!

I've tried this and it works. The table is not very huge...but has about 30k records. And I'm on SQL Server 2005.

I have implemented it this way, are you suggesting I stick with this based on the size of the table or will it impact the performance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-09 : 13:52:32

with 30K rows this will probably be fine. The only other way that would make a significant difference would require that the table have a CLUSTERED index on (Pol_num, SeqNo). For millions of rows an update method (set @var=column=expression) would probably be needed but it sounds like what you've got now will be fine.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources