Comparing Data in Two Consecutive Rows in SQL Server

Question

Suppose my database table is looks like below table,

Id               Name    Status

2012-07-16    A       Login
2012-07-16    A       Logout
2012-07-16 A       Login
2012-07-16    A        Login
2012-07-16 A        Logout

In the above table, Two consecutive rows have the same name login then only last row should be display in the output table. AND I want a output like that:

Date            Name     Status

2012-07-16    A         Login
2012-07-16    A         Logout
2012-07-16    A          Login
2012-07-16 A          Logout

How can I do this? Please help me.

Santhosh Kumar Jayaraman · Answer

try this. ;WITH CTE_TEST AS ( SELECT Date1 Name[Status]  ROW_NUMBER() OVER (PARTITION BY Date1 Name ORDER BY Date1) AS row_num FROM templogin ) SELECT distinct previous.Date1previous.Name previous.[Status] previous.row_num FROM CTE_TEST AS [current] LEFT OUTER JOIN CTE_TEST AS previous ON [current].Date1 = previous.Date1 AND [current].Name = previous.Name AND ([current].row_num = previous.row_num + 1 or previous.row_num=(select MAX(row_num) from CTE_TEST)) where [current].[Status] previous.[Status] ORDER BY previous.row_num SELECT distinct [current].Date1[current].Name [current].[Status] [current].row_num FROM CTE_TEST AS [current] LEFT OUTER JOIN CTE_TEST AS previous ON [current].Date1 = previous.Date1 AND [current].Name = previous.Name AND ([current].row_num = previous.row_num + 1 or [current].row_num=1) where [current].[Status] previous.[Status] ORDER BY [current].row_num Here is my sample table and records. use santhosh create table templogin (Date1 Datetime Name varchar(50) Status varchar(50)) insert into templogin values( '2012-07-16' 'A'  'Login') insert into templogin values( '2012-07-16' 'A'  'Logout') insert into templogin values( '2012-07-16' 'A'  'Login') insert into templogin values( '2012-07-16' 'A'  'Login') insert into templogin values( '2012-07-16' 'A'  'Logout') I have written that query for this table structure.

Santhosh Kumar Jayaraman · Answer

I didnt get your question Can you please elaborate

Radha Reddy · Answer

Hi Sir How can you use two select statements for single CTE. when we execute second select statement it says CTE is invalid object.

Sukesh Marla · Answer

DataTable Common=objCustomers2.clone(); for(i=0;i { if(i!=(objCustomers2.Rows.Count-1)) { if(objCustomers2.Rows[i][col1].ToString()==objCustomers2.Rows[i+1][col1].ToString() && objCustomers2.Rows[i][col2].ToString()==objCustomers2.Rows[i+1][col2].ToString() ) { } else { DataRow DrTemp=Common.NewRow(); DrTemp[Col1]=objCustomers2.Rows[i][col1]; DrTemp[Col2]=objCustomers2.Rows[i][col2]; common.rows.add(DrTemp); } } } Check this is correct answer if it helped

Rohatash Kumar · Answer

Thanks for your reply sir