filter based on same time - 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)

filter based on same time

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-09-07 : 04:35:05

Hi

I have a table that have a column DateTime called "DateAdded", I would like to find all rows that is almost in the same time span. For example, if there is 4 rows like this..

ID Name DateAdded
1 Bob 2010-12-07 12:12:02.963
2 Bob 2010-12-07 12:12:45.523
3 Jenifer 2010-12-07 12:12:34.523
4 Lukas 2010-12-07 12:13:02.973

I would like to filter out row 1 and 2 beacuse they are added whitin one minute and also added by the same user. So there have to be at least 2 rows placed by the same user and within 1 minute.


CREATE TABLE [dbo].[tbl_Test](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](255) NULL,
	[DateAdded] [datetime] NULL
	)
	
INSERT INTO tbl_Test (Name, DateAdded) VALUES ('Bob', '2010-12-07 12:12:02.963') 
INSERT INTO tbl_Test (Name, DateAdded) VALUES ('Bob', '2010-12-07 12:12:45.523') 
INSERT INTO tbl_Test (Name, DateAdded) VALUES ('Jenifer', '2010-12-07 12:12:34.523') 
INSERT INTO tbl_Test (Name, DateAdded) VALUES ('Lukas', '2010-12-07 12:13:02.973')

Can this be done?

Best Regards

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-09-07 : 05:01:54

One way:

;with cte
as (
select row_number() Over (Partition by Name order by DateAdded) as srno
,* from tbl_Test
)

Select t1.* from cte t1
inner join cte t2 on t1.srno + 1 = t2.srno
and t1.Name = t2.name
and datediff( Mi, t1.Dateadded , t2.Dateadded) <=1
union all
Select t2.* from cte t1
inner join cte t2 on t1.srno + 1 = t2.srno
and t1.Name = t2.name
and datediff( Mi, t1.Dateadded , t2.Dateadded) <=1

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-09-07 : 05:08:08

Hi

Thanks, but I forgot to say that it needs to be the same date and time but within one minute. If I add another row for Bob with the same time but different date I get to many results.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-07 : 05:09:37

[code]
select MAX(ID) AS ID,Name,MAX(DateAdded) AS DateAdded
from table
group by Name,dateadd(minute,datediff(minute,0,DateAdded),0)

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-07 : 05:23:46

select distinct a.* from #tbl_Test a
cross apply
(
select b.* from #tbl_Test b where a.Name = b.Name and
DATEDIFF(ss,a.DateAdded,b.DateAdded) between 1 and 60
) c

Karthik
http://karthik4identity.blogspot.com/

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-09-07 : 05:27:31

Sorry, this does not give the correct result either. This display singel rows too. The filter need to find all rows that are the same name and also within the same date and time and added within one minute.

quote:
Originally posted by visakh16

select MAX(ID) AS ID,Name,MAX(DateAdded) AS DateAdded
from table
group by Name,dateadd(minute,datediff(minute,0,DateAdded),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-09-07 : 05:35:04

quote:
Originally posted by magmo

Hi

Thanks, but I forgot to say that it needs to be the same date and time but within one minute. If I add another row for Bob with the same time but different date I get to many results.

I added two more records to your sample data but still I see only 2 records..

create TABLE [dbo].[tbl_Test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](255) NULL,
[DateAdded] [datetime] NULL
)

INSERT INTO tbl_Test (Name, DateAdded) VALUES ('Bob', '2010-12-07 12:12:02.963')
INSERT INTO tbl_Test (Name, DateAdded) VALUES ('Bob', '2010-12-07 12:12:45.523')
INSERT INTO tbl_Test (Name, DateAdded) VALUES ('Bob', '2010-12-07 12:14:45.523')
INSERT INTO tbl_Test (Name, DateAdded) VALUES ('Bob', '2010-12-08 12:12:45.523')
INSERT INTO tbl_Test (Name, DateAdded) VALUES ('Jenifer', '2010-12-07 12:12:34.523')
INSERT INTO tbl_Test (Name, DateAdded) VALUES ('Jenifer', '2010-12-07 12:14:34.523')
INSERT INTO tbl_Test (Name, DateAdded) VALUES ('Lukas', '2010-12-07 12:13:02.973')

;with cte
as (
select row_number() Over (Partition by Name order by DateAdded) as srno
,* from tbl_Test
)

Select t1.* from cte t1
inner join cte t2 on t1.srno + 1 = t2.srno
and t1.Name = t2.name
and datediff( Mi, t1.Dateadded , t2.Dateadded) <=1
union all
Select t2.* from cte t1
inner join cte t2 on t1.srno + 1 = t2.srno
and t1.Name = t2.name
and datediff( Mi, t1.Dateadded , t2.Dateadded) <=1

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-09-07 : 05:36:42

Also it will be better if you post your expected output based on your sample data (with all the columns that you need)

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-07 : 05:46:35

quote:
Originally posted by karthik_padbanaban

select distinct a.* from #tbl_Test a
cross apply
(
select b.* from #tbl_Test b where a.Name = b.Name and
DATEDIFF(ss,a.DateAdded,b.DateAdded) between 1 and 60
) c

Karthik
http://karthik4identity.blogspot.com/

did you try this.?

Karthik
http://karthik4identity.blogspot.com/

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-09-07 : 06:10:08

Thanks, that worked great!

quote:
Originally posted by karthik_padbanaban

quote:
Originally posted by karthik_padbanaban

select distinct a.* from #tbl_Test a
cross apply
(
select b.* from #tbl_Test b where a.Name = b.Name and
DATEDIFF(ss,a.DateAdded,b.DateAdded) between 1 and 60
) c

Karthik
http://karthik4identity.blogspot.com/

did you try this.?

Karthik
http://karthik4identity.blogspot.com/

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-07 : 06:14:15

welcome

Karthik
http://karthik4identity.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources