Oops Anthony, I think there may be several issues here. Lets start with
the simplest:

You are right that all rows are returned for the left table in a left
outer join. But that only includes restrictions in the left outer join
itself, everything in the WHERE clause has to be satisfied regardless.
Hence,

LEFT OUTER JOIN TRANSACTIONSET TS30 ON (PATIENT.PATIENTID = TS30.PATIENTID)

return all rows, but
WHERE... AND CAST(TS30.TRANSACTIONSETDATETIME AS DATE)...

reduces the result set to only those that actually meet a requirement,
and none of the non-existing rows meet this requirement.

To preserve the LEFT OUTER, put all restrictions on the right table in
the LEFT OUTER JOIN, i.e.

LEFT OUTER JOIN TRANSACTIONSET TS30 ON PATIENT.PATIENTID =
TS30.PATIENTID AND CAST(TS30.TRANSACTIONSETDATETIME AS DATE)...
WHERE...

Though there is more, suppose there are two rows within 30 days, this
means two rows not only for the L30, but also for LCURR - hence
SUM( LCURR.AMOUNT ) CURR is likely to return too high a value and it
gets further complicated if you add L60. Hence, I'd suggest a completely
different approach:

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

I didn't see any reference to the credit bit in your original SQL, but I
would guess it is a field in your ledger that tells whether the amount
should be credited or debited. If so, just handle it all inside the WHEN
clause.

HTH,
Set

Anthony Tanas wrote:

> 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.