Subject | Re: SQL query help |
---|---|
Author | Ian A. Newby |
Post date | 2006-09-18T10:49:35Z |
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.
Regards
Ian Newby
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.
Regards
Ian Newby