displaye data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

displaye data

Author

Topic

vipinjha
Starting Member

21 Posts

Posted - 2012-02-02 : 07:40:19

Dear All ,
I have a query in which i want to display two diffrent column for logindatetime and logoutdatetime

in my query i am getting data in 2 rows , i want to display both time in same rows in diffrent column.

my query is below
SELECT EVENT_ID,LOGGED_IN_USER_ID,EVENT_TIME,'' as 'Logindatetime','' as 'Logoutdatetime'
FROM sso_audit_tbl
where SSO_AUDIT_TBL.LOGGED_IN_USER_ID='31156'
order by LOGGED_IN_USER_ID,CLIENT_MACHINE,EVENT_TIME,EVENT_ID

i want to displaye data in below format
EVENT time Login Logout
LOGIN 1/2/12 7:09 AM
LOGOUT 1/2/12 7:16 AM
LOGIN 1/2/12 7:26 AM
LOGOUT 1/2/12 7:26 AM
LOGIN 1/2/12 7:26 AM
LOGOUT 1/2/12 7:26 AM

Regards,
Vipin jha

Vipin jha

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-02 : 07:57:37

Do you have something like a login session id or some other column that would help you identify which logout should be associated with which login? If you don't have that, whatever query we write may not be reliable, especially so if the user can have more than one simultaneous login session.

vipinjha
Starting Member

21 Posts

Posted - 2012-02-02 : 08:05:16

yaya Eventid is the column which is showing that Login AND Logout

thankx ,

regards,
Vipin jha

Vipin jha

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-02 : 08:17:00

You should be able to use the PIVOT operator, or do "manual pivoting" as shown below:

SELECT
	UserId,
	Event_id,
	MAX(CASE WHEN [Event] = 'LOGIN' THEN EVENT_TIME END) AS LoginTime,
	MAX(CASE WHEN [Event] = 'LOGOUT' THEN EVENT_TIME END) AS LogoutnTime
FROM
	sso_audit_tbl
WHERE
	SSO_AUDIT_TBL.LOGGED_IN_USER_ID = '31156'
GROUP BY
	UserId,
	Event_id
ORDER BY
	LOGGED_IN_USER_ID,
	CLIENT_MACHINE,
	EVENT_TIME,
	EVENT_ID

vipinjha
Starting Member

21 Posts

Posted - 2012-02-02 : 08:41:41

DATA IS COMIN BUT NOT GETTING IN SAME ROWS

I AM GETTING IN BELOW FORMAT
Event_id EVENT_TIME LoginTime LogoutnTime
LOGIN 1/2/12 7:09 AM 1/2/12 7:09 NULL
LOGOUT 1/2/12 7:16 AM NULL 1/2/12 7:16
LOGIN 1/2/12 7:26 AM 1/2/12 7:26 NULL
LOGOUT 1/2/12 7:26 AM NULL 1/2/12 7:26
LOGIN 1/2/12 7:26 AM 1/2/12 7:26 NULL
LOGOUT 1/2/12 7:26 AM NULL 1/2/12 7:26

I WANT TO DISPLAY BOTH LOGOUT AND LOGIN INSAME ROWS

REGARDS,
vIPIN JHA

Vipin jha

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-02 : 08:54:37

Can you post the query you are using?

vipinjha
Starting Member

21 Posts

Posted - 2012-02-02 : 09:06:06

SELECT
LOGGED_IN_USER_ID,
Event_id,
CLIENT_MACHINE,
EVENT_TIME,

MAX(CASE WHEN [EVENT_ID] = 'LOGIN' THEN EVENT_TIME END) AS LoginTime,
MAX(CASE WHEN [EVENT_ID] = 'LOGOUT' THEN EVENT_TIME END) AS LogoutnTime
FROM
sso_audit_tbl
WHERE
SSO_AUDIT_TBL.LOGGED_IN_USER_ID = '31156'
GROUP BY
LOGGED_IN_USER_ID,
Event_id,
CLIENT_MACHINE,EVENT_TIME,
EVENT_ID

ORDER BY
LOGGED_IN_USER_ID,
CLIENT_MACHINE,
EVENT_TIME,
EVENT_ID

Vipin jha

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-02 : 09:09:12

Remove EVENT_TIME from the select list as in:

SELECT
	LOGGED_IN_USER_ID,
	Event_id,
	CLIENT_MACHINE,
	--EVENT_TIME,
	MAX(CASE WHEN [EVENT_ID] = 'LOGIN' THEN EVENT_TIME END) AS LoginTime,
	MAX(CASE WHEN [EVENT_ID] = 'LOGOUT' THEN EVENT_TIME END) AS LogoutnTime
FROM
	sso_audit_tbl
WHERE
	SSO_AUDIT_TBL.LOGGED_IN_USER_ID = '31156'
GROUP BY
	LOGGED_IN_USER_ID,
	Event_id,
	CLIENT_MACHINE,
	--EVENT_TIME,
	EVENT_ID
ORDER BY
	LOGGED_IN_USER_ID,
	CLIENT_MACHINE,
	--EVENT_TIME,
	EVENT_ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources