May 27, 2013 at 5:44 am
Create table #temp
(
ID int identity(1,1),
ActivityName varchar(max),
ActivityDate datetime
)
Insert into #temp
Values
('Successfully Login into Dynamic Portal Application','2013-05-20 13:22:21'),
('Login Success into Liferay SAMl Application','2013-05-21 15:28:08'),
('Login Success into Zoho SAMl Application','2013-05-21 15:28:08'),
('Login Success into Intranet Non SAMl Application','2013-05-23 13:04:22'),
('Logout Successfully from application','2013-05-21 18:20:07'),
('Successfully Login into Dynamic Portal Application','2013-05-20 14:22:21'),
('Login Success into Liferay SAMl Application','2013-05-21 16:28:08'),
('Login Success into Zoho SAMl Application','2013-05-21 16:29:08'),
('Login Success into Intranet Non SAMl Application','2013-05-21 15:04:22'),
('Logout Successfully from application','2013-05-21 19:20:07')
Desired Output:
ActivityName LoginTime LogOutTime
Liferay SAMl Application 2013-05-21 15:28:082013-05-21 18:20:07
Zoho SAMl Application 2013-05-21 15:28:082013-05-21 18:20:07
Intranet Non SAMl Application 2013-05-23 13:04:222013-05-21 18:20:07
Liferay SAMl Application 2013-05-21 16:28:082013-05-21 19:20:07
Zoho SAMl Application 2013-05-21 16:29:082013-05-21 19:20:07
Intranet Non SAMl Application 2013-05-21 15:04:222013-05-21 19:20:07
LogOut time of the Activities will be the logout time of application....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 27, 2013 at 5:54 am
I have change structure of a table and added one more column. Now plz help me in achieving that output
Create table #temp
(
ID int identity(1,1),
ActivityName varchar(max),
ApplicationName varchar(100),
ActivityDate datetime
)
Insert into #temp
Values
('Successfully Login into Dynamic Portal Application','Login','2013-05-20 13:22:21'),
('Login Success into Liferay SAMl Application','Liferay SAMl Application','2013-05-21 15:28:08'),
('Login Success into Zoho SAMl Application','Zoho SAMl Application','2013-05-21 15:28:08'),
('Login Success into Intranet Non SAMl Application','Intranet Non SAMl Application','2013-05-23 13:04:22'),
('Logout Successfully from application','Logout','2013-05-21 18:20:07'),
('Successfully Login into Dynamic Portal Application','Login','2013-05-20 14:22:21'),
('Login Success into Liferay SAMl Application','Liferay SAMl Application','2013-05-21 16:28:08'),
('Login Success into Zoho SAMl Application','Zoho SAMl Application','2013-05-21 16:29:08'),
('Login Success into Intranet Non SAMl Application','Intranet Non SAMl Application','2013-05-21 15:04:22'),
('Logout Successfully from application','Logout','2013-05-21 19:20:07')
drop table #temp
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 27, 2013 at 6:12 am
It is not clearly understandable what u need.
Please brief of what u need and what problem u'r facing right now.
May 27, 2013 at 6:13 am
If u'r facing problem with insertion see below demo.
DROP TABLE #temp
Create table #temp
(
ID int identity(1,1),
ActivityName varchar(max),
ApplicationName varchar(100),
ActivityDate datetime2
)
Insert into #temp
Values
('Successfully Login into Dynamic Portal Application','Login',CAST('2013-05-20 13:22:21.000' AS DATETIME2))
May 27, 2013 at 6:15 am
Aadhar Joshi (5/27/2013)
If u'r facing problem with insertion see below demo.DROP TABLE #temp
Create table #temp
(
ID int identity(1,1),
ActivityName varchar(max),
ApplicationName varchar(100),
ActivityDate datetime2
)
Insert into #temp
Values
('Successfully Login into Dynamic Portal Application','Login',CAST('2013-05-20 13:22:21.000' AS DATETIME2))
I am not facing any problem with INSERTION...
I have already given table structure with sample values..
from that table this is the desired output is required:
Desired Output:
ActivityName LoginTime LogOutTime
Liferay SAMl Application 2013-05-21 15:28:082013-05-21 18:20:07
Zoho SAMl Application 2013-05-21 15:28:082013-05-21 18:20:07
Intranet Non SAMl Application 2013-05-23 13:04:222013-05-21 18:20:07
Liferay SAMl Application 2013-05-21 16:28:082013-05-21 19:20:07
Zoho SAMl Application 2013-05-21 16:29:082013-05-21 19:20:07
Intranet Non SAMl Application 2013-05-21 15:04:222013-05-21 19:20:07
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 27, 2013 at 6:35 am
Something like this..
SELECTt.ApplicationName AS ActivityName, t.ActivityDate AS LoginTime, L.ActivityDate AS LogOutTime
FROM#temp AS t
CROSS APPLY(
SELECTTOP 1 *
FROM#temp AS t1
WHEREt1.ID > t.ID
ANDt1.ApplicationName = 'Logout'
ORDER BY t1.ID
) AS L
WHEREt.ApplicationName NOT IN ( 'Login', 'Logout' )
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 27, 2013 at 7:01 am
Create table #temp
(
ID int identity(1,1),
ActivityName varchar(max),
ActivityDate DATETIME2 ,
LoginActivity int
)
Insert into #temp
Values
('Successfully Login into ABC','2013-05-20 13:22:21',1),
('Logout Successfully from ABC','2013-05-21 18:20:07',2),
('Successfully Login into SAMl Application','2013-05-21 15:28:08',1),
('Logout Successfully from SAMl Application','2013-05-21 15:28:08',2)
Create table #OutputTable
(
ActivityName varchar(max),
LoginTime DATETIME2 ,
LogoutTime DATETIME2
)
INSERT INTO #OutputTable(ActivityName)
SELECT DISTINCT CASE WHEN ActivityName LIKE '%Successfully Login into%' THEN SUBSTRING(ActivityName,24,LEN(ActivityName))
WHEN ActivityName LIKE '%Logout Successfully from%' THEN SUBSTRING(ActivityName,25,LEN(ActivityName))
ELSE '' END
FROM #temp
UPDATE l set l.LoginTime = r.ActivityDate
FROM #OutputTable l INNER JOIN #temp r ON l.ActivityName = SUBSTRING(r.ActivityName,24,LEN(r.ActivityName)) AND (r.ActivityName LIKE '%Successfully Login into%')
UPDATE l set l.LogoutTime = r.ActivityDate
FROM #OutputTable l INNER JOIN #temp r ON l.ActivityName = SUBSTRING(r.ActivityName,25,LEN(r.ActivityName)) AND (r.ActivityName LIKE '%Logout Successfully from%')
SELECT * FROM #OutputTable
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply