| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
03-11-10, 15:19
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 16
|
|
|
Max() date with union
|
|
Colleagues,
I am running into a problem when trying to retrieve the latest date from the REVIEW.ACTUAL_DATE AS "Pre-Ob 1" column. Within the JOIN Clause, I am using the MAX function. The problem is, I need to show both null values and the latest date values. With the MAX function it only shows all null values. I believe there is an easy fix for it, but I can't for the life of me think what it is. I've highlighted the area in red.
Thanks in advance.
~subserved
Code:
SELECT PRSYSTEM.NAME AS "School",
EMPLOYEE.DEPARTMENT AS "Dept",
EMPLOYEE.LAST_NAME AS "Last Name",
EMPLOYEE.FIRST_NAME AS "First Name",
PAPOSITION.DESCRIPTION AS "Position",
HREMPUSF.A_FIELD AS "Tenure Status",
REVIEW.ACTUAL_DATE AS "Pre-Ob 1"
FROM EMPLOYEE EMPLOYEE
LEFT OUTER JOIN
REVIEW
ON EMPLOYEE.EMPLOYEE = REVIEW.EMPLOYEE
AND REVIEW.CODE = 'PRE-OB 1 '
AND REVIEW.ACTUAL_DATE = (SELECT MAX (REVIEW.ACTUAL_DATE) FROM REVIEW)
INNER JOIN
PRSYSTEM PRSYSTEM
ON PRSYSTEM.PROCESS_LEVEL = EMPLOYEE.PROCESS_LEVEL
INNER JOIN
PAPOSITION PAPOSITION
ON PAPOSITION.R_POSITION = EMPLOYEE.R_POSITION
LEFT OUTER JOIN
HREMPUSF HREMPUSF
ON HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE
WHERE EMPLOYEE.EMP_STATUS NOT IN
('T1', 'T2', 'T3', 'T4', 'TC', 'SU', 'R1', 'L4', 'P6', 'P7')
UNION
SELECT PRSYSTEM.NAME AS "School",
EMPLOYEE.DEPARTMENT AS "Dept",
EMPLOYEE.LAST_NAME AS "Last Name",
EMPLOYEE.FIRST_NAME AS "First Name",
PAPOSITION.DESCRIPTION AS "Position",
HREMPUSF.A_FIELD AS "Tenure Status",
REVIEW.ACTUAL_DATE AS "Pre-Ob 1"
FROM EMPLOYEE EMPLOYEE
LEFT OUTER JOIN
REVIEW
ON EMPLOYEE.EMPLOYEE = REVIEW.EMPLOYEE
AND REVIEW.CODE = 'PRE-OB 1 '
AND REVIEW.ACTUAL_DATE = (SELECT MAX (REVIEW.ACTUAL_DATE) FROM REVIEW) INNER JOIN
PRSYSTEM PRSYSTEM
ON PRSYSTEM.PROCESS_LEVEL = EMPLOYEE.PROCESS_LEVEL
INNER JOIN
PAPOSITION PAPOSITION
ON PAPOSITION.R_POSITION = EMPLOYEE.R_POSITION
LEFT OUTER JOIN
HREMPUSF HREMPUSF
ON HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE
WHERE EMPLOYEE.R_POSITION IN ('0000-072')
|
|
03-11-10, 20:49
|
|
(Making Your Life Easy)
|
|
Join Date: Feb 2004
Location: New Zealand
Posts: 1,110
|
|
why break it down to a number of querys
that what i do
1st query get most of the data right
2nd query the 1st query and so on
__________________
hope this help
See clear as mud
StePhan McKillen
the aim is store once, not store multiple times
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003
VB based on my own environment: vb6 sp5
ASP based on my own environment: 5.6
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
MYLE
|
|
03-11-10, 23:36
|
|
Registered User
|
|
Join Date: Sep 2001
Location: Chicago, Illinois, USA
Posts: 548
|
|
|
|
Are the two SELECTs forming the UNION identical except for the WHERE clause?
If so, then why two queries requiring two passes of the tables and all of those joins?
I know that this doesn't answer your questions, but I like to cut these problems down to their minimal size first, and then solve the problem.
Can you also restate your problem? Is it that the (SELECT MAX(REVIEW.ACTUAL is returning a NULL when you believe there are qualifying records with actual dates in them?
__________________
Ken
Maverick Software Design
(847) 864-3600 x2
|
|
03-12-10, 11:09
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 16
|
|
Actually they are not identical. I just posted only the information that I needed since my query was too large to add to the thread. I just shortened up the query to ask the question. Basically I am trying to pull the latest evaluation by using the max() function within a date field instead of last year's employee evaluation. I do know there are values in there if I query without the max() function. With it, the field shows only null values. I am wondering if I would have to adjust the format in my query to show how the dates should show up, but I'm not sure.
|
|
03-12-10, 11:48
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
What is happening is it is getting the last review date overall, not the last review date for that employee. I assume you want the latter right?
|
|
03-12-10, 11:51
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 16
|
|
Yes, you are correct. Sounds like I might need to use a Group by.
|
|
03-12-10, 11:53
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
Nope. You currently have a sub-query. You actually require a correlated sub-query. Know how to make it correlated?
|
|
03-12-10, 12:02
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 16
|
|
I've done correlated subqueries within a where condition so I am assuming it would similar in a join syntax.
|
|
03-12-10, 12:03
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
The same syntax
|
|
03-12-10, 12:05
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 16
|
|
Excellent. I'll let you know how it goes.
|
|
03-12-10, 12:24
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 16
|
|
It isn't pulling nulls any longer but it is still pulling all the dates within the summative column. Here is the full query without the added union and second query. I've highlighted the correlated subquery in the join. I believe this is what you were referring to.
Code:
SELECT PRSYSTEM.NAME AS "School",
EMPLOYEE.DEPARTMENT AS "Dept",
EMPLOYEE.LAST_NAME AS "Last Name",
EMPLOYEE.FIRST_NAME AS "First Name",
PAPOSITION.DESCRIPTION AS "Position",
HREMPUSF.A_FIELD AS "Tenure Status",
REVIEW.ACTUAL_DATE AS "Pre-Ob 1",
REVIEW_1.ACTUAL_DATE AS "Obsv 1",
REVIEW_2.ACTUAL_DATE AS "PostConf 1",
REVIEW_3.ACTUAL_DATE AS "Pre-Ob 2",
REVIEW_4.ACTUAL_DATE AS "Obsv 2",
REVIEW_5.ACTUAL_DATE AS "PostConf 2",
REVIEW_6.ACTUAL_DATE AS "Pre-Ob 3",
REVIEW_7.ACTUAL_DATE AS "Obsv 3",
REVIEW_8.ACTUAL_DATE AS "PostConf 3",
REVIEW_9.ACTUAL_DATE AS "Pre Ob 4",
REVIEW_10.ACTUAL_DATE AS "Obsv 4",
REVIEW_11.ACTUAL_DATE AS "PostConf 4",
REVIEW_12.ACTUAL_DATE AS "Mentor 1",
REVIEW_13.ACTUAL_DATE AS "Mentor 2",
REVIEW_14.ACTUAL_DATE AS "Summative"
FROM EMPLOYEE EMPLOYEE
LEFT OUTER JOIN
REVIEW
ON EMPLOYEE.EMPLOYEE =
REVIEW.EMPLOYEE
AND REVIEW.CODE =
'PRE-OB 1 '
LEFT OUTER JOIN
REVIEW REVIEW_1
ON EMPLOYEE.EMPLOYEE =
REVIEW_1.EMPLOYEE
AND REVIEW_1.CODE =
'OBSV1'
LEFT OUTER JOIN
REVIEW REVIEW_2
ON EMPLOYEE.EMPLOYEE =
REVIEW_2.EMPLOYEE
AND REVIEW_2.CODE =
'POSTCONF 1'
LEFT OUTER JOIN
REVIEW REVIEW_3
ON EMPLOYEE.EMPLOYEE =
REVIEW_3.EMPLOYEE
AND REVIEW_3.CODE =
'PRE-OB 2 '
LEFT OUTER JOIN
REVIEW REVIEW_4
ON EMPLOYEE.EMPLOYEE =
REVIEW_4.EMPLOYEE
AND REVIEW_4.CODE = 'OBSV2'
LEFT OUTER JOIN
REVIEW REVIEW_5
ON EMPLOYEE.EMPLOYEE =
REVIEW_5.EMPLOYEE
AND REVIEW_5.CODE = 'POSTCONF 2'
LEFT OUTER JOIN
REVIEW REVIEW_6
ON EMPLOYEE.EMPLOYEE =
REVIEW_6.EMPLOYEE
AND REVIEW_6.CODE = 'PRE-OB 3 '
LEFT OUTER JOIN
REVIEW REVIEW_7
ON EMPLOYEE.EMPLOYEE = REVIEW_7.EMPLOYEE
AND REVIEW_7.CODE = 'OBSV3'
LEFT OUTER JOIN
REVIEW REVIEW_8
ON EMPLOYEE.EMPLOYEE = REVIEW_8.EMPLOYEE
AND REVIEW_8.CODE = 'POSTCONF 3'
LEFT OUTER JOIN
REVIEW REVIEW_9
ON EMPLOYEE.EMPLOYEE = REVIEW_9.EMPLOYEE
AND REVIEW_9.CODE = 'PRE-OB 4 '
LEFT OUTER JOIN
REVIEW REVIEW_10
ON EMPLOYEE.EMPLOYEE = REVIEW_10.EMPLOYEE
AND REVIEW_10.CODE = 'OBSV4'
LEFT OUTER JOIN
REVIEW REVIEW_11
ON EMPLOYEE.EMPLOYEE = REVIEW_11.EMPLOYEE
AND REVIEW_11.CODE = 'POSTCONF 4'
LEFT OUTER JOIN
REVIEW REVIEW_12
ON EMPLOYEE.EMPLOYEE = REVIEW_12.EMPLOYEE
AND REVIEW_12.CODE = 'MENTOR1'
LEFT OUTER JOIN
REVIEW REVIEW_13
ON EMPLOYEE.EMPLOYEE = REVIEW_13.EMPLOYEE
AND REVIEW_13.CODE = 'MENTOR2'
LEFT OUTER JOIN
REVIEW REVIEW_14
ON EMPLOYEE.EMPLOYEE = REVIEW_14.EMPLOYEE
AND REVIEW_14.CODE = 'SUMMATIVE'
AND REVIEW_14.ACTUAL_DATE = (SELECT MAX(T.ACTUAL_DATE) FROM REVIEW T WHERE T.ACTUAL_DATE = REVIEW_14.ACTUAL_DATE) INNER JOIN
PRSYSTEM PRSYSTEM
ON PRSYSTEM.PROCESS_LEVEL = EMPLOYEE.PROCESS_LEVEL
INNER JOIN
PAPOSITION PAPOSITION
ON PAPOSITION.R_POSITION = EMPLOYEE.R_POSITION
LEFT OUTER JOIN
HREMPUSF HREMPUSF
ON HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE
WHERE EMPLOYEE.EMP_STATUS NOT IN
('T1', 'T2', 'T3', 'T4', 'TC', 'SU', 'R1', 'L4', 'P6', 'P7')
|
|
03-12-10, 12:27
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
I think there might be a few issues here but first off, you are now getting the last review date for the employee not the last review date for the employee coded 'SUMMATIVE'.
|
|
03-12-10, 13:13
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 16
|
|
I am getting all the review dates now including summative but not the lastest date under summative. It seems that the max() function isn't working.
|
|
03-12-10, 13:18
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
MAX() has worked fine for many years, I can assure you. I don't think we should be writing to Microsoft quite yet 
Please post your query now. As I said, there might be a few issues that you might not realise apply to the SQL you have written.
|
|
03-12-10, 13:27
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 16
|
|
Here is the query. By the way thanks for taking a look at it.
Code:
SELECT PRSYSTEM.NAME AS "School",
EMPLOYEE.DEPARTMENT AS "Dept",
EMPLOYEE.LAST_NAME AS "Last Name",
EMPLOYEE.FIRST_NAME AS "First Name",
PAPOSITION.DESCRIPTION AS "Position",
HREMPUSF.A_FIELD AS "Tenure Status",
REVIEW.ACTUAL_DATE AS "Pre-Ob 1",
REVIEW_1.ACTUAL_DATE AS "Obsv 1",
REVIEW_2.ACTUAL_DATE AS "PostConf 1",
REVIEW_3.ACTUAL_DATE AS "Pre-Ob 2",
REVIEW_4.ACTUAL_DATE AS "Obsv 2",
REVIEW_5.ACTUAL_DATE AS "PostConf 2",
REVIEW_6.ACTUAL_DATE AS "Pre-Ob 3",
REVIEW_7.ACTUAL_DATE AS "Obsv 3",
REVIEW_8.ACTUAL_DATE AS "PostConf 3",
REVIEW_9.ACTUAL_DATE AS "Pre Ob 4",
REVIEW_10.ACTUAL_DATE AS "Obsv 4",
REVIEW_11.ACTUAL_DATE AS "PostConf 4",
REVIEW_12.ACTUAL_DATE AS "Mentor 1",
REVIEW_13.ACTUAL_DATE AS "Mentor 2",
REVIEW_14.ACTUAL_DATE AS "Summative"
FROM EMPLOYEE EMPLOYEE
LEFT OUTER JOIN
REVIEW
ON EMPLOYEE.EMPLOYEE =
REVIEW.EMPLOYEE
AND REVIEW.CODE =
'PRE-OB 1 '
LEFT OUTER JOIN
REVIEW REVIEW_1
ON EMPLOYEE.EMPLOYEE =
REVIEW_1.EMPLOYEE
AND REVIEW_1.CODE =
'OBSV1'
LEFT OUTER JOIN
REVIEW REVIEW_2
ON EMPLOYEE.EMPLOYEE =
REVIEW_2.EMPLOYEE
AND REVIEW_2.CODE =
'POSTCONF 1'
LEFT OUTER JOIN
REVIEW REVIEW_3
ON EMPLOYEE.EMPLOYEE =
REVIEW_3.EMPLOYEE
AND REVIEW_3.CODE =
'PRE-OB 2 '
LEFT OUTER JOIN
REVIEW REVIEW_4
ON EMPLOYEE.EMPLOYEE =
REVIEW_4.EMPLOYEE
AND REVIEW_4.CODE = 'OBSV2'
LEFT OUTER JOIN
REVIEW REVIEW_5
ON EMPLOYEE.EMPLOYEE =
REVIEW_5.EMPLOYEE
AND REVIEW_5.CODE = 'POSTCONF 2'
LEFT OUTER JOIN
REVIEW REVIEW_6
ON EMPLOYEE.EMPLOYEE =
REVIEW_6.EMPLOYEE
AND REVIEW_6.CODE = 'PRE-OB 3 '
LEFT OUTER JOIN
REVIEW REVIEW_7
ON EMPLOYEE.EMPLOYEE = REVIEW_7.EMPLOYEE
AND REVIEW_7.CODE = 'OBSV3'
LEFT OUTER JOIN
REVIEW REVIEW_8
ON EMPLOYEE.EMPLOYEE = REVIEW_8.EMPLOYEE
AND REVIEW_8.CODE = 'POSTCONF 3'
LEFT OUTER JOIN
REVIEW REVIEW_9
ON EMPLOYEE.EMPLOYEE = REVIEW_9.EMPLOYEE
AND REVIEW_9.CODE = 'PRE-OB 4 '
LEFT OUTER JOIN
REVIEW REVIEW_10
ON EMPLOYEE.EMPLOYEE = REVIEW_10.EMPLOYEE
AND REVIEW_10.CODE = 'OBSV4'
LEFT OUTER JOIN
REVIEW REVIEW_11
ON EMPLOYEE.EMPLOYEE = REVIEW_11.EMPLOYEE
AND REVIEW_11.CODE = 'POSTCONF 4'
LEFT OUTER JOIN
REVIEW REVIEW_12
ON EMPLOYEE.EMPLOYEE = REVIEW_12.EMPLOYEE
AND REVIEW_12.CODE = 'MENTOR1'
LEFT OUTER JOIN
REVIEW REVIEW_13
ON EMPLOYEE.EMPLOYEE = REVIEW_13.EMPLOYEE
AND REVIEW_13.CODE = 'MENTOR2'
LEFT OUTER JOIN
REVIEW REVIEW_14
ON EMPLOYEE.EMPLOYEE = REVIEW_14.EMPLOYEE
AND REVIEW_14.CODE = 'SUMMATIVE'
AND REVIEW_14.ACTUAL_DATE = (SELECT MAX(T.ACTUAL_DATE) FROM REVIEW T WHERE T.ACTUAL_DATE = REVIEW_14.ACTUAL_DATE)
INNER JOIN
PRSYSTEM PRSYSTEM
ON PRSYSTEM.PROCESS_LEVEL = EMPLOYEE.PROCESS_LEVEL
INNER JOIN
PAPOSITION PAPOSITION
ON PAPOSITION.R_POSITION = EMPLOYEE.R_POSITION
LEFT OUTER JOIN
HREMPUSF HREMPUSF
ON HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE
WHERE EMPLOYEE.EMP_STATUS NOT IN
('T1', 'T2', 'T3', 'T4', 'TC', 'SU', 'R1', 'L4', 'P6', 'P7')
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Max() date with union
