Click here to Skip to main content
14,668,823 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have a sample Table :
Access_Id  Access_LoginTimeStamp    Access_Status
1150080    23/08/2020 21:30:57 PM   Login
1150080	   23/08/2020 21:31:57 PM   Logout
1150080	   23/08/2020 21:32:57 PM   Login
1150080    23/08/2020 21:33:58 PM   Logout
1150083	   23/08/2020 21:35:58 PM   Login
1150084	   23/08/2020 21:41:31 PM   Login
1150084	   23/08/2020 21:41:49 PM   Logout

I want the result set like this:
Access_Id  Access_LoginTimeStamp   Access_Status Access_LogoutTimeStamp  Access_Status
1150080    23/08/2020 21:30:57 PM  Login         23/08/2020 21:31:57 PM  Logout
1150080	   23/08/2020 21:32:57 PM  Login         23/08/2020 21:33:58 PM  Logout
1150083	   23/08/2020 21:35:58 PM  Login         -                       -
1150084	   23/08/2020 21:41:31 PM  Login         23/08/2020 21:41:49 PM  Logout


What I have tried:

Is there any way to achieve this in SQL? it's urgent. Any help is really appreciated.

Thank You.
Posted
Updated 1-Sep-20 22:48pm
v4
Comments
Jörgen Andersson 2-Sep-20 2:16am
   
Yes, you can achieve it using a combo of "Islands and Gaps" and Pivot.
There you have your search terms. I have other more urgent stuff to take care of.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Try something like this:
WITH cte As
(
    SELECT
        Access_Id,
        Access_LoginTimeStamp,
        Access_Status,
        ROW_NUMBER() OVER (PARTITION BY Access_Id ORDER BY Access_LoginTimeStamp) As RN
    FROM
        YourTable
)
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 = 'Login' Or LI.Access_Id Is Null)
And 
    (LO.Access_Status = 'Logout' Or LO.Access_Id Is Null)
;
   
Comments
Maciej Los 2-Sep-20 4:54am
   
5ed!
chamindat 2-Sep-20 5:20am
   
Thank you very much Richard
chamindat 8-Sep-20 3:53am
   
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/

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100