max date in query

[Mike Hughes]

The query is selecting (amoung other things) people that have an NOLDBA_INT_EMPLOYMENT_HISTORY.DATE_END which is null (in this case 12/31/9999). Some people have more that one NOLDBA_INT_EMPLOYMENT_HISTORY.DATE_END which are null so I only want to view the the max NOLDBA_INT_EMPLOYMENT_HISTORY.DT_SOURCE_RECEIVED showing me only one NOLDBA_INT_EMPLOYMENT_HISTORY.DT_SOURCE_RECEIVED.

Somehow I need to enter the MAX NOLDBA_INT_EMPLOYMENT_HISTORY.DT_SOURCE_RECEIVED into this query.

Thanks, Mike



SELECT DISTINCT
NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE,
NOLDBA_INT_CASE_STATUS.WORKER_ID,
NOLDBA_INT_CASE_STATUS.CASE_ID,
NOLDBA_INT_CASE_MEMBER.MEMBER_ID,
NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_FIRST,
NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_LAST,
NOLDBA_CASE_ROLLUP.DT_LAST_PAY,
Sum(NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_OWED-NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_PAID) AS ARREARS,
NOLDBA_INT_EMPLOYMENT_HISTORY.DT_SOURCE_RECEIVED,
NOLDBA_INT_EMPLOYMENT_HISTORY.DATE_END


FROM (((NOLDBA_INT_CASE_MEMBER INNER JOIN NOLDBA_INT_CASE_STATUS ON NOLDBA_INT_CASE_MEMBER.CASE_ID = NOLDBA_INT_CASE_STATUS.CASE_ID) INNER JOIN NOLDBA_CASE_ROLLUP ON NOLDBA_INT_CASE_STATUS.CASE_ID = NOLDBA_CASE_ROLLUP.ID_CASE) INNER JOIN NOLDBA_INT_MEMBER_DEMOGRAPHIC ON NOLDBA_INT_CASE_MEMBER.MEMBER_ID = NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEMBER_ID) INNER JOIN NOLDBA_INT_EMPLOYMENT_HISTORY ON NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEMBER_ID = NOLDBA_INT_EMPLOYMENT_HISTORY.MEMBER_ID


WHERE NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE=[DISTRICT OFFICE] AND
NOLDBA_INT_CASE_STATUS.CASE_STATUS="O" AND
NOLDBA_INT_CASE_MEMBER.RELATION_CODE="A" AND
NOLDBA_CASE_ROLLUP.DT_LAST_PAY<Now()-45 AND
NOLDBA_INT_CASE_STATUS.CASE_ID="20081469P" AND
NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_OWED-NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_PAID >500 AND
NOLDBA_INT_EMPLOYMENT_HISTORY.DATE_END=#12/31/9999#



GROUP BY NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE, NOLDBA_INT_CASE_STATUS.WORKER_ID, NOLDBA_INT_CASE_STATUS.CASE_ID, NOLDBA_INT_CASE_MEMBER.MEMBER_ID, NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_FIRST, NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_LAST, NOLDBA_CASE_ROLLUP.DT_LAST_PAY, NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_OWED, NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_PAID, NOLDBA_INT_EMPLOYMENT_HISTORY.DT_SOURCE_RECEIVED, NOLDBA_INT_EMPLOYMENT_HISTORY.DATE_END



ORDER BY NOLDBA_INT_CASE_STATUS.WORKER_ID;

 

[Brianwarnock]

You will need a first query which finds the max value for the field within any grouping that applies to it, it maybe just the persons id. This is then joined in the final query to limit the select to only the records in the group that match with this value .

Brian