Reply from Howler_Fish on Dec 29 at 5:24 PM Don't really know the tables involved, but PS_LM_ENRLMT seems to have the completion date that you are talking about.
Replace the current D.LM_COMPL_DT BETWEEN :2 and :3 with
AND D.LM_COMPL_DT = (SELECT MAX(D_COMP.LM_COMPL_DT) FROM PS_LM_ENRLMT D_COMP
WHERE D_COMP.LM_PERSON_ID = D.LM_PERSON_ID
AND D_COMP.LM_EMPL_RCD = D.LM_EMPL_RCD
AND D_COMP.LM_CI_ID = D.LM_CI_ID /*--*** See note below *** */
AND D_COMP.LM_COMPL_DT BETWEEN :2 AND :3)
This will select the max completion date between the range for the employee.
HOWEVER, it is reliant on E.LM_CRSE_CODE = :1 returning a single LM_CI_ID. If it can return multiple LM_CI_ID values, then the max sub select for D_COMP will return one completion date per LM_CI_ID. If this is the case, you need to replace the line
AND D_COMP.LM_CI_ID = D.LM_CI_ID
with
AND D_COMP.LM_CI_ID IN (SELECT E_COMP.LM_CI_ID FROM PS_LM_CI_TBL E_COMP
WHERE E_COMP.LM_CRSE_CODE = :1 AND E_COMP.EFFDT = (SELECT MAX(E_COMP_ED.EFFDT) FROM PS_LM_CI_TBL E_COMP_ED WHERE E_COMP_ED.LM_CI_ID = E_COMP.LM_CI_ID AND E_COMP_ED.EFFDT <= SYSDATE))
This will allow the sub select to scan all LM_CI_ID's that are attributed to the inbound course code, and of those select that max enrollment in the period.
This is one of those things that gets VERY messy to write in query, but is easy to write via direct SQL views.
| | | ---------------Original Message---------------
From: malmoula
Sent: Thursday, December 29, 2011 2:47 PM
Subject: Max Completion Date
Working on modifying a query in PS/ELM Query Manager
This report is for a specific course and lists out all employees who completed the course in a defined date range.
We would like the query to display only one record per employee, where the employee has taken / completed the course 3 times between the dates.
The last / most current completion date should be included and not the other 2 dates.
For example if am running the query for course XYZ and Emp A has taken the course 3 times this year - 02/01/2011, 06/01/2011 and 11/01/2011, we would like to show only the 11/01/2011 row.
Anyone has any ideas to make it work?
Here is the Query SQL:
SELECT F.LM_HR_EMPLID, G.NAME, A.LM_HR_EMPLID, B.NAME, C.LM_PER_ORG, D.LM_STTS, TO_CHAR(D.LM_ENRL_DT,'YYYY-MM-DD'), TO_CHAR(D.LM_COMPL_DT,'YYYY-MM-DD'), E.LM_CS_LONG_NM, E.LM_CRSE_CODE, TO_CHAR(SYSDATE,'YYYY-MM-DD'),E.LM_CI_ID,TO_CHAR(E.EFFDT,'YYYY-MM-DD')
FROM PS_LM_PERSON A, PS_LM_PERSON_NAME B, PS_LM_PERSON_ATTRB C, PS_LM_ENRLMT D, PS_LM_CI_TBL E, PS_LM_PERSON F, PS_LM_PERSON_NAME G
WHERE A.LM_PERSON_ID = B.LM_PERSON_ID
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_LM_PERSON_NAME B_ED
WHERE B.LM_PERSON_ID = B_ED.LM_PERSON_ID
AND B.LM_NAME_TYPE = B_ED.LM_NAME_TYPE
AND B_ED.EFFDT <= SYSDATE)
AND B.LM_PERSON_ID = C.LM_PERSON_ID
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_LM_PERSON_ATTRB C_ED
WHERE C.LM_PERSON_ID = C_ED.LM_PERSON_ID
AND C.LM_EMPL_RCD = C_ED.LM_EMPL_RCD
AND C_ED.EFFDT <= SYSDATE)
AND C.LM_PERSON_ID = D.LM_PERSON_ID
AND C.LM_EMPL_RCD = D.LM_EMPL_RCD
AND E.LM_CI_ID = D.LM_CI_ID
AND E.EFFDT =
(SELECT MAX(E_ED.EFFDT) FROM PS_LM_CI_TBL E_ED
WHERE E.LM_CI_ID = E_ED.LM_CI_ID
AND E_ED.EFFDT <= SYSDATE)
AND F.LM_PERSON_ID = C.LM_MANAGER_ID
AND F.LM_PERSON_ID = G.LM_PERSON_ID
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_LM_PERSON_NAME G_ED
WHERE G.LM_PERSON_ID = G_ED.LM_PERSON_ID
AND G.LM_NAME_TYPE = G_ED.LM_NAME_TYPE
AND G_ED.EFFDT <= SYSDATE)
AND D.LM_COMPL_DT BETWEEN TO_DATE(:2,'YYYY-MM-DD') AND TO_DATE(:3,'YYYY-MM-DD')
AND E.LM_CRSE_CODE = :1
AND D.LM_COMPL_DT = (SELECT MAX( I.LM_COMPL_DT)
FROM PS_LM_ENRLMT I
HAVING COUNT( I.LM_ACT_ID) > 1)
ORDER BY 2, 3, 8
Thanks a lot for your time. |
| Reply to this email to post your response. __.____._ |   _.____.__ |
No comments:
Post a Comment