regarding repetation check - 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

General SQL Server Forums

New to SQL Server Programming

regarding repetation check

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-07-18 : 08:25:52

Hi,

I am having table

Name,IP, Date(datetime),address having requirement to check the 3 times and more repeation count of IP in last 3 months.

so i formed a query like

select IP,count(*) from t1
DATEADD(dd, 0, DATEDIFF(dd, 0, Date))>=
DATEADD(month, DATEDIFF(month, 0, getdate())-3, 0)
GROUP BY IP
HAVING COUNT(*)>=3

by this query i able to see only the Ip and its duplication count,
i need the result set with all the columns, with the IP count >3
how to get it?

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-07-18 : 08:49:00

--Can you give your table structure with sample data? Then only we can give exact result to you.

--Any Way In my assumption i think structure will be like below
CREATE TABLE t1 (ID INT PRIMARY KEY, IP VARCHAR(50), NAME VARCHAR(50), ADDRESS VARCHAR(50))
Go
-- Insert Into T1(.....) Values (....)
-- Some Values inserted in the table T1
Go
-- One way to show all the columns.
select IP, Name, Address, count(*) AS Dublicate from t1
DATEADD(dd, 0, DATEDIFF(dd, 0, Date))>=
DATEADD(month, DATEDIFF(month, 0, getdate())-3, 0)
GROUP BY IP, Name, Address
HAVING COUNT(*)>=3

--Or else we can solve IN another way.

select DISTINCT IP, count(*)
INTO TempTable1
from t1
DATEADD(dd, 0, DATEDIFF(dd, 0, Date))>=
DATEADD(month, DATEDIFF(month, 0, getdate())-3, 0)
GROUP BY IP
HAVING COUNT(*)>=3

SELECT
TempTable1.IP, t1.Name, t1.Address, TempTable1.Dublicate
from t1
INNER JOIN TempTable1 ON t1.IP = TempTable1.IP

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-07-18 : 09:13:40

ok
my table structure is

CREATE TABLE t1 (IP VARCHAR(50), NAME VARCHAR(50), ADDRESS VARCHAR(50), Date Datetime)

insert t1

select '10.10.1.2','name1','address1','2012-06-16 18:55:22.050'
union all
select '10.10.1.2','name2','address2','2012-06-15 12:52:10.070'
union all
select '10.10.1.2','name3','address2','2012-06-10 12:52:14.070'
union all
select '10.10.2.4','name5','address1','2012-06-10 14:11:22.024'
union all
select '10.10.2.4','name3','address1','2012-06-10 10:11:22.024'
union all
select '10.10.2.4','name6','address7','2012-06-10 21:11:22.024'
union all
select '10.10.2.4','name2','address4','2012-06-10 14:11:22.024'
union all
select '10.10.2.5','name1','address2','2012-06-10 22:11:22.024'
union all
select '10.10.2.5','name3','address1','2012-06-10 14:11:22.024'

i am expecting result like

'10.10.1.2','name1','address1','2012-06-16 18:55:22.050',3
'10.10.1.2','name2','address2','2012-06-15 12:52:10.070',3
'10.10.1.2','name3','address2','2012-06-10 12:52:14.070',3
'10.10.2.4','name5','address1','2012-06-10 14:11:22.024',4
'10.10.2.4','name3','address1','2012-06-10 10:11:22.024',4
'10.10.2.4','name6','address7','2012-06-10 21:11:22.024',4
'10.10.2.4','name2','address4','2012-06-10 14:11:22.024',4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 09:51:29

[code]
select t.*
from yourtable t
cross apply (select count(1) as cnt
from yourtable
where ip = t.ip
and [date] >= dateadd(mm,datediff(mm,0,getdate())-2,0)
and [date] < dateadd(mm,datediff(mm,0,getdate())+1,0)
)t1
where t1.cnt > =3
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources