It's looking like it's my bad. The dates that were returning zero had
somehow been deleted from the test database (which is what my
development platform's app server was pointing to) but DBW (where I was
testing the queries) was pointing at the production database.

Sorry for the confusion.

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

> The intention is to find out the difference between the count of
> rows with P and the count of rows with X, filtered by various
> criteria such as a given DateSbm and/or AccountId.
> ...
> Any suggestions on a correct SQL statement?

Try

SELECT
(SELECT COUNT(CPR1.CLAIMSTS)
FROM CLAIMSPAIDREVERSED CPR1 WHERE CPR1.DATESBM>=?
AND CPR1.DATESBM<=?
AND CPR1.CLAIMSTS='P'
AND CPR1.ACCOUNTID=?
) - (SELECT COUNT(CPR2.CLAIMSTS) FROM
CLAIMSPAIDREVERSED CPR2 WHERE CPR2.DATESBM>=?
AND CPR2.DATESBM<=?
AND CPR2.CLAIMSTS='X' AND CPR2.ACCOUNTID=?
)
FROM RDB$DATABASE


Roman







Yahoo! Groups Links