Merging multiple rows - SQL Server Forums

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)

Merging multiple rows

Author

Topic

Sachin.Nand

2937 Posts

Posted - 2009-11-10 : 01:46:40

[code]
declare @tbl as table(typ int,enddt datetime,err varchar(50) )
insert into @tbl
select 1,'2009-08-12 08:01:00.000',null union all
select 2,'2009-08-12 08:02:00.000',null union all
select 3,'2009-08-12 08:04:00.000',null union all
select 4,'2009-08-12 08:14:00.000',null union all
select 5,'2009-08-12 08:25:00.000',null union all
select 6,'2009-08-12 08:12:00.000',null union all
select 7,'2009-08-12 08:20:00.000',null

select * from @tbl
[/code]

I want to merge the records that are in difference of 5 mins of each other.Output shud be
[code]
1,'2009-08-12 08:01:00.000','transaction 1,2,3 merged'
5,'2009-08-12 08:25:00.000',null
6,'2009-08-12 08:12:00.000','transaction 4,6 merged'
7,'2009-08-12 08:20:00.000',null
[/code]

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-11 : 12:07:30

why u selected min id (1) in case of merging 1, 2 & 3 and max id(6) in case of merging 4 & 6?

Sachin.Nand

2937 Posts

Posted - 2009-11-12 : 01:26:33

No it has nothing to do with id.I selected the min date for the records that need to be merged.

PBUH

weisseb
Starting Member

8 Posts

Posted - 2009-11-16 : 06:19:58

You could try something like


select
   convert(datetime, convert(varchar(14), enddt, 120) +
      replace(str((datepart(minute, enddt) / 5) * 5, 2), ' ', '0'), 120),
   min(enddt),
   max(enddt),
   count(*),
   ...
from @tbl
group by
   convert(datetime, convert(varchar(14), enddt, 120) +
      replace(str((datepart(minute, enddt) / 5) * 5, 2), ' ', '0'), 120)

This creates a datetime suffix without minutes, e.g. "2009-08-12 08:" and concatenates minutes as 00, 05, 10, ..., 55, calculated using integer division (and subsequently multiplication) on the minutes part of the datetime. Finally the result is converted back to a datetime data type.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources