Solved

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

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

10 Experts available now in Live!

Get 1:1 Help Now