Solved

SQL how to join a MAX on a SELECT sub-query ?

Posted on 2016-09-26
4
56 Views
Last Modified: 2016-09-26
Hi.  Forgive the title I gave this question ... I'm not sure how to ask it except through this example ---
I have a table of employees EMP:
EmpID   LastName   Status
------|----------|-------
39    | Carter   | A
40    | Reagan   | I
41    | Bush     | A
42    | Clinton  | A
44    | Obama    | A

Open in new window

and a table of their dates of attendance ATTEND:
id  EmpID   Attended
---|------|-----------
1  | 44   | 08/01/2016
2  | 42   | 06/01/2016
3  | 41   | 06/01/2016
4  | 40   | 04/01/2016
5  | 39   | 04/01/2016
6  | 39   | 03/01/2016

Open in new window

I need to create a SELECT that shows which active status "A" employees have not attended since 05/01/2016  (mm/dd/yyyy).  The result, I imagine, should look like this:
EmpID   LastName   lastdate
------|----------|-----------
39    | Carter   | 04/01/2016

Open in new window

I've tried several ways of doing this with joined "select" statements, but none seem to work right.
Any suggestions?
Thanks!
0
Comment
Question by:Rob Rudloff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
YZlat earned 500 total points
ID: 41816889
try

SELECT e.EmpID, e.LastName, a.Lastdate FROM EMP as e
INNER JOIN (SELECT EmpID, MAX(Attended) As Lastdate FROM ATTEND GROUP BY EmpID) as a ON e.EmpID=a.EmpID
WHERE e.Status='A' AND a.Lastdate < '05/01/2016'

Open in new window

0
 
LVL 20
ID: 41816900
you can also use criteria under the date
     dMax("Attended", "Attend", "EmpID=" & [EmpID])

Open in new window

0
 

Author Comment

by:Rob Rudloff
ID: 41816909
Thanks YZlat!   Seems simple now that you have shown me.  (I was making it way too complicated, and I had the syntax of my JOIN wrong.)
0
 
LVL 35

Expert Comment

by:YZlat
ID: 41816931
Glad I could help
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

763 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