accrual date

[Mike Hughes]

A case could have more than one next accrual date returned that is greater than now( ). I only want the first accrual date greater than now( ).

Is there someway to do that with this query? Thanks Mike

SELECT
NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE AS DO,
NOLDBA_INT_CASE_STATUS.WORKER_ID AS WORKER,
NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_LAST AS [LAST NAME],
NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_FIRST AS [FIRST NAME],
NOLDBA_INT_CASE_STATUS.CASE_ID AS [CASE ID],
NOLDBA_INT_CASE_STATUS.CASE_STATUS AS [CASE STATUS],
NOLDBA_CASE_ROLLUP.DT_LAST_PAY AS [LAST PAY DATE],
NOLDBA_RECEIPT.AMT_RECEIPT AS [RECEIPT AMOUNT],
NOLDBA_RECEIPT.CD_SOURCE_RECEIPT AS [RECT TYPE],
Sum(NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_OWED-NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_PAID) AS BALANCE,
NOLDBA_OBLIGATION.DT_ACCRUAL_NEXT AS [NEXT ACCRUAL] INTO [TABLE 1]

FROM
((((NOLDBA_INT_CASE_STATUS INNER JOIN NOLDBA_INT_CASE_MEMBER ON NOLDBA_INT_CASE_STATUS.CASE_ID = NOLDBA_INT_CASE_MEMBER.CASE_ID) INNER JOIN NOLDBA_INT_MEMBER_DEMOGRAPHIC ON NOLDBA_INT_CASE_MEMBER.MEMBER_ID = NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEMBER_ID) INNER JOIN NOLDBA_CASE_ROLLUP ON NOLDBA_INT_CASE_STATUS.CASE_ID = NOLDBA_CASE_ROLLUP.ID_CASE) INNER JOIN NOLDBA_RECEIPT ON (NOLDBA_CASE_ROLLUP.DT_LAST_PAY = NOLDBA_RECEIPT.DT_RECEIPT) AND (NOLDBA_CASE_ROLLUP.ID_CASE = NOLDBA_RECEIPT.ID_CASE)) INNER JOIN NOLDBA_OBLIGATION ON NOLDBA_CASE_ROLLUP.ID_CASE = NOLDBA_OBLIGATION.ID_CASE

WHERE
(((NOLDBA_INT_CASE_STATUS.WORKER_ID)=[WORKER]) AND ((NOLDBA_INT_CASE_MEMBER.RELATION_CODE)="A"))

GROUP BY NOLDBA_INT_CASE_STATUS.IV_D_DO_CODE, NOLDBA_INT_CASE_STATUS.WORKER_ID, NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_LAST, NOLDBA_INT_MEMBER_DEMOGRAPHIC.NAME_FIRST, NOLDBA_INT_CASE_STATUS.CASE_ID, NOLDBA_INT_CASE_STATUS.CASE_STATUS, NOLDBA_CASE_ROLLUP.DT_LAST_PAY, NOLDBA_RECEIPT.AMT_RECEIPT, NOLDBA_RECEIPT.CD_SOURCE_RECEIPT, NOLDBA_OBLIGATION.DT_ACCRUAL_NEXT, NOLDBA_INT_CASE_MEMBER.MEMBER_ID, NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_OWED, NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_PAID

HAVING (((NOLDBA_OBLIGATION.DT_ACCRUAL_NEXT)>Now() And Not (NOLDBA_OBLIGATION.DT_ACCRUAL_NEXT)=#12/31/9999#));

 

[jdraw]

You might try this in the where clause and remove the Having (not tested)

I would test it as a SELECT query rather than Select into

AND NOLDBA_OBLIGATION.DT_ACCRUAL_NEXT
= ( SELECT Min(NOLDBA_OBLIGATION.DT_ACCRUAL_NEXT) as MinNEXT
FROM NOLDBA_OBLIGATION
WHERE  NOLDBA_OBLIGATION.DT_ACCRUAL_NEXT>Date )

Mar 24/2011

This is day later from original response. I was trying to find which forum I responded to to see if there was an update. So I googled using

NOLDBA_OBLIGATION

Here's the result set

http://www.google.com/#hl=en&sugexp...=1&bav=on.2,or.r_gc.r_pw.&fp=a0e1d04ac32ef934

What does this really mean???