SELECT PATIENT.CHARTID, PATIENT.LASTNAME, PATIENT.FIRSTNAME,
PATIENT.MIDDLEINITIAL,
SUM(CASE
WHEN CAST(TSCURR.TRANSACTIONSETDATETIME AS DATE) <= :ASOFDATE
THEN L.AMOUNT ELSE 0 END) CURR,
SUM(CASE
WHEN CAST(TSCURR.TRANSACTIONSETDATETIME AS DATE)+30 <= :ASOFDATE
THEN L.AMOUNT ELSE 0 END) L30,
SUM(CASE
WHEN CAST(TSCURR.TRANSACTIONSETDATETIME AS DATE)+60 <= :ASOFDATE
THEN L.AMOUNT ELSE 0 END) L60,
SUM(CASE
WHEN CAST(TSCURR.TRANSACTIONSETDATETIME AS DATE)+90 <= :ASOFDATE
THEN L.AMOUNT ELSE 0 END) L90,
SUM(CASE
WHEN CAST(TSCURR.TRANSACTIONSETDATETIME AS DATE)+180 <= :ASOFDATE
THEN L.AMOUNT ELSE 0 END) L180
FROM PATIENT P
JOIN TRANSACTIONSET TS ON P.PATIENTID = TS.PATIENTID
JOIN JOURNAL J ON TS.TRANSACTIONSETID = J.TRANSACTIONSETID
JOIN LEDGER L ON J.JOURNALID = L.JOURNALID
WHERE TS.OFFICEID = :OFFICEID
AND L.LEDGERACCOUNTID = :PATIENTARACT
AND CAST(TS.TRANSACTIONSETDATETIME AS DATE) <= :ASOFDATE
GROUP BY 1, 2, 3, 4


That's an extraordinary query! Brilliant!
That should be in a 'Tip of the Month' section somewhere!

N


Nigel Weeks
Tech Support and Systems Developer
Rural Press Tasmania
The Examiner Newspaper
Ph. 03 6336 7234
Mob. 0408 133 738
Email. nweeks@...