1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
| -- insertion d'un jeu d'essais :
WITH
N AS
(SELECT 0 AS I
UNION ALL
SELECT I + 1
FROM N
WHERE I < 9),
M AS
(SELECT N1.I + 10*N2.I AS I
FROM N AS N1
CROSS JOIN N AS N2
CROSS JOIN N AS N3)
INSERT INTO T_CONNEXION_CNX (CNX_USER_NAME, CNX_DH_LOGIN)
SELECT 'User ' + CAST(ABS(CHECKSUM(NEWID())) % 50 AS VARCHAR(32)),
DATEADD(millisecond, ABS(CHECKSUM(NEWID())), '2020-05-01')
FROM M
WHERE I < 25 ;
-- mise à jour date heure de logout :
UPDATE T_CONNEXION_CNX
SET CNX_DH_LOGOUT = DATEADD(millisecond, ABS(CHECKSUM(NEWID())) / 9876, CNX_DH_LOGIN)
-- la requête finale
SELECT CNX_DH_LOGIN, (SELECT COUNT(*)
FROM T_CONNEXION_CNX
WHERE CNX_DH_LOGIN >= LOG_DH.CNX_DH_LOGIN AND CNX_DH_LOGOUT > LOG_DH.CNX_DH_LOGIN)
FROM T_CONNEXION_CNX AS LOG_DH |
Partager