October 21, 2008 at 10:18 pm
I have table to store user login details. Now i want to get the time difference for each login for all users. The table is as below
User Id Start Time Stop Time
1 2008-10-13 13:01:10.0002008-10-13 13:01:26.000
1 2008-10-13 13:25:11.0002008-10-13 13:30:27.000
1 2008-10-13 16:30:54.0002008-10-13 16:31:10.000
2 2008-10-13 16:31:05.0002008-10-13 16:31:28.000
2 2008-10-13 16:40:36.0002008-10-13 16:50:52.000
2 2008-10-13 16:55:23.0002008-10-13 16:60:47.000
Now i have to compare the 1st & 2nd row, 2nd & 3rd row and so on
October 21, 2008 at 11:14 pm
I'm not sure what do you mean by "compare" but here's the core code that you can just modify to fit your needs:
set nocount on
declare @LoginTable table (UserID int, StartTime datetime, StopTime datetime)
insert into @LoginTable values(1, '2008-10-13 13:01:10.000', '2008-10-13 13:01:26.000')
insert into @LoginTable values(1, '2008-10-13 13:25:11.000', '2008-10-13 13:30:27.000')
insert into @LoginTable values(1, '2008-10-13 16:30:54.000', '2008-10-13 16:31:10.000')
insert into @LoginTable values(2, '2008-10-13 16:31:05.000', '2008-10-13 16:31:28.000')
insert into @LoginTable values(2, '2008-10-13 16:40:36.000', '2008-10-13 16:50:52.000')
insert into @LoginTable values(2, '2008-10-13 16:55:23.000', '2008-10-13 16:59:47.000')
select UserId, DATEDIFF(mi, StartTime, StopTime) as NumberOfMinutes,
'2nd row' = (select top 1 DATEDIFF(mi, StartTime, StopTime) as NumberOfMinutes from @LoginTable a where a.UserID = b.UserID and a.StartTime > b.StartTime)
from @LoginTable b
order by StartTime
Happy coding!
-- CK
October 21, 2008 at 11:23 pm
Arul Manoj (10/21/2008)
I have table to store user login details. Now i want to get the time difference for each login for all users. The table is as belowUser Id Start Time Stop Time
1 2008-10-13 13:01:10.0002008-10-13 13:01:26.000
1 2008-10-13 13:25:11.0002008-10-13 13:30:27.000
1 2008-10-13 16:30:54.0002008-10-13 16:31:10.000
2 2008-10-13 16:31:05.0002008-10-13 16:31:28.000
2 2008-10-13 16:40:36.0002008-10-13 16:50:52.000
2 2008-10-13 16:55:23.0002008-10-13 16:60:47.000
Now i have to compare the 1st & 2nd row, 2nd & 3rd row and so on
Use the following
select UserId'User ID', StartTime'Start Time', StopTime'Stop Time', convert( varchar,StopTime - StartTime, 108 )'Difference' from [tablename]
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 22, 2008 at 2:25 am
Thanks for your reply.
But i need the following result:
For each user i have to compare the stop time of 1st row with start time of 2nd row and stop time of 2nd row with start time of 3rd row.
The table is like below:
Sl No User Id Start Time Stop Time
1 1 2008-10-13 13:01:10.000 2008-10-13 13:01:26.000
2 1 2008-10-13 13:25:11.000 2008-10-13 13:30:27.000
3 1 2008-10-13 16:30:54.000 2008-10-13 16:31:10.000
4 2 2008-10-13 16:31:05.000 2008-10-13 16:31:28.000
5 2 2008-10-13 16:40:36.000 2008-10-13 16:50:52.000
6 2 2008-10-13 16:55:23.000 2008-10-13 16:60:47.000
Now we have to compare as below:
1. Slno:1 with Slno:2 and Slno:2 with Slno:3
2. Slno:4 with Slno:5 and Slno:5 with Slno:6
October 22, 2008 at 3:07 am
Fairly Simple but not the most elegant as I had to make a duplicate record set in order to seperate the records into groups based on userID
set nocount on
declare @login table (UserID int, StartTime datetime, StopTime datetime)
insert into @login values(1, '2008-10-13 13:01:10.000', '2008-10-13 13:01:26.000')
insert into @login values(1, '2008-10-13 13:25:11.000', '2008-10-13 13:30:27.000')
insert into @login values(1, '2008-10-13 16:30:54.000', '2008-10-13 16:31:10.000')
insert into @login values(2, '2008-10-13 16:31:05.000', '2008-10-13 16:31:28.000')
insert into @login values(2, '2008-10-13 16:40:36.000', '2008-10-13 16:50:52.000')
insert into @login values(2, '2008-10-13 16:55:23.000', '2008-10-13 16:59:47.000')
Select *
From @login
declare @Tmp table (UserID int, StartTime datetime, StopTime Datetime, RowNum int )
Insert into @Tmp
Select UserID, StartTime, stopTime, Row_Number() Over( Partition by UserID Order by StartTime )
From @login
Select
*
,Difference_Minute = DateDiff( Minute, a.StopTime, b.StartTime )
From @Tmp as a
Left Join @Tmp as B
On b.UserID = a.UserID
and b.RowNum = a.RowNum +1
October 22, 2008 at 4:45 am
More Thanks for your help. The result we are getting is
User ID StartTime Stop Time RowNum TimeDifference
1, 2008-10-13 13:25:11.000 2008-10-13 13:30:27.000224
1, 2008-10-13 16:30:54.000 2008-10-13 16:31:10.0003180
2, 2008-10-13 16:40:36.000 2008-10-13 16:50:52.00029
2, 2008-10-13 16:55:23.000 2008-10-13 16:59:47.00035
Now if the Time difference is 24 as in the example i want the first two rows. That is I want the two rows which gives the Time difference value.
October 22, 2008 at 6:18 am
Arul Manoj (10/22/2008)
Thanks for your reply.But i need the following result:
For each user i have to compare the stop time of 1st row with start time of 2nd row and stop time of 2nd row with start time of 3rd row.
The table is like below:
Sl No User Id Start Time Stop Time
1 1 2008-10-13 13:01:10.000 2008-10-13 13:01:26.000
2 1 2008-10-13 13:25:11.000 2008-10-13 13:30:27.000
3 1 2008-10-13 16:30:54.000 2008-10-13 16:31:10.000
4 2 2008-10-13 16:31:05.000 2008-10-13 16:31:28.000
5 2 2008-10-13 16:40:36.000 2008-10-13 16:50:52.000
6 2 2008-10-13 16:55:23.000 2008-10-13 16:60:47.000
Now we have to compare as below:
1. Slno:1 with Slno:2 and Slno:2 with Slno:3
2. Slno:4 with Slno:5 and Slno:5 with Slno:6
it means that the user id will have the entries in 3 slabs only
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 22, 2008 at 6:30 am
krayknot (10/21/2008)
Arul Manoj (10/21/2008)
I have table to store user login details. Now i want to get the time difference for each login for all users. The table is as belowUser Id Start Time Stop Time
1 2008-10-13 13:01:10.0002008-10-13 13:01:26.000
1 2008-10-13 13:25:11.0002008-10-13 13:30:27.000
1 2008-10-13 16:30:54.0002008-10-13 16:31:10.000
2 2008-10-13 16:31:05.0002008-10-13 16:31:28.000
2 2008-10-13 16:40:36.0002008-10-13 16:50:52.000
2 2008-10-13 16:55:23.0002008-10-13 16:60:47.000
Now i have to compare the 1st & 2nd row, 2nd & 3rd row and so on
As i dont have the table and data, but still here is the logic , it may be wrong. but you can take the idea:
select UserId'User ID', StartTime'Start Time', StopTime'Stop Time',
(Select top 1 StartTime from creditcardprocesslog order by StartTime DESC) -
(Select top 2 StopTime from creditcardprocesslog order by StopTime DESC) -
(Select top 2 StartTime from creditcardprocesslog order by StartTime DESC) -
(Select top 1 StopTime from creditcardprocesslog order by StopTime )
(Select top 1 StartTime from creditcardprocesslog order by StartTime)
'Difference' from [tablename]
Use the following
select UserId'User ID', StartTime'Start Time', StopTime'Stop Time', convert( varchar,StopTime - StartTime, 108 )'Difference' from [tablename]
and if there are more than 3 similar rows then you should use while loop
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 23, 2008 at 12:26 pm
You don't need a WHILE loop or anything of that nature. Here's a set-based solution you can use with any given input table:
DECLARE @LoginTable TABLE (
UserID int,
StartTime DateTime,
StopTime DateTime
)
insert into @LoginTable values(1, '2008-10-13 13:01:10.000', '2008-10-13 13:01:26.000')
insert into @LoginTable values(1, '2008-10-13 13:25:11.000', '2008-10-13 13:30:27.000')
insert into @LoginTable values(1, '2008-10-13 16:30:54.000', '2008-10-13 16:31:10.000')
insert into @LoginTable values(2, '2008-10-13 16:31:05.000', '2008-10-13 16:31:28.000')
insert into @LoginTable values(2, '2008-10-13 16:40:36.000', '2008-10-13 16:50:52.000')
insert into @LoginTable values(2, '2008-10-13 16:55:23.000', '2008-10-13 16:59:47.000')
;WITH LOGIN_TABLE AS (
SELECT ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY StartTime) AS RN, X.*
FROM @LoginTable AS X
),
SECONDS_OUTPUT AS (
SELECT A.UserID, A.StopTime, B.StartTime, DateDiff(s,A.StopTime,B.StartTime) AS Secs
FROM LOGIN_TABLE AS A INNER JOIN LOGIN_TABLE AS B
ON A.UserID = B.UserID AND
A.RN = B.RN - 1
)
SELECT UserID, StopTime, StartTime, Secs,
CAST((Secs / 3600) AS varchar(4)) + ':' +
RIGHT('0' + CAST(((Secs % 3600) / 60) AS varchar(2)),2) + ':' +
RIGHT('0' + CAST((Secs % 60) AS varchar(2)),2) AS ElapsedTime
FROM SECONDS_OUTPUT
The output will show each consecutive stop and start time associated with adjacent logins, the number of seconds between those times, and a character representation of the number of hours, minutes, and seconds that number of seconds represents. Enjoy!
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply