Results 1 to 16 of 16

Thread: [RESOLVED] Count login for consecutive days.

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2013
    Posts
    25

    Resolved [RESOLVED] Count login for consecutive days.

    Hello,

    I am need of a little help.

    I am currently working on an application that records time when someone logs in and logs out of the system.

    I have the data of login and logout going into an access database through VB6.

    My database table has columns like Username,SystemDate,LoginTime,LogoutTime,TotalHours.

    Now the problem I have is, I want to insert a code that will check the database for login on consecutive days(past 5 logins should be checked).

    If the last 5 logins turn out to be on consecutive days then I have to consider the current login as overtime.

    Please let me know if there are any built in functions that I can use for the same.

    Note: The consecutive days should be any 5 consecutive days in the month. Not necessarily in a single week.

    Any help on this would be greatly appreciated.

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: Count login for consecutive days.

    Get the last (latest) login date. Do a for-loop and check if previous login is one day earlier. If so, keep looping until you have reached the date equalling 5 days earlier, checking each time for the DAY value of the date. Of course, a user might log in more than once in a day, so if another login is found on the same date, ignore that one, and continue the loop.

    You can do it with simple math (subtracting 1 from the Date, or use datediff(). Lotta ways to do this, but that should be thelogic.

  3. #3
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,253

    Re: Count login for consecutive days.

    You should be able to do this with just a single SQL query - no need for looping through recordsets...

    Something like:
    SELECT userid, count(1) AS num_of_logins FROM Table
    WHERE
    login_date >= todays_date* -5
    GROUP BY userid
    HAVING num_of_logins =5

    *whatever Access uses for the current date
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  4. #4
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: Count login for consecutive days.

    Quote Originally Posted by ColinE66 View Post
    You should be able to do this with just a single SQL query - no need for looping through recordsets...

    Something like:
    SELECT userid, count(1) AS num_of_logins FROM Table
    WHERE
    login_date >= todays_date* -5
    GROUP BY userid
    HAVING num_of_logins =5

    *whatever Access uses for the current date
    Can't test (on iPad)...but does this take into account multiple logins on any given day? Looks like it will count ALL logins in last 5 days...can't really tell...

  5. #5
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,253

    Re: Count login for consecutive days.

    To cater for multiple logins on the same day, you would need to modify the SQL a little. Before I do that, though, the OP should let us know if this is a valid scenario.
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  6. #6
    PowerPoster Spoo's Avatar
    Join Date
    Nov 2008
    Location
    Right Coast
    Posts
    2,656

    Re: Count login for consecutive days.

    Prmaodsd

    Question ..
    .. do you mean literally "consecutive" days.
    .. or, do you want to exclude Saturdays and Sundays .. ie, if it is a 5-day work week situation.

    If the latter, then your DateDiff (or similar) algo will need to be a little "smarter".

    Spoo

  7. #7
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,253

    Re: Count login for consecutive days.

    Indeed. From the outset I sensed that there was more information needed to resolve this. For instance, it seems possible (at face value) for somebody to get overtime if they login for 1 minute per day for 5 straight days! Hence, my initial answer was vased on the assumption that it is a once-per-day, clock-in, clock-out application.
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  8. #8
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: Count login for consecutive days.

    I realized, as well, we don't have all the info needed to complete OPs project....hence, I just suggested the basic logic, and a simple method of checking 5-day's worth of logins.....(if I were on my computer, vice PC, coulda jinned (sp?) up something with a coupla options), but being on an iPad, I couldn't check anything I wrote....

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Jul 2013
    Posts
    25

    Re: Count login for consecutive days.

    Hello All,

    Thanks for the replies.

    I have two buttons "Login" and "Logout" for the User.
    I already have code in place to make sure that the time diff between login and logout is atleast 5 hours.

    In the same code, I also have a clause which makes sure that there is only one login permitted per day. No multiple logins will be recorded.

    Also, it will be for 5 consecutive days (i.e., without considering Saturday and Sunday as fixed weekly offs).
    So, I would also have to include them while counting the number of logins backwards.

  10. #10
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: Count login for consecutive days.

    So, can you not do what I posted in #2? You seem to have a grasp of sql, so this should be easy; or do what Colin suggests.

  11. #11
    PowerPoster Spoo's Avatar
    Join Date
    Nov 2008
    Location
    Right Coast
    Posts
    2,656

    Re: Count login for consecutive days.

    Quote Originally Posted by prmaodsd View Post
    ... Also, it will be for 5 consecutive days (i.e., without considering Saturday and Sunday as fixed weekly offs).
    So, I would also have to include them while counting the number of logins backwards.
    Prmaodsd

    This is still a little confusing to me, but I think you mean
    that in this sceario ...

    Code:
    Wed .. logged in
    Thu .. logged in
    Fri .. logged in
    Sat .. ignore
    Sun .. ignore
    Mon .. logged in
    Tue .. logged in
    .. you would count it as 5 consecutive days.
    Am I correct?

    Spoo

  12. #12
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Count login for consecutive days.

    Personally I think I would add another field for consecutive logins to the user table and then when a user logs in check the last login date for that user and if it was the same day leave the logins unchanged. If it was the previous weekday then increment the number by 1 and if more than 1 weekday set the count to 1.

    You could then easily tell how many days they had in a row without checking more than 1 record

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Jul 2013
    Posts
    25

    Re: Count login for consecutive days.

    SamOscarBrown,

    I had tried with a for loop before posting this thread and it does not seem to respond with the required results(Maybe I got the loop incorrect).

    I will try that again and let you know.

    Spoo,

    Thats incorrect.

    We will consider it as 5 consecutive logins only if there is no gap between the logins whatsoever.

    In the example given by you, it should not consider that as consecutive days.


    DataMiser,

    I think your solution might just work well for me.

    I will try and let you know.

    Thanks everyone for your inputs. Greatly appreciated.

  14. #14
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: Count login for consecutive days.

    I am sure DM's method will surely work, but here is a small example using a for-loop and an invisible listbox:
    Code:
    Dim rs As Recordset
    dbConnect 'my connection to my database
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn
    cmd.CommandText = "select loginTime from loginTable where username = " & "'" & Text3.Text & "' order by logintime"
    Set rs = cmd.Execute
    Dim x As Integer, y As Integer, iCnt As Integer
    
    Do While Not rs.EOF
    List2.AddItem (Day(rs!loginTime))   'could use an array instead of an invisible listbox as I did
    rs.MoveNext
    Loop
    For x = List2.ListCount - 1 To 0 Step -1
         If x > 0 Then
             y = x - 1
         End If
         List2.ListIndex = x
         If List2.Text = List2.List(y) + 1 Then
             iCnt = iCnt + 1
         Else
             iCnt = iCnt
         End If
    Next x
    If iCnt = 4 Then  'found five consecutive days
        MsgBox "Overtime is needed"
    End If

  15. #15
    PowerPoster Spoo's Avatar
    Join Date
    Nov 2008
    Location
    Right Coast
    Posts
    2,656

    Re: Count login for consecutive days.

    Quote Originally Posted by prmaodsd View Post
    Spoo,

    Thats incorrect.

    We will consider it as 5 consecutive logins only if there is no gap between the logins whatsoever.

    In the example given by you, it should not consider that as consecutive days.
    OK. Thanks for the clarification.

    Spoo

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Jul 2013
    Posts
    25

    Re: Count login for consecutive days.

    Hello All,

    Thank you for all your replies.

    I was able to do it with a nested if as below.

    ============================
    Code
    ============================

    counter = 0
    rs.MovePrevious
    If Date - CDate(rs.Fields(17)) = 1 Then
    counter = counter + 1
    rs.MovePrevious
    If Date - CDate(rs.Fields(17)) = 2 Then
    counter = counter + 1
    rs.MovePrevious
    If Date - CDate(rs.Fields(17)) = 3 Then
    counter = counter + 1
    rs.MovePrevious
    If Date - CDate(rs.Fields(17)) = 4 Then
    counter = counter + 1
    rs.MovePrevious
    If Date - CDate(rs.Fields(17)) = 5 Then
    counter = counter + 1
    End If
    End If
    End If
    End If
    End If
    If counter = 5 Then
    MsgBox "Overtime Calculated"

    ============================
    End Of Code
    ============================

Posting Permissions

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



Click Here to Expand Forum to Full Width