Conditional count - 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 2000 Forums

SQL Server Development (2000)

Conditional count

Author

Topic

anundsson
Starting Member

8 Posts

Posted - 2006-03-01 : 06:30:26

Hi!

I have a table which contains ip(varchar) and date(datetime).
All incoming traffic is inserted into the table.

Is there any way to create a query to count the number of visitors during a specific time interval and only count the same ip a maximum of three times per day?

Any help will be much appreciated.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-01 : 06:55:32

I'm guessing you're after something like this:


select count(*)
from
(select ip, count(*)
from table
where date >= getdate()-1 and date <= getdate()
group by ip
having count(ip) < 4) a

Havent tested it though..

anundsson
Starting Member

8 Posts

Posted - 2006-03-01 : 07:48:24

Thanks for your reply.

Correct me if I'm wrong but doesn't that query ignore ip-numbers with more than three visits that day.

I might have been unclear.
For example:
On day1 ip1 has 4 visits and ip2 has 5 visits.
On day2 ip1 has 3 visits and ip2 has 2 visits.
The query should then return 11, (6 visits for day1 and 5 visits for day2).

Is it possible to solve this using T-SQL?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-01 : 08:02:02

[code]declare
@visit table
(
ip varchar(30),
visit_date datetime
)

insert into @visit
select '192.168.1.1', '2006-03-01 01:00' union all
select '192.168.1.1', '2006-03-01 02:00' union all
select '192.168.1.1', '2006-03-01 03:00' union all
select '192.168.1.1', '2006-03-01 04:00' union all
select '192.168.1.1', '2006-03-01 05:00' union all
select '192.168.1.1', '2006-03-01 06:00' union all
select '192.168.1.2', '2006-03-01 01:00' union all
select '192.168.1.2', '2006-03-01 02:00' union all
select '192.168.1.2', '2006-03-01 03:00' union all
select '192.168.1.2', '2006-03-01 04:00' union all
select '192.168.1.2', '2006-03-01 05:00' union all
select '192.168.1.1', '2006-03-02 10:00' union all
select '192.168.1.1', '2006-03-02 11:00' union all
select '192.168.1.1', '2006-03-02 12:00' union all
select '192.168.1.2', '2006-03-02 20:00' union all
select '192.168.1.2', '2006-03-02 21:00'

select sum(daily_cnt) as total_visits
from
(
select ip, dte, (case when cnt > 3 then 3 else cnt end) as daily_cnt
from
(
select ip, dateadd(day, 0, datediff(day, 0, visit_date)) as dte, count(*) as cnt
from @visit v
where visit_date >= '2006-03-01'
and visit_date < '2006-03-03'
group by ip, dateadd(day, 0, datediff(day, 0, visit_date))
) as d
) as e[/code]

----------------------------------
'KH'

anundsson
Starting Member

8 Posts

Posted - 2006-03-01 : 09:20:16

It works perfectly!
Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources