Results 1 to 6 of 6

Thread: How to match the most recent date with the project

  1. #1
    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!

  2. #2
    Join Date
    May 2008
    Posts
    2,389

    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.

  3. #3
    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).

  4. #4
    Join Date
    May 2008
    Posts
    2,389

    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

  5. #5
    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?

  6. #6
    Join Date
    May 2008
    Posts
    2,389

    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>

Similar Threads

  1. Project End Date
    By SSTemplar in forum Microsoft Project
    Replies: 3
    Last Post: 30-12-2011, 09:10 PM
  2. Task Start Date does not match constraint
    By ctslaton in forum Microsoft Project
    Replies: 2
    Last Post: 17-09-2011, 04:57 AM
  3. MS Project calculation of end date
    By kerryger in forum Windows Software
    Replies: 1
    Last Post: 15-04-2011, 08:11 PM
  4. Replies: 4
    Last Post: 14-05-2010, 12:16 AM
  5. Exactly match date in java
    By Vodka in forum Software Development
    Replies: 5
    Last Post: 13-02-2010, 02:00 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,640,575.08522 seconds with 16 queries