How to combine a multiple rows in Sql 2005?

Richa Garg

14y1.6k

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Santhosh Kumar Jayaraman14y ago
Accepted 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 Jayaraman14y ago
welcome.

Can you please mark it as solved?
Richa Garg14y ago
Thanx Santhosh Its work properly and really very useful..........
Richa Garg14y ago
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 Jayaraman14y ago
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)