How to select one row from multiple rows in SQL

Question

0.00/5 (No votes)

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 1-Sep-20 20:00pm

chamindat

Updated 1-Sep-20 22:48pm

Richard Deeming

v4

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2020-09-01T22:48:00

Solution 1

Try something like this:

SQL

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)
;

Posted 1-Sep-20 22:48pm

Richard Deeming

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/