How to combine a multiple rows in Sql 2005?

Question

Suppose my database is looks like,

id       name                    date                                              status
1          A           2012-07-16 13:54:02.000                          Login
1          A           2012-07-16 13:54:35.000                          Logout
1        A            2012-07-16 14:21:22.000                          login
1        A             2012-07-16 14:21:36.000                         logout
2        B              2012-07-16 14:21:53.000                        LOGIN
2          B             2012-07-16 14:41:54.000                        LOGOUT

AND I want a output like

id           name          date                         login_period
1             A               2012-07-16               (differnece b/w the login and logout time total)
2             B               2012-07-16                (difference b'w the login and logout time)

How can I do this .Please help me out.

Santhosh Kumar Jayaraman · Answer

cte_test is my common table expression name.. I guess you know about common table expression concept,else please let me know i will help u after inserting try executing only this part. with cte_test as (select [id],[date],[status],ROW_NUMBER() over (partition by [id],[status] order by [id],[status]) as rn from emptest) select a.ID,convert(char(10), a.[date], 111),convert(varchar(8),dateadd(s, sum(datepart(hour, b.[date]-a.[date]) * 3600) + sum(datepart(minute, b.[date]-a.[date]) * 60) + sum(datepart(second, b.[date]-a.[date])),0),108) from cte_test a inner join cte_test b on a.id=b.id and convert(char(10), a.[date], 111)=convert(char(10), b.[date], 111) and a.rn=b.rn and a.status='Login' and b.status='Logout' group by a.Id,convert(char(10), a.[date], 111)

Santhosh Kumar Jayaraman · Answer

welcome. Can you please mark it as solved?

Richa Garg · Answer

Thanx Santhosh Its work properly and really very useful..........

Richa Garg · Answer

Thanx Santhosh for giving me this example, But I face a problem in this ,,,can you tell me what is cte_test in this.The line ' with cte_test as' gives error,What is it and what its uses?

Santhosh Kumar Jayaraman · Answer

Hi Richa, I guess this might help you.. I have written for my own table similar to yours. create TABLE [dbo].[EMptest]( id int , date datetime NULL, status varchar(50) NULL ) insert into EMptest values(1,'2012-07-16 13:54:02.000','Login') insert into EMptest values(1,'2012-07-16 13:54:35.000','Logout') insert into EMptest values(1,'2012-07-16 14:21:22.000','Login') insert into EMptest values(1,'2012-07-16 14:21:36.000','Logout') insert into EMptest values(2,'2012-07-16 14:21:53.000','Login') insert into EMptest values(2,'2012-07-16 14:41:54.000','Logout') insert into EMptest values(2,'2012-07-17 14:21:53.000','Login') insert into EMptest values(2,'2012-07-17 14:41:54.000','Logout') with cte_test as (select [id],[date],[status],ROW_NUMBER() over (partition by [id],[status] order by [id],[status]) as rn from emptest) select a.ID,convert(char(10), a.[date], 111),convert(varchar(8),dateadd(s, sum(datepart(hour, b.[date]-a.[date]) * 3600) + sum(datepart(minute, b.[date]-a.[date]) * 60) + sum(datepart(second, b.[date]-a.[date])),0),108) from cte_test a inner join cte_test b on a.id=b.id and convert(char(10), a.[date], 111)=convert(char(10), b.[date], 111) and a.rn=b.rn and a.status='Login' and b.status='Logout' group by a.Id,convert(char(10), a.[date], 111)