Putting data from multiple rows into one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Putting data from multiple rows into one

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-03-26 : 01:27:00

Table called eventdetails as follows:

EventID Time EventType SiteID
1 2009-03-18 15:00:00 A 1
2 2009-03-18 15:00:01 B 1
3 2009-03-18 15:00:02 B 2
4 2009-03-18 15:00:03 SiteAck 2
5 2009-03-18 15:00:04 C 2
6 2009-03-18 15:00:05 C 1
7 2009-03-18 15:00:06 SiteAck 1
8 2009-03-18 15:00:07 SiteAck 2
9 2009-03-18 15:00:08 A 3
10 2009-03-18 15:00:09 D 1
11 2009-03-18 15:00:15 SiteAck 1

Create Table #e ([EventID] int, [Time] DateTime, [EventType] nvarchar (50), [SiteID] int)

Insert into #e ([EventID], [Time], [EventType], [SiteID])
Select '1', '2009-03-18 15:00:00', 'A', '1'
Union All
Select '2', '2009-03-18 15:00:01', 'B', '1'
Union All
Select '3', '2009-03-18 15:00:02', 'B', '2'
Union All
Select '4', '2009-03-18 15:00:03', 'SiteAck', '2'
Union All
Select '5', '2009-03-18 15:00:04', 'C', '2'
Union All
Select '6', '2009-03-18 15:00:05', 'C', '1'
Union All
Select '7', '2009-03-18 15:00:06', 'SiteAck', '1'
Union All
Select '8', '2009-03-18 15:00:07', 'SiteAck', '2'
Union All
Select '9', '2009-03-18 15:00:08', 'A', '3'
Union All
Select '10', '2009-03-18 15:00:09', 'D', '1'
Union All
Select '11', '2009-03-18 15:00:15', 'SiteAck', '1'

Select * from #e

Drop Table #e

What I need is the response time in seconds between each set for first event and the next siteack event for each site. Basically first event datediff with the first siteack for the respective site. Next set is first event after the first siteack with second siteack for the respective site. Process repeats, events from different sites are interleaved.

Output

EventTime SiteAckTime SiteID ResponseTime
2009-03-18 15:00:00 2009-03-18 15:00:06 1 6
2009-03-18 15:00:02 2009-03-18 15:00:03 2 1
2009-03-18 15:00:04 2009-03-18 15:00:07 2 3
2009-03-18 15:00:09 2009-03-18 15:00:15 1 6

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-03-26 : 03:33:24

I dont know whether this is the best solution but I am getting the desired result.

Declare @tbl table(eventid int,time datetime,eventtype varchar(30),siteid int)
insert into @tbl
select 1, '2009-03-18 15:00:00', 'A', 1 union all
select 2, '2009-03-18 15:00:01' ,'B', 1 union all
select 3, '2009-03-18 15:00:02' ,'B' ,2 union all
select 4, '2009-03-18 15:00:03' ,'SiteAck', 2 union all
select 5, '2009-03-18 15:00:04' ,'C', 2 union all
select 6, '2009-03-18 15:00:05' ,'C', 1 union all
select 7, '2009-03-18 15:00:06' ,'SiteAck', 1 union all
select 8, '2009-03-18 15:00:07' ,'SiteAck', 2 union all
select 9, '2009-03-18 15:00:08' ,'A', 3 union all
select 10, '2009-03-18 15:00:09' ,'D',1 union all
select 11, '2009-03-18 15:00:15' ,'SiteAck', 1

select
t1.eventid ,
t1.siteid,
t2.time as Eventtime,
t1.time as SiteAckTime,
t1.eventtype,
ROW_NUMBER() over(partition by t1.siteid order by t1.siteid)as rowid
into #temp from @tbl t1
cross apply
(select eventid,siteid,time,eventtype from @tbl)t2
where t1.eventtype='siteAck' and t2.eventtype<>'siteAck'
and t1.siteid=t2.siteid

Select Eventtime,SiteAckTime,t1.siteid,DATEDIFF(SECOND,Eventtime,SiteAckTime)as ResponseTime from
(
select MAX(rowid)as rowid,siteid
from #temp
group by siteid

union

select min(rowid)rowid,siteid
from #temp
group by siteid
)t1
inner join
#temp t2 on t1.rowid=t2.rowid and t1.siteid=t2.siteid

drop table #temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources