asked on

SQL sp help.

I have a db with 2 tables. they are related by a specific column.
They each have an id row that corresponds to each other. The one table holds the company info and then the other table holds pricing info. I need to run through all the rows in the pricing table for a specifc date or date range. And then for the max date for the specific id then I have to update that date in the company table in a last date column that is also assoc with that id. Any ideas on a sp to do this?

ksaul

Can you post the schema and some sample data from the two tables? A possible solution is a join to a subquery that gets the max date. Something like:

UPDATE company
SET company.LastDate = MaxDates.MaxDate
FROM company
INNER JOIN
(SELECT ID, MAX(date) MaxDate
FROM pricing
WHERE date = <specific date>
--WHERE date BETWEEN <date1> AND <date2>
GROUP BY ID) AS MaxDates ON MaxDates.ID = company.ID

ksaul

Was this any help? If you give me the schema I can give you a more specific answer. On 2005 you can also use a CTE (Common table expression) to make the query more readible. I can give an example of that as well if you would like.

casit

ASKER

Yeah that would be good. Sry for not getting back to you just other things took a higher priority.

ASKER CERTIFIED SOLUTION

ksaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ksaul

Did that work for you?

casit

ASKER

I actually figured this out by using a function in my view to return the latest date. That way I don't have to change any actual data.