February 15, 2009 at 2:31 am
Hi there,
Please excuse my lack of knowledge new to SQL Server
I currently have the following Function in Access
Set rs(1) = db.OpenRecordset("SELECT tblB0300_Allocations.* " _
& "FROM tblB0300_Allocations " _
& "WHERE (((tblB0300_Allocations.SAP_NASP_ID) " _
& "In (SELECT qryA2000_Unique_List_SAP_NASP_ID_B_Ends.SAP_NASP_ID " _
& "FROM qryA2000_Unique_List_SAP_NASP_ID_B_Ends " _
& "GROUP BY qryA2000_Unique_List_SAP_NASP_ID_B_Ends.SAP_NASP_ID, qryA2000_Unique_List_SAP_NASP_ID_B_Ends.Local_B_End " _
& "HAVING (((Count(qryA2000_Unique_List_SAP_NASP_ID_B_Ends.SAP_NASP_ID))<>1));)) " _
& "AND ((tblB0300_Allocations.Inactive)=0)) " _
& "ORDER BY tblB0300_Allocations.SAP_NASP_ID, tblB0300_Allocations.Period_From;")
Do Until rs(1).EOF
sValue(1) = rs(1)!SAP_NASP_ID
rs(1).MoveNext
If (rs(1)!SAP_NASP_ID = sValue(1)) Then
sValue(2) = rs(1)!Period_From - 1
If (Right(sValue(2), 2) = "00") Then
sValue(2) = Left(sValue(2), 4) - 1 & "12"
End If
rs(1).MovePrevious
rs(1).Edit
rs(1)!Period_To = sValue(2)
rs(1).Update
Else
rs(1).MovePrevious
End If
rs(1).MoveNext
Loop
rs(1).Close
What it does is look at a set of data moves to the next row and if the have same account number it will update the Period_To date so
NLC09731122009010
NLC09731122009040
NLC09731122009080
Becomes
NLC0973112200901200903
NLC0973112200904200907
NLC09731122009080
So I am trying to replicate this function on SQL Server and need to understand the way to go is using a cursor or is there a better way.
Thanks is advance
Geoff
February 15, 2009 at 8:50 am
geoff.codd
If you could add the create table statement and some additional test data, so that those who are willing and able to assist you would not have to expend a considerable amount of effort performing those tasks but rather concentrate on the solution I am sure you will get the assistance you have requested.
It would also be a great help to know what you have attempted using T-SQL and what the results or your efforts have been.
Read the article whose link is in my signature block as a guide.
February 15, 2009 at 9:03 am
Hi,
Thanks for you reply, not as straight forward for me to supply code for creating tables as main table is based on 6 tables joined in views then the resulting data in added to a table.
Probably just easier for to look at this as sample data
NLC0973112 200901 0
NLC0973112 200904 0
NLC0973112 200908 0
Becomes
NLC0973112 200901 200903
NLC0973112 200904 200907
NLC0973112 200908 0
Currently I have
Set cn = CreateObject("ADODB.Connection")
cn.Open "MS-LON-RSID01_Windows"
Set rs = CreateObject("ADODB.Recordset")
sSQL = "SELECT TMA.tblB0300_Allocations.* " _
& "FROM TMA.tblB0300_Allocations " _
& "WHERE (((TMA.tblB0300_Allocations.SAP_NASP_ID) " _
& "In (SELECT TMA.qryA2000_Unique_List_SAP_NASP_ID_B_Ends.SAP_NASP_ID " _
& "FROM TMA.qryA2000_Unique_List_SAP_NASP_ID_B_Ends " _
& "GROUP BY TMA.qryA2000_Unique_List_SAP_NASP_ID_B_Ends.SAP_NASP_ID, TMA.qryA2000_Unique_List_SAP_NASP_ID_B_Ends.Local_B_End " _
& "HAVING (((Count(TMA.qryA2000_Unique_List_SAP_NASP_ID_B_Ends.SAP_NASP_ID))<>1)))) " _
& "AND ((TMA.tblB0300_Allocations.Inactive)=0)) " _
& "ORDER BY TMA.tblB0300_Allocations.SAP_NASP_ID, TMA.tblB0300_Allocations.Period_From"
With rs
.Open sSQL, cn, 1, 1
If Not .EOF Then
i(1) = .RecordCount
Else
End If
.MoveFirst
Do Until i(2) = i(1) - 1
sValue(1) = !SAP_NASP_ID
.MoveNext
If (!SAP_NASP_ID = sValue(1)) Then
sValue(2) = !Period_From - 1
If (Right(sValue(2), 2) = "00") Then
sValue(2) = Left(sValue(2), 4) - 1 & "12"
End If
.MovePrevious
!Period_To = sValue(2)
.Update
Else
.MovePrevious
End If
i(2) = i(2) + 1
.MoveNext
Loop
.Close
End With
cn.Close
Set cn = Nothing
Which does work but I feel that it would be quicker if something was performed at the Server rather than the Loca PC
Thanks
Geoff
February 15, 2009 at 1:06 pm
I believe that this article which discusses and contains the necessary code to link to previous rows will supply you with the answer. If not ask again
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply