this was silly from me!
this should naturally look like this:
select case when status ='Closed' then stop_date else start_date end
as adate, sum(case when status ='Closed' then 1 else 0 end) as
closedCount, sum(case when status ='New' then 1 else 0 end) as openedCount from Yourtable where status in
('Closed','New')group by case when status ='Closed' then stop_date else start_date end
order by adate
Marc
> Hi,
> What about something like that ?
> select adate, sum(openedCount) as openedCount, sum(closedCount) as
closedCount
> from
> (
> select sum(case when status ='Closed' then stop_date else start_date
end) as adate,
> sum(case when status ='Closed' then 1 else 0 end) as
closedCount
> sum(case when status ='New' then 1 else 0 end) as openedCount
> from Yourtable
> where status in ('Closed','New')
> )x
> group by adate
> order by adate
