[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Getting max Commitment_date in a query

Posted on 2004-11-30
6
Medium Priority
?
260 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:schmir1
  • 3
  • 3
6 Comments
 

Author Comment

by:schmir1
ID: 12711432
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12711620
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12711625
Sorry, the GROUP BY part should read:
GROUP BY Commitment.Commitment_ID,  Commitment_Tasks.Task_Commit_Date
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:schmir1
ID: 12711885
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
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 2000 total points
ID: 12713702
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
 

Author Comment

by:schmir1
ID: 12737918
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

834 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