November 28, 2012 at 10:59 pm
Hi Everyone,
I'll start with posting DDL and Sample data :
--DDL
Create Table Ex
(
EmployeeId NVarchar(100),
EmployeeName NVarchar(100),
LoginTime DateTime
)
--Sample Data
Insert Into Ex Values('A0001','Vinu','2012-11-02 14:56:11.577');
Insert Into Ex Values('A0034','Sunil','2012-11-02 15:02:11.513');
Insert Into Ex Values('B0001','Gaurav','2012-11-03 14:56:11.577');
Insert Into Ex Values('C0001','Amar','2012-11-03 17:56:11.513');
Insert Into Ex Values('D0001','Vikash','2012-11-03 21:56:11.443');
Insert Into Ex Values('A0034','Sunil','2012-11-03 23:21:11.122');
Insert Into Ex Values('B0012','Rahul','2012-11-04 08:30:11.577');
Insert Into Ex Values('D0004','Rohan','2012-11-04 11:00:11.333');
Insert Into Ex Values('C0023','Karan','2012-11-04 19:25:11.234');
Insert Into Ex Values('A0007','Karthik','2012-11-05 14:56:11.577');
Insert Into Ex Values('B0009','Sachin','2012-11-06 17:56:11.513');
Insert Into Ex Values('D0011','Arif','2012-11-09 08:30:11.577');
Insert Into Ex Values('C0023','Karan','2012-11-09 16:30:11.567');
Insert Into Ex Values('B0009','Sachin','2012-11-10 15:02:11.513');
Insert Into Ex Values('D0011','Arif','2012-11-10 19:23:11.765');
Insert Into Ex Values('A0001','Vinu','2012-11-10 21:30:11.899');
Insert Into Ex Values('C0023','Karan','2012-11-10 22:45:11.233');
Insert Into Ex Values('A0007','Karthik','2012-11-12 12:00:11.455');
Insert Into Ex Values('D0001','Vikash','2012-11-12 15:15:11.155');
Insert Into Ex Values('B0012','Rahul','2012-11-13 14:56:11.577');
Insert Into Ex Values('A0007','Karthik','2012-11-14 19:25:11.234');
Requirement : To get the previous and next Login times for a particular(or a pattern) Login.
My Attempt : I wrote the query to get previous and next login times for all the EmployeeIds starting with 'C'. ie: 'C0001' and 'C0023'. Following is the Query:
--Query To Get Previous and Next Login Info
;With CTE
As
(
Select *, ROW_NUMBER() Over(Order By LoginTime) As rn From Ex
)
Select a.EmployeeId As CurrentLoggerId, a.EmployeeName As CurrentLoggerName, a.LoginTime As CurrentLogin,
b.EmployeeId As PreviousLoggerId, b.EmployeeName As PreviousLoggerName, b.LoginTime As PreviousLogin,
c.EmployeeId As NextLoggerId, c.EmployeeName As NextLoggerName, c.LoginTime As NextLogin
From CTE As a
LEFT JOIN CTE As b ON b.rn = (a.rn - 1)
LEFT JOIN CTE As c ON c.rn = (a.rn + 1)
Where a.EmployeeId LIKE 'C%'
Order By a.LoginTime
I did this just as an example for learning purpose.
The query works ok and gets the desired resultset.
But, whats bothering me is that if there is a lot of data in the table and since I'm using two self joins with the CTE, would it cause a loss in performance??.......Is there a more efficient and performance friendly way to accomplish what I am trying???
Looking forward to your opinions and a lot of advise on this.
Ideas for doing it using any other query are also welcome. 🙂
November 29, 2012 at 1:11 am
Take a look at the following article "Linking to the previous row", and perhaps more importantly the subsequent discussion which compares CTE with alternatives for this type of problem.
http://qa.sqlservercentral.com/articles/T-SQL/62159/
Regards,
David McKinney.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply