How to List information by date

Hi,

I want to track the lifecycle of a request(requestno), the time it was submitted to accepted/denied.

The information is captured as :

Audit

auditid
adminid
auditdate
requestno
taskid

auditid adminid auditdate requestno taskid
1 1 12/06/2007 1 1
2 3 13/06/2007 2 1
3 12 12/06/2007 3 1
4 2 13/06/2007 1 2
5 2 16/06/2007 1 3
6 4 14/06/2007 2 2
7 4 7/06/2007 2 4

Task
taskid
taskname

taskid taskname
1 Submitted
2 InProgress
3 Accepted
4 Denied
5 Transferred

I want a query that will list down the following. There should be only one row for each request :

RequestNo DtSubmit DtProgress DtAccepted DtDenied Admin
1 12/06/2007 13/06/2007 16/06/2007 null 2
2 13/06/2007 14/06/2007 null 17/06/2007 4
3 12/06/2007 null null null 12

Thanks,

Vidya.

here it is,

Code Snippet

Create Table #audit (

[auditid] int ,

[adminid] int ,

[auditdate] datetime ,

[requestno] int ,

[taskid] int

);

SET DATEFORMAT DMY

Insert Into #audit Values('1','1','12/06/2007','1','1');

Insert Into #audit Values('2','3','13/06/2007','2','1');

Insert Into #audit Values('3','12','12/06/2007','3','1');

Insert Into #audit Values('4','2','13/06/2007','1','2');

Insert Into #audit Values('5','2','16/06/2007','1','3');

Insert Into #audit Values('6','4','14/06/2007','2','2');

Insert Into #audit Values('7','4','7/06/2007','2','4');

Create Table #task (

[taskid] int ,

[taskname] Varchar(100)

);

Insert Into #task Values('1','Submitted');

Insert Into #task Values('2','InProgress');

Insert Into #task Values('3','Accepted');

Insert Into #task Values('4','Denied');

Insert Into #task Values('5','Transferred');

Select

RequestNo

,Max(Case When T.[taskid]=1 Then [auditdate] End) DtSubmit

,Max(Case When T.[taskid]=2 Then [auditdate] End) DtInProgress

,Max(Case When T.[taskid]=3 Then [auditdate] End) DtAccepted

,Max(Case When T.[taskid]=4 Then [auditdate] End) DtDenied

,Max(Case When T.[taskid]=5 Then [auditdate] End) DtTransferred

,Max(Adminid)

From

#audit A

Join #task T On A.[taskid] = T.[taskid]

Group By

RequestNo

|||

Bingo!!

Thanks.