I had written a stored procedure before I realized the SNAFU over here.
The SP does two SELECT COUNT()s and subtracts the results. Would a
single query of the suggested style be more efficient? I don't know how
the engine would end up processing the two suggested queries.
The network traffic is probably the majority of the time, as the query
is executed seven times for each account to display the week's activity
for everyone, so you end up with a hundred queries.
(When I have more time I plan on returning a large result set of all
accounts, with a row for each day.)

-----Original Message-----
From: Arno Brinkman [mailto:firebird@...]
Sent: Friday, October 08, 2004 10:30 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Query returns invalid result


I meant SUM instead of COUNT.

> SELECT
> COUNT(CASE WHEN CPR1.CLAIMSTS = 'P' THEN 1 ELSE 0 END) -
> COUNT(CASE WHEN CPR1.CLAIMSTS = 'X' THEN 1 ELSE 0 END)
> FROM
> CLAIMSPAIDREVERSED CPR1
> WHERE
> CPR1.DATESBM >= ?
> AND CPR1.DATESBM <= ?
> AND CPR1.ACCOUNTID = ?

Regards,
Arno Brinkman
ABVisie






Yahoo! Groups Links