Solved

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

Posted on 2016-09-26
4
44 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
  • 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 19
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
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 …

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now