Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Unanswered: 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.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    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
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jul 2007
    Posts
    2
    This would be assuming a limited data set, though, correct? Suppose I do not know how many logins and times there are?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rampantnine
    This would be assuming a limited data set, though, correct?
    Why are you thinking that? Did you try the query?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •