Using a loop of one recordset to update another in a stored procedure

Question

Post reply

Using a loop of one recordset to update another in a stored procedure

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

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.