Comments by chamindat (Top 13 by date)

I amendment Describe the problem, please check

Actually, I don't mention all data columns, it's enough to filter from clearing date

Thanks a lot 😍💗💗

this is what i done finally

ALTER PROCEDURE [dbo].[AUDIT_LOGINLOGOUT_MGMT]
AS

DECLARE @PREVIOUWORKINGHOUR VARCHAR(500);

BEGIN
SET NOCOUNT ON;

SET @PREVIOUWORKINGHOUR = FORMAT(DATEADD(HH ,-1, GETDATE()),'dd/MM/yyyy HH');

SELECT
W.ACCESS_ID,
W.ACCESS_NAME,
W.ACCESS_TYPE,
W.ACCESS_USER_CC,
W.ACCESS_PROFILE_NAME,
W.ACCESS_MACHINE_NAME,
W.ACCESS_MACHINE_IP,
W.ACCESS_LOGIN_DATETIME,
W.ACCESS_LOGOUT_DATETIME
FROM (

SELECT
ACCESS_ID='Access_Id',
ACCESS_NAME='Access_Name',
ACCESS_TYPE = 'Access_Type',
ACCESS_USER_CC='Access_Country',
ACCESS_PROFILE_NAME='Access_Profile',
ACCESS_MACHINE_NAME='Access_SourceHost',
ACCESS_MACHINE_IP='Access_SourceIP',
ACCESS_LOGIN_DATETIME='Access_LoginTimeStamp',
ACCESS_LOGOUT_DATETIME='Access_LogoutTimestamp',
Rn='0'

UNION ALL

SELECT
T1.Access_Id,
T1.Access_Name,
T1.Access_Type,
T1.Access_Country,
T1.Access_Profile,
T1.Access_SourceHost,
T1.Access_SourceIP,
T1.Access_LoginTimeStamp,
T2.Access_LogoutTimestamp,
RANK() OVER (PARTITION BY T1.Access_Id ORDER BY T1.Access_Id) As Rn
FROM (

SELECT
ROW_NUMBER() OVER( PARTITION BY Access_Id ORDER BY Access_LoginTimeStamp) AS Row_No,
X.Access_Id,
X.Access_Name,
X.Access_Type,
X.Access_Country,
X.Access_Profile,
X.Access_SourceHost,
X.Access_SourceIP,
X.Access_LoginTimeStamp
FROM
(
SELECT
T.ACCESS_ID AS Access_Id,
T.ACCESS_NAME AS Access_Name,
T.ACCESS_TYPE AS Access_Type,
T.ACCESS_USER_CC AS Access_Country,
T.ACCESS_PROFILE_NAME AS Access_Profile,
T.ACCESS_MACHINE_NAME AS Access_SourceHost,
T.ACCESS_MACHINE_IP AS Access_SourceIP,
T.ACCESS_DATETIME AS Access_LoginTimeStamp
FROM
(
SELECT
ACCESS_ID AS Access_Id,
MAX(ACCESS_DATETIME) AS Access_LoginTimeStamp
FROM ALOGINEVENTS
WHERE 1=1
AND (ACCESS_EVENT_STATUS=1)
AND (FORMAT(CONVERT(DATETIME,ACCESS_DATETIME,105),'dd/MM/yyyy HH') < @PREVIOUWORKINGHOUR)
GROUP BY ACCESS_ID
HAVING COUNT(ACCESS_ID)%2=1
) AS M
INNER JOIN ALOGINEVENTS AS T ON
T.Access_Id = M.Access_Id AND
T.ACCESS_DATETIME = M.Access_LoginTimeStamp
WHERE T.ACCESS_ID IN (
SELECT
ACCESS_ID AS Access_Id
FROM ALOGINEVENTS
WHERE 1=1
AND (ACCESS_EVENT_STATUS=2)
AND (FORMAT(CONVERT(DATETIME,ACCESS_DATETIME,105),'dd/MM/yyyy HH') = @PREVIOUWORKINGHOUR)
))X )
AS T1 LEFT JOIN (
SELECT ROW_NUMBER() OVER( PARTITION BY ACCESS_ID ORDER BY ACCESS_DATETIME) AS Row_No,
ACCESS_ID AS Access_Id,
ACCESS_NAME AS Access_Name,
ACCESS_TYPE AS Access_Type,
ACCESS_USER_CC AS Access_Country,
ACCESS_PROFILE_NAME AS Access_Profile,
ACCESS_MACHINE_NAME AS Access_SourceHost,
ACCESS_MACHINE_IP AS Access_SourceIP,
ACCESS_DATETIME AS Access_LogoutTimestamp
FROM ALOGINEVENTS
WHERE 1=1
AND (ACCESS_EVENT_STATUS = 2)
AND (FORMAT(CONVERT(DATETIME,ACCESS_DATETIME,105),'dd/MM/yyyy HH') = @PREVIOUWORKINGHOUR)
) AS T2 ON T1.Row_No = T2.Row_No AND T1.ACCESS_ID = T2.ACCESS_ID

UNION ALL

SELECT
T1.Access_Id,
T1.Access_Name,
T1.Access_Type,
T1.Access_Country,
T1.Access_Profile,
T1.Access_SourceHost,
T1.Access_SourceIP,
T1.Access_LoginTimeStamp,
T2.Access_LogoutTimestamp,
RANK() OVER (PARTITION BY T1.Access_Id ORDER BY T1.Access_Id) As Rn
FROM (
SELECT ROW_NUMBER() OVER( PARTITION BY ACCESS_ID ORDER BY ACCESS_DATETIME) AS Row_No,
ACCESS_ID AS Access_Id,
ACCESS_NAME AS Access_Name,
ACCESS_TYPE AS Access_Type,
ACCESS_USER_CC AS Access_Country,
ACCESS_PROFILE_NAME AS Access_Profile,
ACCESS_MACHINE_NAME AS Access_SourceHost,
ACCESS_MACHINE_IP AS Access_SourceIP,
ACCESS_DATETIME AS Access_LoginTimeStamp
FROM ALOGINEVENTS
WHERE 1=1
AND (ACCESS_EVENT_STATUS = 1)
AND (FORMAT(CONVERT(DATETIME,ACCESS_DATETIME,105),'dd/

when use like this some records are missing



WITH cte As
(
SELECT
ACCESS_ID as Access_Id,
ACCESS_DATETIME as Access_LoginTimeStamp,
ACCESS_EVENT_STATUS as Access_Status,
ROW_NUMBER() OVER (PARTITION BY ACCESS_ID ORDER BY ACCESS_DATETIME) As RN
FROM
ALOGINEVENTS_NEW
)
SELECT
IsNull(LI.Access_Id, LO.Access_Id) As Access_Id,
LI.Access_LoginTimeStamp,
LI.Access_Status,
LO.Access_LoginTimeStamp As Access_LogoutTimestamp,
LO.Access_Status
FROM
cte As LI
FULL OUTER JOIN cte As LO
ON LO.Access_Id = LI.Access_Id
And LO.RN = LI.RN + 1
WHERE
(LI.Access_Status = 1 Or LI.Access_Id Is Null)
And
(LO.Access_Status = 2 Or LO.Access_Id Is Null)
;

when use like this some records are missing



WITH cte As
(
SELECT
ACCESS_ID as Access_Id,
ACCESS_DATETIME as Access_LoginTimeStamp,
ACCESS_EVENT_STATUS as Access_Status,
ROW_NUMBER() OVER (PARTITION BY ACCESS_ID ORDER BY ACCESS_DATETIME) As RN
FROM
ALOGINEVENTS_NEW
)
SELECT
IsNull(LI.Access_Id, LO.Access_Id) As Access_Id,
LI.Access_LoginTimeStamp,
LI.Access_Status,
LO.Access_LoginTimeStamp As Access_LogoutTimestamp,
LO.Access_Status
FROM
cte As LI
FULL OUTER JOIN cte As LO
ON LO.Access_Id = LI.Access_Id
And LO.RN = LI.RN + 1
WHERE
(LI.Access_Status = 1 Or LI.Access_Id Is Null)
And
(LO.Access_Status = 2 Or LO.Access_Id Is Null)
;

Thank you very much Richard