By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,637 Members | 1,967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,637 IT Pros & Developers. It's quick & easy.

Looping through recordset creating calculated Rank Column

P: 1
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
Aug 1 '08 #1
Share this Question
Share on Google+
2 Replies


Stang02GT
Expert 100+
P: 1,208
This is not the correct forum for technical questions. And please use [code] tags when posting code!


Thank you
Aug 2 '08 #2

ck9663
Expert 2.5K+
P: 2,878
Can't read it well.

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

-- CK
Aug 5 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.