Solved

MS SQL Query Help - Filter Records Down

Posted on 2015-02-17
7
29 Views
Last Modified: 2015-02-22
I am connecting to several tables.. I want to find out if employee needs training.  Here is how it is set up.

Employee assigned to a Function Code (Department) and a Job Code (Position).. in a table called JOB_SKL_EDU there are required skills that the FC and JC must have.  When an employee takes training it is then recorded in course attendance (Course ID and Skill ID are identical)..

When I run this query

SELECT     EMPLOYEE.EMPLOYEE_ID, EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME, EMPLOYEE.FUNCTION_CODE, FUNCTION_CODE.DESCRIPTION AS FC_DESCRIP, 
                      EMPLOYEE.JOB_CODE, JOB_CODE.DESCRIPTION AS JC_DESCRIP, JOB_SKL_EDU.SKILL_ID, SKILL.NAME, SKILL.REVISION_LEVEL, SKILL.ISSUE_DATE, 
                      COURSE_ATTENDANCE.DATE_TIME, COURSE_ATTENDANCE.COURSE_ID
FROM         EMPLOYEE INNER JOIN
                      FUNCTION_CODE ON EMPLOYEE.FUNCTION_CODE = FUNCTION_CODE.FUNCTION_CODE INNER JOIN
                      JOB_CODE ON EMPLOYEE.JOB_CODE = JOB_CODE.JOB_CODE INNER JOIN
                      JOB_SKL_EDU ON EMPLOYEE.FUNCTION_CODE = JOB_SKL_EDU.FUNCTION_CODE AND EMPLOYEE.JOB_CODE = JOB_SKL_EDU.JOB_CODE INNER JOIN
                      SKILL ON JOB_SKL_EDU.SKILL_ID = SKILL.SKILL_ID LEFT OUTER JOIN
                      COURSE_ATTENDANCE ON EMPLOYEE.EMPLOYEE_ID = COURSE_ATTENDANCE.EMPLOYEE_ID AND SKILL.SKILL_ID = COURSE_ATTENDANCE.COURSE_ID
WHERE     (EMPLOYEE.STATUS = 'A') AND (NOT (EMPLOYEE.FUNCTION_CODE IS NULL)) AND (NOT (EMPLOYEE.JOB_CODE IS NULL)) AND (SKILL.STATUS = 'C')
ORDER BY EMPLOYEE.EMPLOYEE_ID, EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME

Open in new window


The following example are some of the records returned.

EMPLOYEE_ID	LAST_NAME	FIRST_NAME	FUNCTION_CODE	FC_DESCRIP	JOB_CODE	JC_DESCRIP	SKILL_ID	NAME	REVISION_LEVEL	ISSUE_DATE	DATE_TIME	COURSE_ID
AND09	ANDERSON	MARSHA	50	QUALITY CONTROL	QCTECH1	QUALITY CONTROL TECH LEVEL 1	100.001	DOCUMENT CONTROL	N	2014-10-30 00:00:00.000	2014-01-06 11:17:00.000	100.001
AND09	ANDERSON	MARSHA	50	QUALITY CONTROL	QCTECH1	QUALITY CONTROL TECH LEVEL 1	100.002	EMPLOYEE PRACTICES-SANITATION PROGRAM	W	2015-02-13 00:00:00.000	2014-07-09 09:11:00.000	100.002
AND09	ANDERSON	MARSHA	50	QUALITY CONTROL	QCTECH1	QUALITY CONTROL TECH LEVEL 1	100.002	EMPLOYEE PRACTICES-SANITATION PROGRAM	W	2015-02-13 00:00:00.000	2014-09-10 13:27:00.000	100.002
AND09	ANDERSON	MARSHA	50	QUALITY CONTROL	QCTECH1	QUALITY CONTROL TECH LEVEL 1	100.002	EMPLOYEE PRACTICES-SANITATION PROGRAM	W	2015-02-13 00:00:00.000	2013-12-31 11:25:00.000	100.002
AND09	ANDERSON	MARSHA	50	QUALITY CONTROL	QCTECH1	QUALITY CONTROL TECH LEVEL 1	100.002	EMPLOYEE PRACTICES-SANITATION PROGRAM	W	2015-02-13 00:00:00.000	2015-02-04 11:53:00.000	100.002
AND09	ANDERSON	MARSHA	50	QUALITY CONTROL	QCTECH1	QUALITY CONTROL TECH LEVEL 1	100.031	MASTER MANUFACTURING RECORD GUIDLINES	A	2010-10-27 00:00:00.000	NULL	NULL
AND09	ANDERSON	MARSHA	50	QUALITY CONTROL	QCTECH1	QUALITY CONTROL TECH LEVEL 1	100.039	ALLERGEN CONTROL	B	2013-07-12 00:00:00.000	2014-01-28 10:52:00.000	100.039
AND09	ANDERSON	MARSHA	50	QUALITY CONTROL	QCTECH1	QUALITY CONTROL TECH LEVEL 1	2000.001	GOOD MANUFACTURING PRACTICES (CGMPS)	2015	2015-02-15 00:00:00.000	2014-08-20 10:26:00.000	2000.001
AND09	ANDERSON	MARSHA	50	QUALITY CONTROL	QCTECH1	QUALITY CONTROL TECH LEVEL 1	210.019	CONTROLLED CLEANING AND SANITIZATION COM	A	2012-04-13 00:00:00.000	2014-01-27 15:21:00.000	210.019
AND09	ANDERSON	MARSHA	50	QUALITY CONTROL	QCTECH1	QUALITY CONTROL TECH LEVEL 1	300.006	QUARANTINE POLICY	F	2011-03-04 00:00:00.000	2014-01-27 10:55:00.000	300.006
AND09	ANDERSON	MARSHA	50	QUALITY CONTROL	QCTECH1	QUALITY CONTROL TECH LEVEL 1	300.007	MATERIAL MOVEMENT AND STORAGE	J	2014-03-18 00:00:00.000	2014-01-27 10:55:00.000	300.007
AND09	ANDERSON	MARSHA	50	QUALITY CONTROL	QCTECH1	QUALITY CONTROL TECH LEVEL 1	300.014	CERTIFICATE OF DISPOSAL	F	2014-10-01 00:00:00.000	2014-01-27 10:55:00.000	300.014
AND09	ANDERSON	MARSHA	50	QUALITY CONTROL	QCTECH1	QUALITY CONTROL TECH LEVEL 1	300.022	SUPPLIER QUALIFICATION	H	2014-03-19 00:00:00.000	2014-01-03 11:22:00.000	300.022

Open in new window


Lines 2-5 are times she has taken the course.  I only need the very last time she attended the class or if null then she has never taken the class.  Then if the class has been taken is it later than the issue_date of the skill which would mean she is current or needs updated training.

Looking for best way to get this down so I can write a report that says here are the classes you need to be retrained or trained on.

Thanks for any help.
0
Comment
Question by:DMTechGrooup
  • 3
  • 2
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
This should give you "the latest". Instead of joining directly to the course attendance table, use a derived table that utilizes ROW_NUMBER().
LEFT OUTER JOIN (
      SELECT
            *
          , ROW_NUMBER() OVER (PARTITION BY EMPLOYEE_ID, COURSE_ID ORDER BY DATE_TIME DESC)
      FROM COURSE_ATTENDANCE
) AS CA ON EMPLOYEE.EMPLOYEE_ID = CA.EMPLOYEE_ID
            AND SKILL.SKILL_ID = CA.COURSE_ID
            AND CA.RN = 1

Open in new window


other references to COURSE_ATTENDANCE will also need change to the alias you use (I used CA)

For the other conditions you would use a case expression I think, e.g.
    , CASE
            WHEN CA.COURSE_ID IS NULL THEN 'no course'
            WHEN CA.DATE_TIME < SKILL.ISSUE_DATE THEN 'update this skill'
            ELSE 'ok'
      END

Open in new window


nb: if I use select * it is not a recommendation! merely a way to abbreviate my answer

SELECT
      EMPLOYEE.EMPLOYEE_ID
    , EMPLOYEE.LAST_NAME
    , EMPLOYEE.FIRST_NAME
    , EMPLOYEE.FUNCTION_CODE
    , FUNCTION_CODE.DESCRIPTION AS FC_DESCRIP
    , EMPLOYEE.JOB_CODE
    , JOB_CODE.DESCRIPTION AS JC_DESCRIP
    , JOB_SKL_EDU.SKILL_ID
    , SKILL.NAME
    , SKILL.REVISION_LEVEL
    , SKILL.ISSUE_DATE
    , CA.DATE_TIME
    , CA.COURSE_ID
    , CASE
            WHEN CA.COURSE_ID IS NULL THEN 'no course'
            WHEN CA.DATE_TIME < SKILL.ISSUE_DATE THEN 'update this skill'
            ELSE 'ok'
      END
FROM EMPLOYEE
INNER JOIN FUNCTION_CODE ON EMPLOYEE.FUNCTION_CODE = FUNCTION_CODE.FUNCTION_CODE
INNER JOIN JOB_CODE ON EMPLOYEE.JOB_CODE = JOB_CODE.JOB_CODE
INNER JOIN JOB_SKL_EDU ON EMPLOYEE.FUNCTION_CODE = JOB_SKL_EDU.FUNCTION_CODE
            AND EMPLOYEE.JOB_CODE = JOB_SKL_EDU.JOB_CODE
INNER JOIN SKILL ON JOB_SKL_EDU.SKILL_ID = SKILL.SKILL_ID
LEFT OUTER JOIN (
      SELECT
            *
          , ROW_NUMBER() OVER (PARTITION BY EMPLOYEE_ID, COURSE_ID ORDER BY DATE_TIME DESC)
      FROM COURSE_ATTENDANCE
) AS CA ON EMPLOYEE.EMPLOYEE_ID = CA.EMPLOYEE_ID
            AND SKILL.SKILL_ID = CA.COURSE_ID
            AND CA.RN = 1
WHERE (EMPLOYEE.STATUS = 'A')
      AND (NOT (EMPLOYEE.FUNCTION_CODE IS NULL))
      AND (NOT (EMPLOYEE.JOB_CODE IS NULL))
      AND (SKILL.STATUS = 'C')
ORDER BY EMPLOYEE.EMPLOYEE_ID, EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME

Open in new window

0
 
LVL 24

Author Comment

by:DMTechGrooup
Comment Utility
That was the intention.. its not a shouting thing.. its a style for consistency.  Please do not assume I was trying to shout or get attention, I am offended by that.
0
 
LVL 24

Accepted Solution

by:
DMTechGrooup earned 0 total points
Comment Utility
I figured it out on my own.. used a group by and MAX.

SELECT     EMPLOYEE.EMPLOYEE_ID, EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME, EMPLOYEE.FUNCTION_CODE, FUNCTION_CODE.DESCRIPTION AS FC_DESCRIP, 
                      EMPLOYEE.JOB_CODE, JOB_CODE.DESCRIPTION AS JC_DESCRIP, JOB_SKL_EDU.SKILL_ID, SKILL.NAME, SKILL.REVISION_LEVEL, SKILL.ISSUE_DATE, 
                      MAX(COURSE_ATTENDANCE.DATE_TIME) AS LST_ATTEND
FROM         EMPLOYEE INNER JOIN
                      FUNCTION_CODE ON EMPLOYEE.FUNCTION_CODE = FUNCTION_CODE.FUNCTION_CODE INNER JOIN
                      JOB_CODE ON EMPLOYEE.JOB_CODE = JOB_CODE.JOB_CODE INNER JOIN
                      JOB_SKL_EDU ON EMPLOYEE.FUNCTION_CODE = JOB_SKL_EDU.FUNCTION_CODE AND EMPLOYEE.JOB_CODE = JOB_SKL_EDU.JOB_CODE INNER JOIN
                      SKILL ON JOB_SKL_EDU.SKILL_ID = SKILL.SKILL_ID LEFT OUTER JOIN
                      COURSE_ATTENDANCE ON EMPLOYEE.EMPLOYEE_ID = COURSE_ATTENDANCE.EMPLOYEE_ID AND SKILL.SKILL_ID = COURSE_ATTENDANCE.COURSE_ID
WHERE     (EMPLOYEE.STATUS = 'A') AND (SKILL.STATUS = 'C') AND (COURSE_ATTENDANCE.COURSE_ID IS NULL) AND (NOT (EMPLOYEE.FUNCTION_CODE IS NULL)) AND 
                      (NOT (EMPLOYEE.JOB_CODE IS NULL)) OR
                      (EMPLOYEE.STATUS = 'A') AND (SKILL.STATUS = 'C') AND (NOT (EMPLOYEE.FUNCTION_CODE IS NULL)) AND (NOT (EMPLOYEE.JOB_CODE IS NULL)) AND 
                      (SKILL.ISSUE_DATE > COURSE_ATTENDANCE.DATE_TIME)
GROUP BY EMPLOYEE.EMPLOYEE_ID, EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME, EMPLOYEE.FUNCTION_CODE, FUNCTION_CODE.DESCRIPTION, 
                      EMPLOYEE.JOB_CODE, JOB_CODE.DESCRIPTION, JOB_SKL_EDU.SKILL_ID, SKILL.NAME, SKILL.REVISION_LEVEL, SKILL.ISSUE_DATE
ORDER BY EMPLOYEE.EMPLOYEE_ID, EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Sorry, "shout" is a term associated with using all upper, it was not intended to offend.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
You may want to double check this:
LEFT OUTER JOIN COURSE_ATTENDANCE ON EMPLOYEE.EMPLOYEE_ID = COURSE_ATTENDANCE.EMPLOYEE_ID
                                             AND SKILL.SKILL_ID = COURSE_ATTENDANCE.COURSE_ID

...
        AND (SKILL.ISSUE_DATE > COURSE_ATTENDANCE.DATE_TIME)

If that condition has to be met than the OUTER JOIN becomes an implicit INNER JOIN (> NULL is never going to be true).  If you really want an OUTER JOIN than you have to code it differently.  Let me know if you need help with that.
0
 
LVL 24

Author Closing Comment

by:DMTechGrooup
Comment Utility
Used a group by MAX to be able to get desired results.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

'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 …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 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

11 Experts available now in Live!

Get 1:1 Help Now