Help with SELECT Statement

  • 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.

  • One possibility and possibly the simplest is:

    update

    r

    set

    Status = isnull(

       (select top 1 t.Status
        from #RequestAuditTrails t
        where t.RequestID = r.RequestID
        order by t.RequestID, t.AuditDateTime desc)
        ,r.Status)
    from Request r

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • 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.

  • I'm missing the key between #Requests and #RequestAuditTrails. stax68 says t.RequestID = r.RequestID but there is no RequestID  on #Requests table. Please explain the logic.

  • 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

  • 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