Solved

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

Posted on 2016-09-26
4
61 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 21
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

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

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. …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

695 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