i have a database with almost 7 million recs. i have to produce a report with the following info
top 300 customers
rank custno custname sales YeartoDate% CurrentMonthSales
PriorYearRank PriorYearSales PriorYearMonthsales
the problem comes in getting the rank (since sql server 2000 doesn't have a RANK() function). it would be faster to populate the rank on both table "A" current sales and table "B" Prior Year sales by looping through temp cursors . So I get current sales, add rank, and store in a temp cursor - "A". do the same for prior year sales - temp cursor "B".
After the "A" and "B" are complete, I will need to join them into one result set along with some other info.
The question is how do i do this. is it possible to add records to a temp cursor or do i have to create a temp table. if i have to create a temp table how can i make sure the tablename is unique.
thanks.
NV
Randy Belcher
AFG Industries, Inc.