Friday, February 24, 2012

Logic problem - a challenge if you will

This is killing, me and I think that I'm failing to see something simple here:

If I have a table with logins and datetimes. I need to output any logins that have logged in more than 3 times in any 3 hour period of time, and how may times it was done. For example:

Login table:
user1 01:00
user2 01:13
user1 02:32
user2 01:17
user1 01:12
user2 07:00
user1 04:10

I would need:
user1 2 <-- (times user 1 logged in more than 3 times in 3 hours)

Because:
01:00, 02:32, 01:12 are all within 3 hours of each other
02:32, 01:12, 04:10 are all within 3 hours of each other

Obviously I have alot more data than this, but I'm failing to grasp the logic properly. Trying to do this in a Sybase stored proc.create table #tmp (
login char(5),
log_time smalldatetime
)

insert into #tmp
select 'user1', '01:00'
union all
select 'user2', '01:13'
union all
select 'user1', '02:32'
union all
select 'user2', '01:17'
union all
select 'user1', '01:12'
union all
select 'user2', '07:00'
union all
select 'user1', '04:10'

select rs1.login, count(*) as cnt
from (
select #tmp.Login
from #tmp inner join (
select login, log_time as mintime, dateadd(hh,3,log_time) as maxtime from #tmp) rs
on #tmp.login=rs.login
where #tmp.log_time between rs.mintime and rs.maxtime
group by #tmp.login, rs.mintime, rs.maxtime
having count(#tmp.log_time)>=3) rs1
group by rs1.login

drop table #tmp|||This would be assuming a limited data set, though, correct? Suppose I do not know how many logins and times there are?|||This would be assuming a limited data set, though, correct? Why are you thinking that? Did you try the query?

No comments:

Post a Comment