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("ADODB.Connection")

cn.Open = "Provider=SQLOLEDB;Data Source=(Local);Initial Catalog=Ave;User ID=sa;Password=bigmak10"

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

set rs1 = CreateObject("ADODB.Recordset")
rs1.open sSql , cn
do until rs1.eof
sSql = "SELECT SMART_SALES.EXPRESS, SMART_FUND_LIST.[Fund Long Name], SUM(SMART_SALES.FY_SALES) AS GROSS_SALES "& _
"FROM SMART_SALES INNER JOIN "& _
"SMART_FUND_LIST ON SMART_SALES.FUND_NUM = SMART_FUND_LIST.[Fund TA Fund Num] "& _
"GROUP BY SMART_SALES.EXPRESS, SMART_FUND_LIST.[Fund Long Name] "& _
"HAVING (SUM(SMART_SALES.FY_SALES) > 5000) AND (SMART_SALES.EXPRESS = '" & rs1("express").value & "') "& _
"ORDER BY SUM(SMART_SALES.FY_SALES) DESC "
set rs2 = CreateObject("ADODB.Recordset")
rs2.Open sSql, cn
i = 0
Do Until rs2.EOF
i = i + 1
set cmd = createobject("ADODB.COMMAND")
cmd.activeconnection = cn
cmd.commandtype = adCmdText
cmd.commandtext = "insert into SMART_SALES_EXPRESS_FUND ( express, [Fund Long Name], Gross_sales, rank ) "& _
"values ('" & rs1("express").value & "', '" & rs2("Fund Long Name").value & "', " & rs2("Gross_sales").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 = DTSTaskExecResult_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