Scott Scott - 3 months ago 9
SQL Question

Get corresponding rows in single line result?

Given the following data, how would I get the desired result below?

Timestamp | Session ID | Event | Name
------------------------------------------
08:15 | 89 | Login | Scott
08:16 | 89 | Edit | Scott
08:16 | 92 | Login | John
08:17 | 92 | Refresh | John
08:23 | 89 | Logout | Scott
08:28 | 92 | Logout | John
08:30 | 96 | Login | Scott
08:37 | 96 | Logout | Scott


Desired Result (essentially a list of session durations):

Name | Login | Logout
------------------------
Scott | 8:15 | 8:23
John | 8:16 | 8:28
Scott | 8:30 | 8:37


edit: extended sample data and results to avoid some confusion.




The query I'm actually needing to develop is much more complex . I just thought this would give me a good jumpstart on one of the logic hurdles. Since I know everyone will want to know what I've tried, here is my current, embarrassing, iteration from the actual structure...

SELECT
SessionId,
SAMLData_Organization,
(Select TimeCreated FROM ens.messageheader h1,HS_Message.XMLMessage m1 WHERE h1.SessionId = h3.SessionId and m1.name = 'XDSB_QueryRequest') as RequestRecieved,
(Select TimeCreated FROM ens.messageheader h1,HS_Message.XMLMessage m1 WHERE h1.SessionId = h3.SessionId and m1.name = 'XDSB_QueryResponse') as ResponseSent
FROM
ens.messageheader h3,HS_Message.XMLMessage m3
WHERE SessionId IN (SELECT Distinct SessionId FROM ens.messageheader WHERE TimeCreated >= '2016-08-22 08' AND TimeCreated < '2016-08-22 17')


Things I'm trying to tackle:


  1. Join
    ENS.MessageHeader
    and
    HS_Message.XMLMessage

  2. Get the
    TimeCreated
    value for messages of type
    XDSb_QueryRequest

  3. Get the
    TimeCreated
    value for the corresponding
    XDSb_QueryResponse
    using the
    SessionId
    as a common value.

  4. Return results as
    Organization | RequestReceived | ResponseSent


Answer

Pure LEFT SELF JOIN method

SELECT
    li.Name
    ,li.Timestamp as Login
    ,lo.Timestamp as LogOut
FROM
    TableName li
    LEFT JOIN TableName lo
    ON li.[Session ID] = lo.[Session ID]
    AND lo.Event = 'Logou'
WHERE
    li.Event = 'Login'

LEFT SELF JOIN with aggregation

SELECT
    li.Name
    ,li.Timestamp as Login
    ,MIN(lo.Timestamp) as LogOut
FROM
    TableName li
    LEFT JOIN TableName lo
    ON li.Name = lo.Name
    AND lo.Timestamp > li.Timestamp
    AND lo.Event = 'Logou'
WHERE
    li.Event = 'Login'
GROUP BY
    li.Name
    ,li.Timestamp

The top one is good because it constrains per SessionId so you can see a per session look. The bottom works well if session Id is not unique to the name and login/logout pair you are looking for.

Per your answer it should be able to be written like this as well:

SELECT
    li.SAMLData_Organization,
    li.SessionId,
    m1.TimeCreated as RequestRecieved,
    m2.TimeCreated as ResponseSent
FROM
    ens.messageheader h1
    INNER JOIN HS_Message.XMLMessage m1
    ON h1.MessageBodyId = m1.id
    and m1.name = 'XDSb_RetrieveRequest'
    LEFT JOIN HS_Message.XMLMessage m2
    ON h1.MessageBodyId = m2.id
    and m2.name = 'XDSb_RetrieveResponse'
ORDER BY
    h1.SessionId DESC