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.
| Author |
Topic |
|
msbolton
Starting Member
12 Posts |
 Posted - 2006-04-12 : 05:45:15
|
I have a large dataset containing visist to an organisation. I need to assign a sequential visits number per person, i.e. first visit is 1 nth visit is n.At the momemnts I am using an activex recordste to get all visits for a person in date order and then looping through these to add visit number.This takes a really long time to run but I can't think of another way or approaching this. Can it be done via an update statement?Function Main() set cn = CreateObject("adodb.connection") set rs = CreateObject("adodb.recordset" ) set cn2 = CreateObject("adodb.connection") set rs2 = CreateObject("adodb.recordset" ) set cn3 = CreateObject("adodb.connection") set rs3 = CreateObject("adodb.recordset" ) cn.Provider = "SQLOLEDB" cn.open = "Data Source=RIS_DM_MB; Initial Catalog=CRIS; Integrated Security=SSPI" cn2.Provider = "SQLOLEDB" cn2.open = "Data Source=RIS_DM_MB; Initial Catalog=CRIS; Integrated Security=SSPI" cn3.Provider = "SQLOLEDB" cn3.open = "Data Source=RIS_DM_MB; Initial Catalog=CRIS; Integrated Security=SSPI" rs2.open "select computer_no from table_dm_patients", cn2, 0 do while not rs2.eof rs.open "select event_key from extract_event e, attendances a where computer_number = " & rs2(0) & " and event_key = rm_attendance_no and attendance_date is not null order by attendance_date", cn, 0 intAtNo = 0 do while not rs.eof intAtNo = intAtNo + 1 rs3.open "update extract_event set at_no = " & intAtNo & " where event_key = " & rs(0), cn3, 2, 3 rs.movenext loop rs.close rs2.movenext loop Main = DTSTaskExecResult_SuccessEnd FunctionAny help appreciated |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
|
|
|
|
|
Alternative to update with cursor