Breadcrumbs

[Dummy1912]

Hello,

yep its me again blusho;
never knew i needed to ask so much with this private project :lol:

but okay lets start.

i want to count the hours every employee has worked
from the table but it only can count if the month and year are the same

like if we are month March 2014
it has to count all hours in that field for any user that has those months

yes i know i never can explain it :( never been good at that but i do understand it all :lol:

my code:
this will only counts 1
also we use DISTINCT because we only want to show the employee once in the listview.
not all the records from this employee with the same month.

Code: Select all

            SQLcommand.CommandText = "SELECT DISTINCT hours.month,hours.year,hours.timein,hours.timeout FROM hours  ORDER BY hours.srno"

we calculated it to minutes to count it better

Code: Select all

                    If Not Date.TryParse(timein, TimeA) Then
                    End If
                    If Not Date.TryParse(timeout, TimeB) Then
                    End If

                    hh = TimeB.Subtract(TimeA).Hours
                    mm = TimeB.Subtract(TimeA).Minutes
                    Dim timeDiff As String = ((hh.ToString("00") & ":") + mm.ToString("00"))
                    Dim result = CInt(Split(timeDiff, ":")(0)) * 60 + CInt(Split(timeDiff, ":")(1))
                    hours = result

so we have like this image
now we only needs to get all 'result' from all the timein - timeout from the month: march and year: 2014

so the complete result should be : 329
we only get result : 60 :(

thanks

[noypikami]

try something like this: i think this is more accurate to calculate the time intervals
when it comes to seconds, minutes,hours, day or year.

..........to calculate the hours.............

Code: Select all

 Dim startDATE As Date = timein
                    Dim endDATE As Date = timeout
                    Dim Dif As Long = DateDiff(DateInterval.hours, startDATE, endDATE)

..........to calculate the minutes.............

Code: Select all

Dim startDATE As Date = timein
                    Dim endDATE As Date = timeout
                    Dim Dif As Long = DateDiff(DateInterval.minutes,  startDATE, endDATE)

just make sure that the time-in and timeOut is in a valid format for the DATE, and so on..... :|

[Dummy1912]

#noypikami,

thanks but i don't think you understood what i was talking about
this code works fine to calculated the time

but my point was to calculated all data to 1 result
like i showed in the example

so all fields from march and 2010 must be calculated at once in 1 result : 329
because i can only calculate 1 row and i get a result : 60

so it will be like

employee:
test
month:
March
year:
2014
hours:
329 --> all the timein - timeout calculated

employee:
demo
month:
March
year:
2014
hours:
120 --> all the timein - timeout calculated

[TADS]

Just a idea but how about adding a hidden column for the result for each line (hours) then do a
for each line in listview
loop...
then add them all up for each month into a "myresult"

so it is like

month | year | Timein | Timeout | hours

that way you are storing your data that is right then counting them all up after.

[noypikami]

#noypikami,

thanks but i don't think you understood what i was talking about
this code works fine to calculated the time

if that's the case. you have to calculate each row and store them in a list
then sum up all the result from the list to get the final result.

[noypikami]

#Dummy1912 , please take a look on this example ( Project), this might help you. :oops:



Download project:

TimeinOUT.rar

[Dummy1912]

#noypikami,
thanks but the image i added is not a listview or anything
these are the records from my database table

so i need to sum it all at once when we loading the data before we adding them in the listview.

#TADS,
yea sorry i can't do that because none of the data is stored in the listview before i get the complete sum of each employee

as you can see on this listview after been sum it
it add them in the listview
but you can see we only get 60 instead of 329 :?

[noypikami]

#noypikami,
thanks but the image i added is not a listview or anything
these are the records from my database table
so i need to sum it all at once when we loading the data before we adding them in the listview.

Now i'm confused... :lol: :lol: :lol:

[Dummy1912]

#noypikami i updated the post
and sorry for making you confused ;)

[noypikami]

#noypikami i updated the post
and sorry for making you confused ;)

It's okay....... dont bother to say sorry. :lol: