CODEWITHSUNDEEP
sqlintersystems-cache
user736893
user736893

Reputation:

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

Upvotes: 2

Views: 90

Answers (3)

Matt
Matt

Reputation: 14361

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

Upvotes: 2

user736893
user736893

Reputation:

Based on @Matt's answer, here is my actual query:

SELECT
        li.SAMLData_Organization,
        li.SessionId,
        li.TimeCreated as RequestRecieved,
        lo.TimeCreated as ResponseSent
FROM
    (Select h1.SessionId, TimeCreated, SAMLData_Organization FROM ens.messageheader h1,HS_Message.XMLMessage m1 WHERE h1.MessageBodyId = m1.id and m1.name = 'XDSb_RetrieveRequest') li
    LEFT JOIN (Select h2.SessionId, TimeCreated FROM ens.messageheader h2,HS_Message.XMLMessage m2 WHERE h2.MessageBodyId = m2.id and m2.name = 'XDSb_RetrieveResponse') lo
    ON li.SessionId = lo.SessionId
GROUP BY li.SessionId
ORDER BY li.SessionId Desc

Upvotes: 0

Sobbles
Sobbles

Reputation: 105

You can do something like this, just break everything down into pieces and then put them together, I assumed you have an id column

select distinct name, id, 

(SELECT min(timestamp) from table
where event = "login" and user_id = event_table.user_id) as login,

(SELECT max (timestamp) from table
where event = "logout" and user_id = event_table.user_id) as logout

from event_table

Hope you understand and this helps

Upvotes: -2

Related Questions