[RESOLVED] time calculation

[met0555]

Hi,

i'm looking for a solution that will let me calculate the total working hour of a user. I currently have the LOGGED IN time and the LOGGED OUT time of each user in a datetime data type column . I was wondering if I can calculate the total working time of each user with the current data i have and how?

thanks

[IanRyder]

Hi,

In a Database environment you would typically use the DateDiff function. Have a look here:-

SQL Server DATEDIFF() Function

In Visual Studio you would use a TimeSpan structure. Have a look here:-

TimeSpan Structure

Hope that helps.

Cheers,

Ian

[met0555]

Hi,

My idea was to set column B of data type datetime to 0000-00-00 00:00, and each time the user SIGNS OUT, will use VB to do the difference between the SIGN IN time and SIGN OUT time and save it under column B, and to get the total of working hours I will just sum column B, but unfortunately it looks like it's impossible.

I tried to use the DATEDIFF() function. to sum up all signed in date and time and signed out date and time then do the difference. But the result is always 0 bcz the start and end time is the same. I don't know how to make it sum all lines under the datee coulmn

SELECT SUM(DATEDIFF(second, datee, datee)) AS Diffminute
FROM     timestamp

where SIGNED='IN'

Please let me know if there is a better to get the total working time.

thanks

[IanRyder]

Hi,

As your SQL stands now you are always going to get Zero since you are using the same column in the DataDiff function for the start time and end time.

On the basis that you have a Where clause to select all records where the Signed column equals "IN", would I be right in assuming that your Table only has 1 DateTime Column and you have an additional field called Signed which identifies whether each particular DataTime value represents a Signs In or Signs Out value?

Cheers,

Ian

[met0555]

Yes, that is correct

[IanRyder]

Hi,

Ok. This type of setup always makes things a bit more awkward.

Next question then is:- How do you identify the associated Signs Out Record with the Initial Signs In record? It is crucial to identify this so that you can link together your Signs In and Signs Out records to make the necessary calculation with the DateDiff function.

Cheers,

Ian

[met0555]

Hi,

I don't have any other field to track the relationship between IN and OUT but the records are always in this format

SIGNED IN
SIGNED OUT
SIGNED IN
SIGNED OUT
SIGNED IN
SIGNED OUT

So SIGNED IN #1 is always related with SIGNED OUT #1 ...

Thanks

[IanRyder]

Hi,

Sorry, but I am not prepared to help any further on this thread. My reason for this is as follows:-

1) The original question of the Thread has been answered.

2) Your issue is now a Database related issue. Since your relationship between the Signs In and Signs Out Times rely purely on the order in which they appear in the DataTable then I am not comfortable in suggesting any SQL code to relate your records since there is no way to guarantee that the First Signs In record and the First Signs Out record belong to the same user.

I would suggest creating a new Thread in the Database Forum to help you solve this problem.

Cheers,

Ian

[met0555]

ok, Thanks