July 13, 2006 at 5:51 pm
Sample data:
create table #Requests
(ID INT identity (1,1),
Received datetime default getdate(),
Status varchar (10))
create table #RequestAuditTrails
(ID INT identity (1,1),
RequestID int,
Status varchar (10),
Notes varchar(100),
AuditDateTime datetime default getdate())
insert #Requests (Received , Status) values ('20050710', 'Closed')
insert #Requests (Received , Status) values ('20050711', 'Closed')
insert #Requests (Received , Status) values ('20050712', 'Closed')
insert #Requests (Received , Status) values ('20050712', 'Open')
insert #RequestAuditTrails (RequestID, Status, Notes, AuditDateTime) values (1, 'Open', 'abc', '20050711')
insert #RequestAuditTrails (RequestID, Status, Notes, AuditDateTime) values (1, null , 'def', '20050712')
insert #RequestAuditTrails (RequestID, Status, Notes, AuditDateTime) values (1, 'Closed', null, '20050712')
insert #RequestAuditTrails (RequestID, Status, Notes, AuditDateTime) values (2,'Open', null,'20050712')
insert #RequestAuditTrails (RequestID, Status, Notes, AuditDateTime) values (2,'Closed', 'incomplete','20050712')
insert #RequestAuditTrails (RequestID, Status, Notes, AuditDateTime) values (2,'Open', 'abc','20050712')
insert #RequestAuditTrails (RequestID, Status, Notes, AuditDateTime) values (2,'Closed', 'def','20050722')
insert #RequestAuditTrails (RequestID, Status, Notes, AuditDateTime) values (3, null, 'abc','20050715')
insert #RequestAuditTrails (RequestID, Status, Notes, AuditDateTime) values (3, 'Open', null, '20050718')
insert #RequestAuditTrails (RequestID, Status, Notes, AuditDateTime) values (3, null, 'def', '20050720')
insert #RequestAuditTrails (RequestID, Status, Notes, AuditDateTime) values (3, 'Closed', 'ghi', '20050721')
insert #RequestAuditTrails (RequestID, Status, Notes, AuditDateTime) values (3, null, 'xxx', '20051120')
insert #RequestAuditTrails (RequestID, Status, Notes, AuditDateTime) values (4, 'Open','abc','20050725')
I need to have a select statement that list all columns in #Requests table and if a request is closed, I need to put the ClosedDate next to it. I am able to make a temp table as select *, null as ClosedDate from #Requests and loop through the #RequestAuditTrails table to get the MAX(AuditDateTime) column when the Status is closed and insert it into ClosedDate column. However I perfer to have it done in only a SELECT statement. Please advice. Thanks.
Please note that the Status can be Closed and Open again and Closed. I just need the date when it's last closed.
July 13, 2006 at 6:39 pm
One possibility and possibly the simplest is:
r
Status = isnull(
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 13, 2006 at 8:24 pm
Sorry if I wasn't clear enough but here is what I would like to see.
ID Received Status ClosedDate
----------- ------------------------------------------------------ ---------- -----------
1 2005-07-10 00:00:00.000 Closed 2005-07-12 00:00:00.000
2 2005-07-11 00:00:00.000 Closed 2005-07-22 00:00:00.000
3 2005-07-12 00:00:00.000 Closed 2005-07-21 00:00:00.000
4 2005-07-12 00:00:00.000 Open NULL
There are 2 Closed rows for RequestID = 2 in #RequestAuditTrails but only the last Closed which had ClosedDate = 07/22 is selected as MAX(AuditDateTime). The first table, #Requests, will stay intact. I expected to see the result above from a SELECT statement to join #Requests and #RequestAuditTrails, no temp table involved. Thanks.
July 14, 2006 at 5:29 am
Hello,
this should be what you are looking for:
SELECT r.ID, r.received, r.status, MAX(CASE WHEN r.status = 'Closed' THEN t.AuditDateTime ELSE NULL END) as dateclosed
FROM #Requests r
LEFT JOIN #RequestAuditTrails t ON t.requestID=r.ID and t.status='Closed'
GROUP BY r.ID, r.received, r.status
OREDR BY r.ID
July 14, 2006 at 9:28 am
Vladan, it works for me. Thanks.
Claudia, I think stax68 meant r.ID, not r.RequestID.
Thanks everyone.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply