Multiple COUNTS

[luckyman]

Hi, heres my problem

I have a Problem table with the following fields

problem_number, employee_id, status, creation, date_closed

and an employee table with the following fields

employee_id, department

what i need to do is bring back a table with distinct departments and three problem count coloumns with the following three criterias

datediff(hour, creation, getdate()) < 8
datediff(hour, creation, getdate()) between 8 and 48
datediff(hour, creation, getdate()) > 48

so the table will look something like

department green yellow red
------------ ------ -------- -----
ACCOUNTS 5 2 8
CORP 6 4 2

anyone know how do this in one statement?

cheers

 

[carp]

What RDBMS are you using?

 

[luckyman]

SQL Server 2000