Solved

SQL sp help.

Posted on 2007-04-06
6
884 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:casit
  • 4
  • 2
6 Comments
 
LVL 10

Expert Comment

by:ksaul
ID: 18868026
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
 
LVL 10

Expert Comment

by:ksaul
ID: 18882375
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
 

Author Comment

by:casit
ID: 18885877
Yeah that would be good.  Sry for not getting back to you just other things took a higher priority.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 10

Accepted Solution

by:
ksaul earned 500 total points
ID: 18891882
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
 
LVL 10

Expert Comment

by:ksaul
ID: 18952233
Did that work for you?
0
 

Author Comment

by:casit
ID: 18986266
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now