select columns into rows grouped by columns values

Question

I have a table of logins that looks like this:

logins table

Emp_ID    Created                |  Action
1       20/01/2016 10:44:42 AM      login
1       20/01/2016 4:45:49 PM       logout
1       20/01/2016 6:30:13 PM       logout
1       21/01/2016 8:46:28 AM       login
1       21/01/2016 9:46:42 AM       login
1       21/01/2016 1:46:46 PM       logout
1       22/01/2016 8:49:21 AM       login
1       22/01/2016 1:49:27 PM       logout
1       22/01/2016 2:29:53 PM       login
1       22/01/2016 2:30:13 PM       logout
3       22/01/2016 2:42:06 PM       login
1       22/01/2016 9:57:22 PM       login
1       22/01/2016 10:22:23 PM      logout
1       23/01/2016 8:01:47 AM       login
1       23/01/2016 9:01:58 AM       logout
3       23/01/2016 8:02:06 AM       login
3       23/01/2016 9:02:28 AM       logout

The employees table

| ID | Fname | Lname |
|----|-------|-------|
|  1 | James | Brown |
|  2 |  Mark |  Bond |
|  3 |  Kemi |   Ojo |

The result I got

|    created |    login |   logout |    Employee | Emp_ID |
|------------|----------|----------|-------------|--------|
| 2016-01-20 | 10:44:42 | 18:30:13 | James Brown |      1 |
| 2016-01-21 | 08:46:28 | 13:46:46 | James Brown |      1 |
| 2016-01-22 | 08:49:21 | 22:22:23 | James Brown |      1 |
| 2016-01-22 | 14:42:06 | 22:22:23 |    Kemi Ojo |      3 |
| 2016-01-23 | 08:01:47 | 09:02:28 | James Brown |      1 |
| 2016-01-23 | 08:02:06 | 09:02:28 |    Kemi Ojo |      3 |

Here is what I have tried:

SELECT
    CAST(LI.created AS DATE) AS created,
    MIN(CAST(LI.created AS TIME)) AS login,
    MAX(CAST(LO.created AS TIME)) AS logout,
    e.fname+' '+e.lname Employee, li.Emp_ID

FROM
    Logins LI
LEFT OUTER JOIN Logins LO ON
    LO.action = 'logout' AND
    CAST(LO.created AS DATE) = CAST(LI.created AS DATE)
    JOIN dbo.Employees AS E ON E.ID = li.Emp_ID

WHERE
    LI.action = 'login'
GROUP BY
    CAST(LI.created AS DATE), E.fname + ' ' + E.lname, li.Emp_ID

But the result is not correct.

1. Notice that the last two result for different users are the same. for example 09:02:28 appears twice instead of 9:01:58

2. Also I have issues with a login without a logout for emp_id = 3. this happens when the app shuts down unexpectedly.

3 how can i place a 00:00:00 in the case when there is no logout 4. Or what would be your suggestion of what to do in this case?

I need to select a result set that looks like this:

|    created |    login |   logout |    Employee | Emp_ID |
|------------|----------|----------|-------------|--------|
| 2016-01-20 | 10:44:42 | 18:30:13 | James Brown |      1 |
| 2016-01-21 | 08:46:28 | 13:46:46 | James Brown |      1 |
| 2016-01-22 | 08:49:21 | 22:22:23 | James Brown |      1 |
| 2016-01-22 | 14:42:06 | 00:00:00 |    Kemi Ojo |      3 |
| 2016-01-23 | 08:01:47 | 09:01:58 | James Brown |      1 |
| 2016-01-23 | 08:02:06 | 09:02:28 |    Kemi Ojo |      3 |

SQL fiddle

Answer 1

First of all, cudos for a prefect question. Table structure, fiddle demo and expected output helps a lot.

Now I tried this and it is working in fiddle. Please recheck and let me know.

select t_login.emp_id,t_login.dt_created as created,t_login.login,
case when t_logout.logout is null 
    then cast('00:00:00' as time)
else t_logout.logout end as logout,
e.fname+' '+e.lname Employee
from 
    (select emp_id,CAST(created AS DATE) AS dt_created,
    MIN(CAST(created AS TIME)) as login
    from logins
    where action='login'
    group by emp_id,CAST(created AS DATE)) t_login
left join
    (select emp_id,CAST(created AS DATE) AS dt_created,
    max(CAST(created AS TIME)) as logout
    from logins
    where action='logout'
    group by emp_id,CAST(created AS DATE)) t_logout
        on t_login.emp_id=t_logout.emp_id
        and t_login.dt_created=t_logout.dt_created
inner join 
    employees e
        on e.id=t_login.emp_id

PS: This will not take care of cases where there is no login on a particular day and only a logout. If you want that, then use a full outer join and use same case statement as I used in outer select clause.

See fiddle demo here

http://sqlfiddle.com/#!3/465f0/34

Ouput

+---------+-------------+-------------------+-------------------+-------------+
| emp_id  |  created    |      login        |      logout       |  Employee   |
+---------+-------------+-------------------+-------------------+-------------+
|      1  | 2016-01-20  | 10:44:42.0000000  | 18:30:13.0000000  | James Brown |
|      1  | 2016-01-21  | 08:46:28.0000000  | 13:46:46.0000000  | James Brown |
|      1  | 2016-01-22  | 08:49:21.0000000  | 22:22:23.0000000  | James Brown |
|      3  | 2016-01-22  | 14:42:06.0000000  | 00:00:00.0000000  | Kemi Ojo    |
|      1  | 2016-01-23  | 08:01:47.0000000  | 09:01:58.0000000  | James Brown |
|      3  | 2016-01-23  | 08:02:06.0000000  | 09:02:28.0000000  | Kemi Ojo    |
+---------+-------------+-------------------+-------------------+-------------+

Answer 2

This is my suggestion (be aware of culture specific date literals. I had to set a language to get a correct date conversion):

SET LANGUAGE GERMAN;

DECLARE @logins TABLE(Emp_ID INT,Created DATETIME, Action VARCHAR(100));
INSERT INTO @logins VALUES
 (1,'20/01/2016 10:44:42 AM','login')
,(1,'20/01/2016 4:45:49 PM','logout')
,(1,'20/01/2016 6:30:13 PM','logout')
,(1,'21/01/2016 8:46:28 AM','login')
,(1,'21/01/2016 9:46:42 AM','login')
,(1,'21/01/2016 1:46:46 PM','logout')
,(1,'22/01/2016 8:49:21 AM','login')
,(1,'22/01/2016 1:49:27 PM','logout')
,(1,'22/01/2016 2:29:53 PM','login')
,(1,'22/01/2016 2:30:13 PM','logout')
,(3,'22/01/2016 2:42:06 PM','login')
,(1,'22/01/2016 9:57:22 PM','login')
,(1,'22/01/2016 10:22:23 PM','logout')
,(1,'23/01/2016 8:01:47 AM','login')
,(1,'23/01/2016 9:01:58 AM','logout')
,(3,'23/01/2016 8:02:06 AM','login')
,(3,'23/01/2016 9:02:28 AM','logout');

DECLARE @employees TABLE(ID INT,Fname VARCHAR(100),Lname VARCHAR(100));
INSERT INTO @employees VALUES
 (1,'James','Brown')
,(2,'Mark','Bond')
,(3,'Kemi','Ojo'); 

WITH Logins AS
(
    SELECT
         MIN(PureDate) AS Created
        ,MIN(CAST(l.Created AS TIME)) AS LoginTime
        ,l.Emp_ID
    FROM @logins AS l
    CROSS APPLY(SELECT CAST(l.Created AS DATE) PureDate) AS Created 
    WHERE l.Action ='login'
    GROUP BY l.Emp_ID,PureDate  
)
,Logouts AS
(
    SELECT
         MAX(PureDate) AS Created
        ,MAX(CAST(l.Created AS TIME)) AS LogoutTime
        ,l.Emp_ID
    FROM @logins AS l
    CROSS APPLY(SELECT CAST(l.Created AS DATE) PureDate) AS Created 
    WHERE l.Action ='logout'
    GROUP BY l.Emp_ID,PureDate  
)
SELECT Logins.Created
      ,Logins.LoginTime 
      ,ISNULL(Logouts.LogoutTime,'00:00:00')
      ,e.Lname 
      ,e.ID 
FROM Logins
INNER JOIN @employees AS e ON e.ID = Logins.Emp_ID
LEFT JOIN Logouts ON Logins.Emp_ID = Logouts.Emp_ID 
                 AND Logins.Created = Logouts.Created 
ORDER BY Created,LoginTime 

The result

2016-01-20  10:44:42    18:30:13    Brown   1
2016-01-21  08:46:28    13:46:46    Brown   1
2016-01-22  08:49:21    22:22:23    Brown   1
2016-01-22  14:42:06    00:00:00    Ojo     3
2016-01-23  08:01:47    09:01:58    Brown   1
2016-01-23  08:02:06    09:02:28    Ojo     3