The problem with

select extract(month from CreateDate), extract(month from CeaseDate),
count(*)
where CreateDate between '01.01.2005' and '31.12.2005'
or CeaseDate between '01.01.2005' and '31.12.2005'
group by 1, 2

isn't mainly an indexing problem (although that may be a showstopper on
a large database with executives that require quick calculations).
Rather, the problem is that this answer the question: How many created
that month ceased this month? You're interested in totals ceased a
particular month, regardless of when they were created.

Basically, answering two separate questions like this in a single query
isn't easy and Adam is right that it is best solved through a stored
procedure (the best alternative being two different queries, which is
simple enough). The only way I can think of solving this in a single
query, is the somewhat complex

select sum(case
when extract(month from CreateDate) = 1 then 1
else 0
end) as CreatedJanuary,
sum(case
when extract(month from CeaseDate) = 1 then 1
else 0
end) as CeasedJanuary,
sum(case
when extract(month from CreateDate) = 2 then 1
else 0
end) as CreatedFebruary,
sum(case
when extract(month from CeaseDate) = 2 then 1
else 0
end) as CeasedFebruary,
//repeat 12 times...
from MyTable
where CreateDate between '01.01.2005' and '31.12.2005'
or CeaseDate between '01.01.2005' and '31.12.2005'
group by 1
//Alternatively use UNION if you want twelve rows rather than one.

There may well exist simpler solutions as to how do this in one single
query, but I'm pretty certain that 'simpler' does not mean simple. A
stored procedure or two separate queries are the simplest solutions.

Set

Adam wrote:

> --- In firebird-support@yahoogroups.com, "bill_zwirs" <bzwirs@...> wrote:
>>
>>> 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
>>>
>> Adam
>>
>> Maybe I didn't make my question very clear, but I want the result
>> grouped by month so that, for example, a 1 year period the result
>> would show for each month, number of customers created, number of
>> customers made inactive.
>>
>> The end result of the query is to be shown in a graph that will then
>> also show the net gain/loss of customers (per month). That's why I
>> would like to be able to do this in a single query.
>
> Well if you are a saddist you may wish to investigate an aggregate
> query grouping by an extract month from date. This would not be able
> to use any available index on the create or cease dates.
>
> Otherwise, it would be much simpler to create a selectable stored
> procedure with a start and end date that iterates through each month
> and does the select count for the appropriate subset of dates.
>
> Adam