Re: [Emerald] sql help calculating invoices

Dale E. Reed Jr. ( (no email) )
Thu, 10 Aug 2000 14:02:54 -0700

Bernard Andrys wrote:
>
> I'm trying to create a query for accounting that lists the invoices billed
> by account type for a particular month.
>
> Here is the query I came up with:
>
> SELECT Sum(Invoices.Amount) AS SumOfAmount, SubAccounts.AccountType
> FROM Invoices, SubAccounts
> WHERE (((Invoices.Date)>'6/30/00' And (Invoices.Date)<'8/1/00'))
> GROUP BY SubAccounts.AccountType;
>
> However, it doesn't work at all. It seems to be ignoring the "WHERE" clause
> and adding together every invoice ever produced. Any suggestions would be
> appreciated.

Its not ignoring the where clause. However, you didn't specify a
relationship (join) between the two tables, so SQL Server gives
you the cartesian product of the two tables (ie, a record for every
entry
in invoices with every entry in subaccounts).

What you are trying to do isn't as simple as above. Take a look at
the service revenue report, as it should do what you want:

ftp://ftp.iea-software.com/Emerald/reports/cr60/serv_rev.rpt

-- 

Dale E. Reed Jr.    Emerald and RadiusNT/X__________________________________________IEA Software, Inc.   www.iea-software.com

For more information about this list (including removal) go to:http://www.iea-software.com/support/maillists/liststart