select group same column by date into different column

Question

I have a table of logins that looks like this:

Created                |  Action
20/01/2016 08:00:00 AM    login
20/01/2016 10:05:10 AM    logout
20/01/2016 12:00:00 PM    login
20/01/2016 04:12:22 PM    logout
21/01/2016 08:00:50 AM    login
21/01/2016 09:44:42 AM    login
21/01/2016 10:44:42 AM    login
21/01/2016 04:00:42 PM    logout

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

Created        |    Login   |    Logout        
20/01/2016      08:00:00 AM   04:12:22 PM
21/01/2016      08:00:50 PM   04:00:42 PM

Here is what I have tried:

SELECT
    CONVERT(VARCHAR(10),li.Created,10) [Date],
    CONVERT(VARCHAR(8),MAX(li.Created),8) [Login],
    CONVERT(VARCHAR(8),MAX(lo.Created),8) [Logout]
FROM Logins li
LEFT JOIN Logins lo ON lo.[Action] = 'logout'
GROUP BY li.Created

But the result is not grouped by date.

What is the proper way?

Answer 1

You can try like this:

SELECT 
CONVERT(VARCHAR(10),li.Created,10) [Date], 
CONVERT(VARCHAR(8),MAX(li.Created),8) [Login],
CONVERT(VARCHAR(8),MAX(lo.Created),8) [Logout] 
From Logins li 
Left Join Logins lo  on lo.[Action] = 'logout' 
GROUP BY CAST(li.Created AS DATE)

ie, you need to group by your records on the date only and not by date and time both.

Answer 2

I'm going to assume that the 4am logout on the 20th in your results is an oversight, otherwise we would need to know the logic that determines that it should be considered the logout time for that day.

One problem is that you're trying to display just dates as your groups, but you're grouping by the date and time.

Try this instead:

SELECT
    CAST(LI.created AS DATE) AS created,
    MIN(CAST(LI.created AS TIME)) AS login,
    MAX(CAST(LO.created AS TIME)) AS logout
FROM
    dbo.My_Table LI
LEFT OUTER JOIN dbo.My_Table LO ON
    LO.action = 'logout' AND
    CAST(LO.created AS DATE) = CAST(LI.created AS DATE)
WHERE
    LI.action = 'login'
GROUP BY
    CAST(LI.created AS DATE)

Another thing to consider is whether or not login periods can span date boundaries. If this is in the tech industry then it wouldn't be odd to have a login of 1/20 10pm and a logout of 1/21 2am. ;)