--- In firebird-support@yahoogroups.com, "bill_zwirs" <bzwirs@...> wrote:

>
> I have a customer table (Firebird1.5.3) that contains a create date
> and a cease date (cease date records date customer becomes inactive
> otherwise remains null).
>
> What I want to do is get a result set that will show for each month
> the number of customers created and number of customers made inactive.
>
> Can somebody please show me how to achieve this with a SQL query.

Isn't it just a simple count query on both occasions?

select count(*)
from table
where createdate >= :FirstDayOfMonth
and createdate < :FirstDayOfNextMonth

-- an index on createdate would assist this query

select count(*)
from table
where ceasedate >= :FirstDayOfMonth
and ceasedate < :FirstDayOfNextMonth

-- an index on ceasedate would assist this query

Or if you were desperate to get them in a single query,

select
(
select count(*)
from table
where createdate >= :FirstDayOfMonth
and createdate < :FirstDayOfNextMonth
) as CreateCount
,
(select count(*)
from table
where ceasedate >= :FirstDayOfMonth
and ceasedate < :FirstDayOfNextMonth
) as CeaseCount
from RDB$DATABASE

Adam