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

if date only is significant, then a datetime field can cause you problems
with comparisons if you are not aware that you are dealing with the time
part of the value as well.
all non-aggregate fields need to be included in the GROUP BY clause of an
aggregate select. The order of the non-aggregate fields will be the
"natural" order of the dataset. If you select the non-aggregate fields in
the order in which you wish the dataset, then an ORDER by clause is not
required since the GROUP by clause is doing the job.
Alan

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