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?
casitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ksaulCommented:
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
0
ksaulCommented:
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.
0
casitAuthor Commented:
Yeah that would be good.  Sry for not getting back to you just other things took a higher priority.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

ksaulCommented:
Here is the CTE example:

WITH MaxDates (ID, MaxDate) AS
 (SELECT ID, MAX(date) MaxDate
  FROM pricing
  WHERE date  = <specific date>
  --WHERE date BETWEEN <date1> AND <date2>
  GROUP BY ID )

UPDATE Company
SET Company.LastDate = MaxDates.MaxDate
FROM Company
INNER JOIN MaxDates on Company.ID = MaxDates.ID
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ksaulCommented:
Did that work for you?
0
casitAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.