Looping through recordset creating calculated Rank Column

[kamgib]

Ok, I am racking my head over how to efficently do this in Sql Server 2000. This code is going slow.......very slow.......and direction would be appreciated.



dim rs1
dim rs2
dim cn
dim sSql
dim cmd

' instantiate the ADO objects
set cn = CreateObject("A DODB.Connection ")

cn.Open = "Provider=SQLOL EDB;Data Source=(Local); Initial Catalog=Ave;Use r ID=sa;Password= bigmak10"

'Find distinct express numbers in sales table
sSql = "SELECT DISTINCT EXPRESS FROM SMART_SALES ; "

set rs1 = CreateObject("A DODB.Recordset" )
rs1.open sSql , cn
do until rs1.eof
sSql = "SELECT SMART_SALES.EXP RESS, SMART_FUND_LIST .[Fund Long Name], SUM(SMART_SALES .FY_SALES) AS GROSS_SALES "& _
"FROM SMART_SALES INNER JOIN "& _
"SMART_FUND_LIS T ON SMART_SALES.FUN D_NUM = SMART_FUND_LIST .[Fund TA Fund Num] "& _
"GROUP BY SMART_SALES.EXP RESS, SMART_FUND_LIST .[Fund Long Name] "& _
"HAVING (SUM(SMART_SALE S.FY_SALES) > 5000) AND (SMART_SALES.EX PRESS = '" & rs1("express"). value & "') "& _
"ORDER BY SUM(SMART_SALES .FY_SALES) DESC "
set rs2 = CreateObject("A DODB.Recordset" )
rs2.Open sSql, cn
i = 0
Do Until rs2.EOF
i = i + 1
set cmd = createobject("A DODB.COMMAND")
cmd.activeconne ction = cn
cmd.commandtype = adCmdText
cmd.commandtext = "insert into SMART_SALES_EXP RESS_FUND ( express, [Fund Long Name], Gross_sales, rank ) "& _
"values ('" & rs1("express"). value & "', '" & rs2("Fund Long Name").value & "', " & rs2("Gross_sale s").value & ", " & i & " ) "
cmd.execute, adAsyncExecute
set cmd = nothing
rs2.movenext
Loop
rs2.Close
Set rs2 = Nothing

rs1.movenext
loop

rs1.close
set rs1 = nothing

cn.close
set cn = nothing

Main = DTSTaskExecResu lt_Success
End Function

[Stang02GT]

This is not the correct forum for technical questions. And please use [code] tags when posting code!


Thank you

[ck9663]

Can't read it well.

Could you post some sample data and the result you're trying to achieve?

-- CK