-
Aug 12th, 2013, 10:07 AM
#1
Thread Starter
Junior Member
[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.
-
Aug 12th, 2013, 01:46 PM
#2
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.
-
Aug 12th, 2013, 02:15 PM
#3
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
-
Aug 12th, 2013, 02:35 PM
#4
Re: Count login for consecutive days.
Originally Posted by ColinE66
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...
-
Aug 12th, 2013, 02:45 PM
#5
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
-
Aug 12th, 2013, 04:29 PM
#6
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
-
Aug 12th, 2013, 04:43 PM
#7
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
-
Aug 12th, 2013, 06:14 PM
#8
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....
-
Aug 13th, 2013, 02:55 AM
#9
Thread Starter
Junior Member
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.
-
Aug 13th, 2013, 05:54 AM
#10
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.
-
Aug 13th, 2013, 09:44 AM
#11
Re: Count login for consecutive days.
Originally Posted by prmaodsd
... 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
-
Aug 13th, 2013, 10:19 AM
#12
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
-
Aug 14th, 2013, 03:07 AM
#13
Thread Starter
Junior Member
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.
-
Aug 14th, 2013, 08:17 AM
#14
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
-
Aug 14th, 2013, 09:22 AM
#15
Re: Count login for consecutive days.
Originally Posted by prmaodsd
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
-
Aug 15th, 2013, 07:05 AM
#16
Thread Starter
Junior Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|