How to pivot this table in SQL

问题:

I came across this issue when trying to make use of this "active users" table from Microsoft NAV. What I got asked to do is a Dashboard in Power BI or other tool that will allow the company I work for to understand how many users are active on the NAV system at any point in time. I found a DAX formula in Power BI that could do the trick - but it requires the following table transformation:

I am trying to do this in SQL Management Studio:

My SQL Server is Microsoft SQL Azure (RTM) - 12.

How could I pivot this NAV table below with the following structure knowing that Session ID might repeat further down the line?

Session ID  |    Event Datetime       | Event Type
350              2017-07-01 01:00       Logon
350              2017-08-01 02:00       Logoff
351              2017-07-01 02:00       Logon
351              2017-08-01 03:00       Logoff
350              2017-09-01 01:00       Logon
350              2017-09-01 02:00       Logoff

The final result would be each Session ID against their Logon and Logoff time (allowing duplicates if Session ID repeats like in this example)

Thanks a lot in advance.

Best

Andre

回答1:

This is TSQL code and will run on an Azure database. I think this will get you the results you are looking for.

DECLARE @ActiveUsersLog AS TABLE
      (
          sessionId     INT
         ,EventDateTime DATETIME
         ,EventType     VARCHAR(50)
      );

INSERT INTO @ActiveUsersLog
     (
         sessionId
        ,EventDateTime
        ,EventType
     )
VALUES
     (350, '2017-07-01 01:00', 'Logon')
    ,(350, '2017-08-01 02:00', 'Logoff')
    ,(351, '2017-07-01 02:00', 'Logon')
    ,(351, '2017-08-01 03:00', 'Logoff')
    ,(350, '2017-09-01 01:00', 'Logon')
    ,(350, '2017-09-01 02:00', 'Logoff');

WITH cte_logon
    AS (
           SELECT aul.sessionId
                 ,aul.EventDateTime
                 ,seq = RANK() OVER (PARTITION BY aul.sessionId ORDER BY aul.EventDateTime)
             FROM @ActiveUsersLog AS aul
            WHERE aul.EventType = 'Logon'
       )
    ,cte_logoff
    AS (
           SELECT aul.sessionId
                 ,aul.EventDateTime
                 ,seq = RANK() OVER (PARTITION BY aul.sessionId ORDER BY aul.EventDateTime)
             FROM @ActiveUsersLog AS aul
            WHERE aul.EventType = 'Logoff'
       )
SELECT o.sessionId
      ,LogonTime = o.EventDateTime
      ,LogoffTime = f.EventDateTime
  FROM cte_logon AS o
      LEFT OUTER JOIN cte_logoff AS f
          ON o.sessionId = f.sessionId
             AND o.seq = f.seq;

If you are only looking for active users then you would add this where clause: (make sure to remove the semicolon at the end of the code above before adding a where clause)

where f.EventDateTime is null

回答2:

Without knowing the flavour of SQL, I can only give you basic advice.

By performing an inner join with both sides lining to the same table (aliasing appropriately) matching on the session_id of both sides, you can then filter Event Type on one of the aliased tables to equal Logon and Event Type on the other aliased table to match Logoff.

This method should work on just about any form of SQL I've used, as it's core functionality of SQL.