J'ai une table de connexions qui ressemble à ceci:

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

Je dois sélectionner un jeu de résultats qui ressemble à ceci:

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

Voici ce que j'ai essayé:

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

Mais le résultat n'est pas groupé par date.

Quelle est la bonne manière?

7
Smith 22 janv. 2016 à 16:06

3 réponses

Meilleure réponse

Similaire à la réponse de Rahul, mais vous pouvez faire une auto-inscription pour obtenir la première connexion / dernière déconnexion et simplement grouper par date pour obtenir le résultat que vous demandez;

SELECT CONVERT(DATE,     li.created)  [Date], 
       CONVERT(TIME, MIN(li.created)) [Login],
       CONVERT(TIME, MAX(lo.created)) [Logout] 
FROM Logins li 
JOIN Logins lo 
  ON CONVERT(DATE, li.created) = CONVERT(DATE, lo.created)
 AND li.action = 'login'
 AND lo.action = 'logout' 
GROUP BY CONVERT(DATE, li.created)

Un SQLfiddle avec lequel tester.

EDIT: non testé pour SQL Server 2005, vous devrez peut-être définir une longueur pour les varchars;

SELECT CONVERT(VARCHAR, li.created, 110)    [Date], 
       CONVERT(VARCHAR, MAX(li.created), 8) [Login],
       CONVERT(VARCHAR, MAX(lo.created), 8) [Logout] 
FROM Logins li 
JOIN Logins lo 
  ON CONVERT(VARCHAR, li.created, 110) = CONVERT(VARCHAR, lo.created, 110)
 AND li.action = 'login'
 AND lo.action = 'logout' 
GROUP BY CONVERT(VARCHAR, li.created, 110)

Un autre SQLfiddle.

1
Joachim Isaksson 22 janv. 2016 à 22:23

Je vais supposer que la déconnexion à 4 heures du matin le 20 dans vos résultats est un oubli, sinon nous aurions besoin de connaître la logique qui détermine qu'elle devrait être considérée comme l'heure de déconnexion pour ce jour-là.

Un problème est que vous essayez d'afficher uniquement les dates en tant que groupes, mais que vous les regroupez par date et heure.

Essayez plutôt ceci:

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)

Une autre chose à considérer est de savoir si les périodes de connexion peuvent ou non dépasser les limites de date. Si c'est dans l'industrie de la technologie, il ne serait pas étrange d'avoir une connexion de 1/20 22 heures et une déconnexion de 1/21 2 heures du matin. ;)

2
Tom H 22 janv. 2016 à 16:39

Vous pouvez essayer comme ceci:

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)

C'est-à-dire que vous devez regrouper vos enregistrements par date uniquement et non par date et heure à la fois.

3
Rahul Tripathi 22 janv. 2016 à 13:11