March 20, 2007 at 11:19 am
Hello,
I want to move a vba script to the SQL Server, so I can schedule it.
This data is dumped from live database to the reporting database each evening, from midnight to 2am, roughly.
Rather than use a join to discover the "last sale price" I was going to have a stored procedure update a custom field in the reporting database and store that "last sale price" in the part master table.
What I want to do is move this to a stored procedure.
Can anyone give me a suggestion on what approach to take?
Do you open multiple recordsets in a stored procedure?
Is this something better done on the "tranformation" of the data in the DTS package?
PS. if there are easier ways to achieve this in SQL 2005, please let me know. We're moving from 7 to 2005 by beginning of summer, and I can test in the meantime.
here's the vba code I've used for a long time:
Public Function UpdateLastSale()
Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim i As Integer
Set db = CurrentDb
Dim strPart As String
Dim strSQL As String
Set rs = db.OpenRecordset("SELECT partnum,curLastSale FROM part WHERE part.class = 'MANF'")
rs.MoveLast
rs.MoveFirst
For i = 1 To rs.recordcount
strSQL = "SELECT TOP 1 invcdtl.unitprice FROM invcdtl WHERE invcdtl.partnum = '" & rs!PARTNUM & "' ORDER BY invcdtl.shipdate DESC;"
Set rs2 = db.OpenRecordset(strSQL)
rs.Edit
If rs2.EOF = False Then
rs!curLastSale = rs2!UNITprice
Else
rs!curLastSale = 0
End If
rs.Update
rs2.Close
rs.MoveNext
Next
End Function
March 20, 2007 at 11:27 am
Here's the basic idea of the select you need :
SELECT dtLastSales.PartNum, dtLastSales.mxShipDate, inv.UnitPrice FROM invcdtl inv INNER JOIN
(Select PartNum, MAX(ShipDate) AS mxShipDate FROM dbo.Invcdtl GROUP BY PartNum) dtLastSales
on inv.PartNum = dtLastSales.PartNum AND inv.ShipDate = dtLastSales.mxShipDate
Keep in mind that this can gove you some ties and you must decide how you want to deal with them.
Does that solve you problem?
March 20, 2007 at 11:28 am
I forgot one step. You need to select from parts left join that query and use isnull(currentprice, 0) so that all products have a price. Post back if you need further help with this.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply