[thelist] SQL Statement problem

[Seth Bienek]

Howdy.

I am querying a table for statistics on how many "applications" are approved or
declined (by week number).  The table is structured so that there is either a
value in the approved date column or the declined date column.  I have a handle
on this up to this point: I need to use GROUP BY to aggregate the data, and
since I use coalesce

SELECT
	COALESCE (DATEPART(ww, approved_date), DATEPART(ww, terminate_date)) AS
status_date,
	COUNT(approved_date) AS apdate,
      COUNT(terminate_date) AS tdate
FROM
	applications
WHERE
	company_key = 'LI'
	AND
	(approved_date BETWEEN '01/01/2001' AND '01/01/2002')
	OR
	(terminate_date BETWEEN '01/01/2001' AND '01/01/2002') AND (terminate_type =
'declined')
GROUP BY
	status_date

The problem is that I can't group by an aliased column, right?  So I get errors.

Is my approach all wrong, or is there just a technique I'm missing?

Thanks in advance ;)

Seth