February 15, 2011 at 7:53 am
This is what I am trying to do
CREATE TABLE [dbo].[user_trans](
[id] [int] IDENTITY(1,1) NOT NULL,
[user_id] [nchar](10) NULL,
[trans_type] [varchar](1) NULL,
[trans_time] [datetime] NULL
)
SET IDENTITY_INSERT [dbo].[user_trans] ON;
BEGIN TRANSACTION;
INSERT INTO [dbo].[user_trans]([id], [user_id], [trans_type], [trans_time])
SELECT 1, N'USER1', N'L', '20110201 08:00:00.000' UNION ALL
SELECT 2, N'USER1', N'O', '20110201 08:05:00.000' UNION ALL
SELECT 3, N'USER2', N'L', '20110201 08:02:00.000' UNION ALL
SELECT 4, N'USER3', N'L', '20110201 08:02:00.000' UNION ALL
SELECT 5, N'USER1', N'L', '20110201 08:02:00.000' UNION ALL
SELECT 6, N'USER1', N'L', '20110201 08:06:00.000' UNION ALL
SELECT 7, N'USER1', N'O', '20110201 08:11:00.000' UNION ALL
SELECT 8, N'USER2', N'O', '20110201 08:15:00.000' UNION ALL
SELECT 9, N'USER3', N'O', '20110201 08:20:00.000'
COMMIT;
RAISERROR (N'[dbo].[user_trans]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO
SET IDENTITY_INSERT [dbo].[user_trans] OFF;
I am looking for an output something like below
------------------------------------------------------------------------
UserIDL (login) timeO (logoff) time
------------------------------------------------------------------------
USER12011-02-01 08:00:00.0002011-02-01 08:05:00.000
USER22011-02-01 08:02:00.0002011-02-01 08:15:00.000
USER32011-02-01 08:02:00.0002011-02-01 08:20:00.000
USER12011-02-01 08:02:00.0002011-02-01 08:05:00.000
USER12011-02-01 08:06:00.0002011-02-01 08:11:00.000
------------------------------------------------------------------------
I can accomplish the above using cursor, but when I do that with about 20,000 rows it is taking a lot of time. I know there is way we can do this using sets, and I would appreciate if anyone can point me to that direction.
Second Part of the script has to find the error (not per se error, but anomaly that needs to be eliminated) in the above results. I am trying to find out the time a user has been logged into the system from the results. In the output look at rows for USER1
------------------------------------------------------------------------
UserIDL (login) timeO (logoff) time
------------------------------------------------------------------------
USER12011-02-01 08:00:00.0002011-02-01 08:05:00.000
USER12011-02-01 08:02:00.0002011-02-01 08:05:00.000
USER12011-02-01 08:06:00.0002011-02-01 08:11:00.000
------------------------------------------------------------------------
Here row 2 is considered duplicate and eliminated or marked as error, as the user cannot be logged in twice at the same time. Not sure if that makes sense.
Thanks for any pointers in the direction of the solution.
George
February 15, 2011 at 10:31 am
I have taken the liberty of adding an entry to your table:
SELECT 10, N'USER1', N'L', '20110201 08:00:00.000' UNION ALL
with that entry I then executed the following:
;with numbered as (Select rowno=row_number() OVER
(partition by [user_id],trans_time order by [user_id]),[user_id],trans_type,trans_time from #user_trans)
select * from numbered
A partial list of the results:
rownouser_idtrans_typetrans_time
1USER1 L2011-02-01 08:00:00.000
2USER1 L2011-02-01 08:00:00.000
1USER1 L2011-02-01 08:02:00.000
1USER1 O2011-02-01 08:05:00.000
1USER1 L2011-02-01 08:06:00.000
1USER1 O2011-02-01 08:11:00.000
Alter the above T-SQL to show duplicates by Login or Logout
;with numbered as (Select rowno=row_number() OVER
(partition by [user_id],trans_time,trans_type order by [user_id]),[user_id],trans_type,trans_time from #user_trans)
select * from numbered
Note that duplicate entries have a rowno value greater than 1
February 15, 2011 at 10:36 am
I would do this in two steps:
First, select all the logout times, possibly using a Distinct operator if there is a possibility of a duplicate.
Then, select the Min login that's greater than the prior logout, for each, or the Min login for the first logout.
That will give you a set of the earliest login for each logout, which should give you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 15, 2011 at 11:55 am
;WITH Grp
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY trans_time)
- ROW_NUMBER() OVER (PARTITION BY user_id, trans_type ORDER BY trans_time) AS Grp
FROM dbo.user_trans
)
,GrpNoDups
AS
(
SELECT user_id, trans_type, grp
,MIN(trans_time) AS trans_time
FROM Grp
GROUP BY user_id, trans_type, grp
)
,UserOrder
AS
(
SELECT user_id, trans_time
,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY trans_time) AS RowNum
FROM GrpNoDups
)
SELECT F.user_id
,F.trans_time AS LoginTime
,T.trans_time AS LogOffTime
FROM UserOrder F
LEFT JOIN UserOrder T
ON F.user_id = T.user_id
AND T.RowNum = F.RowNum + 1
WHERE F.RowNum % 2 = 1
ORDER BY LoginTime
February 15, 2011 at 12:02 pm
bitbucket-25253: Thanks for the pointer, it definitely gets the duplicates in a efficient way. I think I should be able to utilize this snippet. In this situation, any Login that occurs after another login and before the corresponding logoff is considered duplicate, not necessarily the same time stamp.
GSquared: That is exactly what I was thinking...
Ken McKelvey: That code just works! - I sure have to brush up my tsql knowledge to understand the script in the first place. Thanks so much. I will study how to wrote the code and see what I can do. I know I can use it blindly, but there is no fun in doing that 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply