How to use cursor to create re-occuring numbers? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to use cursor to create re-occuring numbers?

Author

Topic

AJL
Starting Member

7 Posts

Posted - 2007-06-09 : 02:06:21

SELECT pid, lname, visit_date, quantity
FROM customer
ORDER BY pid

pid lname visit_date quantity
-------------------------------------------
23 wang 07/23/2006 100
23 wang 07/30/2006 140
23 wang 08/05/2006 130
23 wang 08/15/2006 135
23 wang 08/22/2006 110
34 linden 06/23/2006 99
34 linden 07/06/2006 110
34 linden 07/15/2006 120
34 linden 08/26/2006 99

How do I use cursor (or is there a better way) to create an additional column "index"? "Index" starts at 1 for every unique pid

index pid lname visit_date quantity
----------------------------------------------------------
1 23 wang 07/23/2006 100
2 23 wang 07/30/2006 140
3 23 wang 08/05/2006 130
4 23 wang 08/15/2006 135
5 23 wang 08/22/2006 110
1 34 linden 06/23/2006 99
2 34 linden 07/06/2006 110
3 34 linden 07/15/2006 120
4 34 linden 08/26/2006 99

Please let me know if I can explain the my question better. Thanks in advance

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-06-09 : 02:34:41

For this u dont need a cursor, try this

SELECT pid, lname, visit_date, quantity,
( SELECT COUNT(*) FROM customer WHERE pid = C.pid AND
visit_date <= C.visit_date) AS 'index'
FROM customer C
ORDER BY pid

if u got large data then use update.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-09 : 02:42:18

Where do you want to show data?
Note that if the table has millions of rows, the above query will run for hours
If you want to show data in front end, you can easily do numbering there

Madhivanan

Failing to plan is Planning to fail

AJL
Starting Member

7 Posts

Posted - 2007-06-09 : 03:21:21

Thank you all.

PeterNeo, it worked wonderfully!!

Madhivanan, thank you for your suggestion, I will remember it for future reference.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-09 : 06:52:21

Also, if you want to show data in Report, you can group it by pid and Reset Recordnumber for each group

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources