Helen and Alan,

Thank you so much for the responses. I was able to fix my issue by going
about the query a bit different and discovered some problems with my
database design.

Alan - I am using a Date datatype...please let me know what you were
thinking about for my own edification.

Helen - yes, sorry, I actually removed the BILLINGID field from the select
when I submitted my problem here (and forgot to remove it from the group by)
as I was thinking it was extraneous to my question and I thought it would
make it clearer. Obviously that was a stupid thing and I won't do it again.
:p (AMOUNT is in the select however, in a calculation for a field I named
"EXTENDED" in the select.)

In regards to order of GROUP BY, please help me to understand how this
should work generally. I've noticed that the database wants me to include
any non-aggregated fields in the select list but I don't understand why? It
seems to me that I should only have to GROUP BY BILLINGITEMID for what I was
trying to do.

In regards to DISTINCT, yes I see I don't need that, I'm not sure why I had
it. However it shouldn't negatively affect my results though right?

OK, (I used a subselect instead of the previous grouping approach) my
solution uncovered an embarrassing issue with my database design that I
would be grateful for some tips on. Here is my working solution (untouched!
:p ) with question to follow:

SELECT DISTINCT
BI.BILLINGITEMID,
P.CHARTID,
P.LASTNAME || ', ' || P.FIRSTNAME || ' ' || P.MIDDLEINITIAL
PATIENTDISPLAYNAME,
BI.BILLINGITEMDATE,
BI.DIAGNOSISCODE,
BI.PROCEDURECODE,
BI.BILLINGID,
BI.AMOUNT * BI.UNITS EXTENDED,
COALESCE((SELECT SUM(AMOUNT) FROM PAYMENT WHERE BILLINGITEMID =
BI.BILLINGITEMID AND PAYMENTDATE <= :BILLINGITEMDATE),0.00) SUMOFPAYMENTS,
(BI.AMOUNT * BI.UNITS) - COALESCE((SELECT SUM(AMOUNT) FROM PAYMENT WHERE
BILLINGITEMID = BI.BILLINGITEMID AND PAYMENTDATE <= :BILLINGITEMDATE),0.00)
BALANCE
FROM
BILLINGITEM BI
INNER JOIN BILLING B ON (BI.BILLINGID = B.BILLINGID)
INNER JOIN PATIENT P ON (B.PATIENTID = P.PATIENTID)
WHERE
BI.BILLINGITEMDATE <= :BILLINGITEMDATE AND
(
(BI.AMOUNT * BI.UNITS) - COALESCE((SELECT SUM(AMOUNT) FROM PAYMENT WHERE
BILLINGITEMID = BI.BILLINGITEMID AND PAYMENTDATE <= :BILLINGITEMDATE),0.00)

> 0.001 OR

(BI.AMOUNT * BI.UNITS) - COALESCE((SELECT SUM(AMOUNT) FROM PAYMENT WHERE
BILLINGITEMID = BI.BILLINGITEMID AND PAYMENTDATE <= :BILLINGITEMDATE),0.00)
< -0.001
)
ORDER BY
P.LASTNAME, P.FIRSTNAME, P.MIDDLEINITIAL, BI.BILLINGITEMDATE,
BI.BILLINGITEMID

You'll notice some weirdness in my where clause with "> 0.001" OR "< -0.001"
... Where really what I want to say is "<> 0"

Some time ago I was required to convert my application from a Paradox
database to client/server LITERALLY overnight. In my haste I think I made a
big mistake with my datatypes. I used a FLOAT for my currency, so there are
numbers after the hundreds place that is causing me to have to do the above
to get my results.

An additional problem I just noticed is that out of nearly $100,000
calculated my final result is off by some change, maybe 60 or 70 cents.

So my question is - what is the correct datatype for currency? What is the
best way to safely convert my tables and data?

Thanks to all for your time and expertise! :)

-Anthony

> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
> Sent: Sunday, August 27, 2006 5:47 PM
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] Query Problem
>
> At 06:58 AM 28/08/2006, you wrote:
> >I have one table BILLINGITEM, with individual charge lines.
> >
> >For each entry in BILLINGITEM, the PAYMENT table may have 0,
> 1 or more
> >entries.
> >
> >I want to query the database and for every line in
> BILLINGITEM, I want
> >to get the charge and then the sum of any payments in the
> payment table.
> >Further I want to get this information, as of a certain date.
> >
> >It seems like a simple outer join issue, however this query
> is kicking
> >my rear... :p
> >
> >Here is the current version of my query:
> >
> >SELECT DISTINCT
> > BI.BILLINGITEMID,
> > BI.AMOUNT * BI.UNITS CHARGES,
> > SUM(PM.AMOUNT) CREDITS
> >FROM
> > BILLINGITEM BI
> > LEFT OUTER JOIN PAYMENT PM ON (BI.BILLINGITEMID =
> PM.BILLINGITEMID)
> >WHERE
> > (
> > BI.BILLINGITEMDATE <= :ASOFDATE AND
> > (PM.PAYMENTDATE <= :ASOFDATE OR
> > PM.PAYMENTDATE IS NULL)
> > )
> >GROUP BY
> > BI.BILLINGITEMID,
> > BI.billingid,
> > BI.AMOUNT,
> > BI.UNITS
> >ORDER BY
> > BI.BILLINGITEMID
> >
> >The problem is that if there are payments *after* the "ASOFDATE" but
> >none
> >*before* then that record is not returned at all. :(
> >
> >If there are no payments at all then the record is returned properly
> >and if there are any payments before the as of date then
> those are also
> >returned properly.
> >
> >Again, if there are no payments before the as of date, but
> some after,
> >then that record is not returned.
> >
> >*sobs* :(
>
> Questions first.
>
> 1. What's the *actual* query that manages to work sometimes?
> -- In this one you have 2 grouping arguments (bi.billingid and
> bi.Amount) that are not in the SELECT list. Unless you are
> using InterBase or Firebird 1.0, that should return
> exceptions, not results.
>
> 2 (related) If bi.billingid is meant to be there, is it a
> foreign key to some parent structure? If yes, it's in the
> wrong place in the GROUP BY clause.
>
> 3. Why DISTINCT? What do expect from including it?
>
> To solve this, it's probably going to avoid the "20 Questions"
> rigmarole if you provide the metadata for the (2? 3?) tables
> involved and a description of the output you're seeking and
> mention what version of Firebird? InterBase? you're using.
>
> ./heLen
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>