Hey guys,

I'm having trouble getting an AR report with aging - to show balances for
current - 30,60,90 and 180 days.

A PATIENT has a TRANSACTIONSET which is a batch of one or more JOURNAL
entries that include two LEDGER entries (a debit and a credit). To get
current balance I just query the balance of the AR from ledger. 30 days I
think should be the ledger balance 30 days ago minus any credits since that
time.

I've started with this:

SELECT
PATIENT.CHARTID,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.MIDDLEINITIAL,
SUM( LCURR.AMOUNT ) CURR,
SUM( L30.AMOUNT ) /*- SUM( L30C.AMOUNT)*/ THIRTY
--SUM( L4.AMOUNT ) - SUM( L5.AMOUNT) SIXTY,
--SUM( L6.AMOUNT ) - SUM( L6.AMOUNT) NINETY
--SUM( L8.AMOUNT ) - SUM( L9.AMOUNT) ONEEIGHTY
FROM
PATIENT
INNER JOIN TRANSACTIONSET TSCURR ON (PATIENT.PATIENTID =
TSCURR.PATIENTID)
INNER JOIN JOURNAL JCURR ON (TSCURR.TRANSACTIONSETID =
JCURR.TRANSACTIONSETID)
INNER JOIN LEDGER LCURR ON (JCURR.JOURNALID = LCURR.JOURNALID)
LEFT OUTER JOIN TRANSACTIONSET TS30 ON (PATIENT.PATIENTID =
TS30.PATIENTID)
INNER JOIN JOURNAL J30 ON (TS30.TRANSACTIONSETID = J30.TRANSACTIONSETID)
INNER JOIN LEDGER L30 ON (J30.JOURNALID = L30.JOURNALID)
WHERE
TSCURR.OFFICEID = :OFFICEID
AND LCURR.LEDGERACCOUNTID = :PATIENTARACT
AND (CAST(TSCURR.TRANSACTIONSETDATETIME AS DATE) <= :ASOFDATE)
--Get 30 days AR
AND TS30.OFFICEID = :OFFICEID
AND L30.LEDGERACCOUNTID = :PATIENTARACT
AND CAST(TS30.TRANSACTIONSETDATETIME AS DATE) <= ADDDAY(:ASOFDATE,-30)
GROUP BY PATIENT.CHARTID, PATIENT.LASTNAME, PATIENT.FIRSTNAME,
PATIENT.MIDDLEINITIAL
HAVING SUM(LCURR.AMOUNT) <> 0.00
ORDER BY PATIENT.LASTNAME, PATIENT.FIRSTNAME, PATIENT.MIDDLEINITIAL,
PATIENT.CHARTID

For now I just stuck in the balance 30 days ago. The problem is if there is
no balance then nothing is returned. I don't understand - I've tried left
joins which I think should do the trick.

So I have no data from 30 days ago and this returns nothing. If I remove
the 30 days ago criteria I get the current balances correctly.

I also tried with subselects:

SELECT
PATIENT.CHARTID,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.MIDDLEINITIAL,
SUM( LEDGER.AMOUNT ) CURR,
(SELECT
(SUM( L30.AMOUNT ) /*- SUM( L30C.AMOUNT)*/)
FROM
LEDGER L30
INNER JOIN JOURNAL J30 ON (L30.JOURNALID = J30.JOURNALID)
INNER JOIN TRANSACTIONSET TS30 ON (J30.TRANSACTIONSETID =
TS30.TRANSACTIONSETID)
WHERE
TS30.PATIENTID = PATIENT.PATIENTID
AND TS30.OFFICEID = :OFFICEID
AND L30.LEDGERACCOUNTID = :PATIENTARACT
AND CAST(TS30.TRANSACTIONSETDATETIME AS DATE) < ADDDAY(:ASOFDATE,-30))
THIRTY
FROM
LEDGER
INNER JOIN JOURNAL ON (LEDGER.JOURNALID = JOURNAL.JOURNALID)
INNER JOIN TRANSACTIONSET ON (JOURNAL.TRANSACTIONSETID =
TRANSACTIONSET.TRANSACTIONSETID)
INNER JOIN PATIENT ON (TRANSACTIONSET.PATIENTID = PATIENT.PATIENTID)
WHERE
TRANSACTIONSET.OFFICEID = :OFFICEID
AND LEDGER.LEDGERACCOUNTID = :PATIENTARACT
AND (CAST(TRANSACTIONSET.TRANSACTIONSETDATETIME AS DATE) < :ASOFDATE)
GROUP BY PATIENT.CHARTID, PATIENT.LASTNAME, PATIENT.FIRSTNAME,
PATIENT.MIDDLEINITIAL
HAVING SUM(LEDGER.AMOUNT) <> 0.00
ORDER BY PATIENT.LASTNAME, PATIENT.FIRSTNAME, PATIENT.MIDDLEINITIAL,
PATIENT.CHARTID

With this query the subselect gives me an error:

"Invalid expression in the select list (not contained in either an aggregate
function or the GROUP BY clause)."

Any advice would be greatly appreciated! Thanks.