Subject Re: SQL query help
Author Ian A. Newby
You could do it in firebird 2 I think, using derived tables:

select coalesce(cr.fyear, ce.fyear) as fYear, coalesce(cr.fmonth,
ce.fmonth), cr.created, ce.ceased from

(select extract(year from createdate) as fYear,extract(month from
createdate) as fMonth, tcount(*) as Created from customer group by 1,2) cr

full outer join
(select extract(year from ceasedate) as fYear,extract(month from
ceasedate) as fMonth, tcount(*) as Ceased from customer group by 1,2)
ce on cr.fyear = ce.fyear and cr.fmonth = ce.fmonth

order by 1,2

However, this will not include months where no customers were added or
removed. To do this, you would need a stored procedure to generate the
year month combinations.


Ian Newby