Manipulate Login Logout Data With T-sql In Sql Server

Does anyone know the way of building table 2 with information in table 1? It is easy to approach with Python because I can use ‘checking by row’. However, there is a big datase

Solution 1:

Good day,

DDL+DML: Something the user that ask the question should post!!!

-- I assume that your date and time data is in format "mm/dd/yy", which means style 1-- For better aqurecy I am using datetime2(7) droptable if exists T;
createtable T(UserID int, EventTime datetime2(7), [Event] bit)
GO
INSERT T(UserID,EventTime,Event)
values
(1,CONVERT(datetime2(7),'9/1/13 15:33', 1), 0),
(1,CONVERT(datetime2(7),'9/1/13 17:00', 1), 0),
(1,CONVERT(datetime2(7),'9/1/13 18:00', 1), 0),
(1,CONVERT(datetime2(7),'9/1/13 18:20', 1), 1),
(1,CONVERT(datetime2(7),'9/1/13 18:30', 1), 1),
(1,CONVERT(datetime2(7),'9/2/13 11:05', 1), 0),
(1,CONVERT(datetime2(7),'9/2/13 11:45', 1), 1),
(1,CONVERT(datetime2(7),'9/2/13 13:50', 1), 0),
(2,CONVERT(datetime2(7),'9/1/13 16:15', 1), 0),
(2,CONVERT(datetime2(7),'9/1/13 17:00', 1), 1),
(2,CONVERT(datetime2(7),'9/1/13 18:01', 1), 0),
(2,CONVERT(datetime2(7),'9/1/13 18:02', 1), 0),
(2,CONVERT(datetime2(7),'9/1/13 19:02', 1), 1),
(3,CONVERT(datetime2(7),'9/1/13 17:10', 1), 0),
(3,CONVERT(datetime2(7),'9/1/13 19:10', 1), 1),
(3,CONVERT(datetime2(7),'9/2/13 21:01', 1), 0)
GO
SELECT*FROM T
orderby UserID, EventTime, Event
GO

First draft of solution

Please check if this solve your needs

;with MyCTE as (
    SELECT UserID, EventTime, [Event]
        , [RN1-RN2] = ROW_NUMBER() over (orderby UserID, EventTime, [Event]) - ROW_NUMBER() over (partition by UserID, [Event] orderby UserID, EventTime, [Event])
    FROM T
),
MyCTE2 as (
    selectdistinct UserID, [Event] 
        , MIN(EventTime) OVER (partition by UserID,[Event], [RN1-RN2]) M
    from MyCTE
)
select UserID
  , [0] as LoginTime
  , [1] as LogoutTime
From (
    select UserID, [Event], M
        , ROW_NUMBER() OVER(partition by UserID,[Event] orderby M) as GroupNum
    from MyCTE2
)x
pivot
(
  MIN(M)
  for [Event] in([0], [1])
)p
orderby UserID, [LoginTime]
GO

If this solution fits you then we know that I got your needs, and we can move to the next step which is discuss about performance. For this we will need to get your real tab;e structure ans some more sample data (DDL+DML from you)