Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 04-05-2009
Member
 
Join Date: May 2008
Posts: 351
How to match the most recent date with the project

Here is my query:

Code:
SELECT idCompany project, max (date) 
FROM 'db_prospect_client' . 'prospect_appeal' 
WHERE project <> 'no' 
GROUP BY idCompany
At the base the complaint is more complex but I simplify for understanding and oversimplification. Basically, this query is supposed to return me the project (it is a number corresponding to the number of months), all grouped by idCompany and the maximum date. I'm the most recent project for a corporation.

Big problem: it includes by Company, it selects the last but the project number is not that corresponding to the most recent date. How to match the most recent date and project that is linked?

I hope I was clear because it is sometimes hard to explain!
Reply With Quote
  #2  
Old 04-05-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: How to match the most recent date with the project

Your query will return the max date and grouped by idCompany project. You have not put in your project GROUP BY, its a syntax error and most of the DBMS did not accept it, you do not specify your own but I guess it is MySQL.

For your problem, you need to recover the date for each idCompany max, then do a join on the couple date/idCompany obtained.

Code:
SELECT idCompany, project, date 
FROM 'db_prospect_client' . 'prospect_appeal' 
JOIN (SELECT idCompany, max (date) AS datemax 
FROM 'db_prospect_client' . 'prospect_appeal'
WHERE project <> 'no' 
GROUP BY idCompany) appealmax 
ON appeal.idCompany = appealmax.idCompany
AND appeal.date = appealmax.datemax
Incidentally, it is strongly discouraged of number column 'date' is a reserved word in SQL.
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
  #3  
Old 04-05-2009
Member
 
Join Date: May 2008
Posts: 351
Re: How to match the most recent date with the project

Yes I know for the date field (it is not me who has created the database). Well here I have received your request I get an error that refers me idCompany ambiguous (I imagine it must specify the table before).

I'll see what I can do (it is a syntax that I have never used).
Reply With Quote
  #4  
Old 04-05-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: How to match the most recent date with the project

Yes, I made a mistake by not appointing the table in front of the selected columns. This gives:

Code:
  SELECT appeal.idCompany, appeal.projet, appeal.date 
  FROM 'db_prospect_client'. 'prospect_appeal' 'call 
  JOIN (SELECT idCompany, max (date) AS datemax 
       FROM 'db_prospect_client'. 'prospect_appeal' 
       WHERE project <> 'no' 
       GROUP BY idCompany) appealmax 
  ON appeal.idCompany = appealmax.idCompany 
  AND appeal.date = appealmax.datemax
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
  #5  
Old 04-05-2009
Member
 
Join Date: May 2008
Posts: 351
Re: How to match the most recent date with the project

Yes, the query works fine. Now, a small question. I have to select the contact name, company and telephone correspondingly. Short, or select them and where to join?
Reply With Quote
  #6  
Old 04-05-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: How to match the most recent date with the project

We must make a second joint on the table containing this information:

Code:
SELECT appeal.idCompany, project, date, prospect, OtherTable.NameContact, OtherTable.telephone 
FROM 'db_prospect_client'.'prospect_appeal' call 
JOIN ( 
SELECT prospect_appel.idCompany, max (date) AS datemax 
FROM 'db_prospect_client'. 'prospect_appeal' 
WHERE project <> 'no' 
GROUP BY idCompany 
) appealmax 
ON appeal.idCompany = appealmax.idCompany
AND appeal.date = appealmax.datemax 
JOIN Other
ON <Conditions of joint>
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to match the most recent date with the project"
Thread Thread Starter Forum Replies Last Post
Project End Date SSTemplar Microsoft Project 3 30-12-2011 09:10 PM
Task Start Date does not match constraint ctslaton Microsoft Project 2 17-09-2011 04:57 AM
MS Project calculation of end date kerryger Windows Software 1 15-04-2011 08:11 PM
Select Unique Values (Selecting The Most Recent Record Based On Date) Leeland Software Development 4 14-05-2010 12:16 AM
Exactly match date in java Vodka Software Development 5 13-02-2010 02:00 AM


All times are GMT +5.5. The time now is 09:12 AM.