--- 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