Getting max Commitment_date in a query

I've got a query that I need to get the latest overall Commitment_Date for each Task_Commit_Date.  Here is the query:

------------------------------------------------------------------------------------------------
SELECT Commitment.Commitment_ID, Commitment_Tasks.Task_Commit_Date, Commitment_Date.Commitment_Date
FROM (Commitment INNER JOIN Commitment_Date ON Commitment.Commitment_ID = Commitment_Date.Commitment_ID) INNER JOIN Commitment_Tasks ON Commitment.Commitment_ID = Commitment_Tasks.Commitment_ID
WHERE (((Commitment.Commitment_Completed)=False) AND ((Commitment_Tasks.Task_Completed)=False))
ORDER BY Commitment_Tasks.Task_Commit_Date DESC;
 
Which returns:

Commitment_ID      Task_Commit_Date      Commitment_Date
875                           11/15/2004                11/1/2004
875                           11/15/2004                11/19/2004
875                           11/15/2004                11/1/2004
881                           11/1/2004                   1/1/2005
881                           11/1/2004                       11/20/2004
881                           11/1/2004                       1/1/2005
881                           11/1/2004                       11/20/2004

I would like it to return the lastest Commitment_Date as follows:

Commitment_ID      Task_Commit_Date      Commitment_Date (all dates being the latest)
875                           11/15/2004                11/19/2004
875                           11/15/2004                11/19/2004
875                           11/15/2004                11/19/2004
881                           11/1/2004                   1/1/2005
881                           11/1/2004                       1/1/2005
881                           11/1/2004                       1/1/2005
881                           11/1/2004                       1/1/2005
schmir1Asked:
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.

schmir1Author Commented:
I should have used a better example.  The Task_Commit_Date are not always the same as it appears above.  They could be any date as follows:

Commitment_ID     Task_Commit_Date     Commitment_Date (all dates being the latest)
875                           11/1/2004                11/19/2004
875                           11/12/2004                11/19/2004
875                           11/7/2004                11/19/2004
881                           11/10/2004                   1/1/2005
881                           10/1/2004                      1/1/2005
881                           11/2/2004                      1/1/2005
881                           11/4/2004                      1/1/2005
0
shanesuebsahakarnCommented:
Something like this, I imagine:

SELECT Commitment.Commitment_ID, Commitment_Tasks.Task_Commit_Date, Max(Commitment_Date.Commitment_Date)
FROM (Commitment INNER JOIN Commitment_Date ON Commitment.Commitment_ID = Commitment_Date.Commitment_ID) INNER JOIN Commitment_Tasks ON Commitment.Commitment_ID = Commitment_Tasks.Commitment_ID
WHERE (((Commitment.Commitment_Completed)=False) AND ((Commitment_Tasks.Task_Completed)=False))
GROUP BY Commitment.Commitment_ID
ORDER BY Commitment_Tasks.Task_Commit_Date DESC;

Is that what you were looking for?
0
shanesuebsahakarnCommented:
Sorry, the GROUP BY part should read:
GROUP BY Commitment.Commitment_ID,  Commitment_Tasks.Task_Commit_Date
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

schmir1Author Commented:
Your query only returns the following:

Commitment_ID      Task_Commit_Date      Expr1002
875      11/15/2004      11/19/2004
881      11/1/2004        1/1/2005

That's the same problem that I had?  It only wants to return one record (even when there are more)  for each max Commitment_Date.
0
shanesuebsahakarnCommented:
OK, I think a subquery is called for:

SELECT Commitment.Commitment_ID, Commitment_Tasks.Task_Commit_Date,
    (SELECT Max(Commitment_Date) FROM Commitment_Date As C WHERE C.Commitment_ID=Commitment.Commitment_ID)
FROM (Commitment
INNER JOIN Commitment_Date ON Commitment.Commitment_ID = Commitment_Date.Commitment_ID)
INNER JOIN Commitment_Tasks ON Commitment.Commitment_ID = Commitment_Tasks.Commitment_ID
WHERE Commitment.Commitment_Completed=False
AND Commitment_Tasks.Task_Completed=False
ORDER BY Commitment_Tasks.Task_Commit_Date DESC

I don't understand why you need all records though - in your first example, you end up with 3 identical records for 875.
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
schmir1Author Commented:
My first posting was misleading.  My second posting shows all the task dates are different.  In the real world,  each task can have any date that is put in so it can be the same or different then another task.  Your query works GREAT!!!  Thanks

I added all the fields that need visibility and DISTINCT.  Here is the final query.
--------------------------------------------------------------------------------------------
SELECT DISTINCT Commitment.Commitment_ID, Commitment_Tasks.Commitment_Tasks_ID, Commitment.Project_Name, Commitment.DTL_Owner, Commitment_Tasks.Task, Commitment_Tasks.DTL_Assignee, (SELECT Max(Commitment_Date) FROM Commitment_Date As C WHERE C.Commitment_ID=Commitment.Commitment_ID) AS LastCommitDate, Commitment_Tasks.Task_Commit_Date, Commitment_Tasks.Task_Commit_Date_Accept AS Accepted
FROM (Commitment INNER JOIN Commitment_Date ON Commitment.Commitment_ID = Commitment_Date.Commitment_ID) INNER JOIN Commitment_Tasks ON Commitment.Commitment_ID = Commitment_Tasks.Commitment_ID
WHERE (((Commitment.Commitment_Completed)=False) AND ((Commitment_Tasks.Task_Completed)=False))
ORDER BY Commitment_Tasks.Task_Commit_Date;

Here is the Query Output
----------------------------
Commitment_ID      Commitment_Tasks_ID      Project_Name      DTL_Owner      Task      DTL_Assignee      LastCommitDate      Task_Commit_Date      Accepted
912      1434      FactoryWorks Eval      Bob Schmitt      FactoryW      Bob Schmitt      1/2/2005      1/2/2004      No
881      1227      Gen 2 MiBs      Bob Schmitt      Gen 2 task 1      Bob Schmitt      1/1/2005      11/1/2004      Yes
881      1228      Gen 2 MiBs      Bob Schmitt      Gen 2 task 2      Bob Schmitt      1/1/2005      11/1/2004      Yes
1004      1435      no date      Bob Schmitt      a      Bob Schmitt      1/1/2005      11/1/2004      No
1004      1436      no date      Bob Schmitt      a      Bob Schmitt      1/1/2005      11/1/2004      No
875      1216      MECC Pulse Tester      Bob Schmitt      xxx      Bob Schmitt      11/19/2004      11/15/2004      Yes
875      1433      MECC Pulse Tester      Bob Schmitt      Task2      Bob Schmitt      11/19/2004      11/19/2004      No
1004      1437      no date      Bob Schmitt      c      Bob Schmitt      1/1/2005      1/1/2005      No
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 Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.