Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Oct 2009
    Posts
    16

    Unanswered: 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')

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    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
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    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
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    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

  4. #4
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?

  6. #6
    Join Date
    Oct 2009
    Posts
    16
    Yes, you are correct. Sounds like I might need to use a Group by.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope. You currently have a sub-query. You actually require a correlated sub-query. Know how to make it correlated?

  8. #8
    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.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The same syntax

  10. #10
    Join Date
    Oct 2009
    Posts
    16
    Excellent. I'll let you know how it goes.

  11. #11
    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')

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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'.

  13. #13
    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.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.

  15. #15
    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')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •