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?
Showing posts with label challenge. Show all posts
Showing posts with label challenge. Show all posts
Friday, February 24, 2012
Subscribe to:
Posts (Atom)