Okay so I'm struggling here. We have a table that keeps track of a certain user ID. One row has the ID, a second row has the inactive ID. It looks kind of like this:
B.MISC_INFO | Date | B.MISC_VALUE |
---|---|---|
Active | 1/1/20 | BXXXX |
Inactive | 1/1/21 | BXXXX |
Active | 1/1/22 | B2XXX |
I create a report using query manager and it pulls in both active statuses. I need it to only pull in the active status without a corresponding inactive status (in the example above, the 'B2XXX' value).
Right now the SQL on the view SQL tab looks like this:
SELECT DISTINCT A.EMPLID, A.NAME, B.MISC_INFO, B.MISC_VALUE
FROM ((PS_EMPLOYEES A INNER JOIN PS_EMPLMT_SRCH_QRY A1 ON (A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.OPRID = 'XXXXXXXX' )) LEFT OUTER JOIN PS_FTI_EMP_MISC2 B ON A.EMPLID = B.EMPLID AND B.MISC_INFO = 'Active' )
WHERE ( ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_EMPLOYEES A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_EMPLOYEES A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT)
AND B.MISC_VALUE LIKE 'B%' ))
I've thought about writing a case statement but I can't figure it out.
There are a couple of ways to do this. If you are on PeopleTools 8.56 or later, you can first write a query that pulls all active statuses. Drop the effective dated logic for this one. You would then use composite query to query your query. This will treat the inner query like an inline view. You would then apply effective dated logic in composite query.
Another option is to put the active status criteria in the effective dated logic. For this to work, you won't be able to use standard effective dated logic. You will need to create your own subquery criteria on the EFFDT field.