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. 
