Hi Albert,

I am not sure about your where clause, but I would re-write these two:

( SELECT COUNT(*) FROM Apps WHERE Apps.File_ID == PartFile.File_ID ) AS NumApps, ;
( SELECT MAX(Apps.LastPaymnt) FROM Apps WHERE Apps.File_ID == PartFile.File_ID ) AS LastPay_Dt ;

as

INNER JOIN (select count(*) as NumApps, Max(Apps.LastPaymnt) as LastPay_Dt, FIle_ID
from Apps GROUP BY File_ID) X ON PartFile.File_ID = X.File_ID

and would just use X.NumApps and X.LastPay_Dt in the main query

>Hi all,
>
>Getting the hang of them and they have turned into a goldmine on my next query as well - Tamar's white paper also helped in that for this second one, I used it to get the last payment date as a calculated column - would have done this as multiple queries before (i.e. one query to get the "files" and then another to get the max(payment date) for each file. So much nicer and a whole lot less code to do and check.
>
>This ended up being the query - I know, I know I should have used aliases on the tables to save some typing, will take time to get used to that :-) I am a fast, fast typist so it has not been an issue so far (but it is when I make a typo :-) )
>
>See the nice correlated subquery and also the calculated columns. If anyone sees a syntax problem, please point it out - I am running checks to see if the data results are correct right now.
>
>Thanks all for the help - particularly Tamar for writing the paper
>
>

>STORE GOMONTH(DATE(),-84) TO ldCutOffDate
>STORE "TestCursor" TO lcCursorName
>
>STORE SECONDS() TO lnStartSecs
>
>SELECT ;
>   PartFile.File_ID, Plaintif.Plaintf_ID, Plaintif.Lastname, Plaintif.Firstname, PartFile.Date_Opend, PartFile.Date_Closd, ;
>      PartFile.Partner, PartFile.Our_FileNo, PartFile.Folder_ID, PartFile.TrackNotes, ;
>      ( SELECT COUNT(*) FROM Apps WHERE Apps.File_ID == PartFile.File_ID ) AS NumApps, ;
>      ( SELECT MAX(Apps.LastPaymnt) FROM Apps WHERE Apps.File_ID == PartFile.File_ID ) AS LastPay_Dt ;
>   FROM Plaintif JOIN PartFile ;
>      ON Plaintif.Plaintf_ID == PartFile.Plaintf_ID ;
>   WHERE PartFile.FileLocked = .T. AND ;
>      PartFile.NeverPurge = .F. AND ;
>      NOT EMPTY(PartFile.Folder_ID) AND ;
>      NOT EMPTY(PartFile.Stru_Amt) AND NOT EMPTY(PartFile.Date_Fund) AND ;
>      NOT EMPTY(PartFile.Date_Closd) AND PartFile.Date_Closd < ldCutOffDate AND ;
>      EMPTY(PartFile.Archv_Date) AND PartFile.Archv_Type # "P" AND ;
>      NOT ( Plaintif.EstateFile = .T. AND EMPTY(Plaintif.EstateClsd) ) AND ;
>         NOT EXISTS ;
>         ( SELECT App_ID FROM Apps WHERE Apps.File_ID == PartFile.File_ID AND ;
>            ( EMPTY(Apps.LastPaymnt) OR Apps.LastPaymnt >= ldCutOffDate ) ) ;
>   ORDER BY Plaintif.Lastname, Plaintif.Firstname ;
>   INTO CURSOR ( lcCursorName ) READWRITE
>
>

>
>Albert