So I'm still bit of a beginner with developing complex queries but I was able to create a query that would give me the first payment date of all accounts along with the amount and record number of that payment.
Code:
SELECT A.FILENO, A.RECEIVED, B.MinOfTRANS_DATE, B.MinOfRECNO
FROM dbo_ACTIVE AS A INNER JOIN
(SELECT FILENO, MIN(TRANS_DATE) AS MinOfTRANS_DATE, MIN(RECNO) AS MinOfRECNO FROM dbo_ACTIVE WHERE CODE="20" GROUP BY FILENO) AS B
ON (B.FILENO=A.FILENO) AND (B.MinOfTRANS_DATE=A.TRANS_DATE) AND (B.MinOfRECNO=A.RECNO)
ORDER BY A.FILENO;
I need to take this one step further by finding the first payment after another specific date in another table which would be linked by the file number.
Table: MASTER
Linked Field: FILENO
Specific date field: JMT_DATE
I'm guessing that i would need to add another join to the MASTER table but how do I find the first payment after the judgment date after confirming that there is a judgment date. Some guidance would be greatly appreciated.
dBforums
>
First Date after another date
02-09-12, 12:41
Linear Mode
