Ad:

ColdFusion Discussion Thread
Unsolved
Views: 6056

Nov 1st, 2006

finding duration worked between time ranges from login and logout time

Expand Post »

Hi Folks
I am in a difficult situation right now. Please help me.
There are several stores and several employees.
employees can work in multiple stores and stores have multiple employees
the time they work are calculated based on the time they log in and log out.
for this purpose there is a logins table which has following fields.
emp_id, store_id, login_time, logout_time
emp_id stores the employee id
store_id stores the store id
login_time will have the time of logging in by the employee (datetime)
logout_time will have the time of logging out by the employee (datetime)
there is a manager for every store. he/she will be concerned with the store.
what I need is a way to find out the total working time in minutes for a store
within a particular date range
for eg. from 11/1/06 7:30 am to 11/5/06 6:pm for store id 7
i want the system to return something like
employee_id time
-------- -----
45 780 minutes
34 327 minutes

-Ramesh

Similar Threads

Strange time complexity problems... help! in Computer Science
impose a limitation on form of time duration in PHP
Windows Search Assistant Blank and or Corrupted in Windows NT / 2000 / XP
Still working on apptlist program using queue in C++
Windows XP super slow startup because of sp2 in Windows NT / 2000 / XP

rameshsambu

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

8 posts
since Oct 2006

Permalink

Mar 23rd, 2007

Re: finding duration worked between time ranges from login and logout time

pankaj_chetry

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Mar 2007

Permalink

Mar 25th, 2007

Re: finding duration worked between time ranges from login and logout time

Might a SQL query like this work?

SQL Syntax (Toggle Plain Text)
SELECT employee, SUM(login_date_time-logout_date_time) as empTime 
  FROM login_log_table
 WHERE login_date_time BETWEEN X AND Y
 GROUP BY employee
SELECT employee, SUM(login_date_time-logout_date_time) as empTime 
  FROM login_log_table
 WHERE login_date_time BETWEEN X and Y
 GROUP BY employee

Memento

Reputation Points: 12

Solved Threads: 0

Light Poster

Offline

30 posts
since Jan 2007

Permalink

Mar 30th, 2007

Re: finding duration worked between time ranges from login and logout time

Hi Ramesh,

This example might be more complex than what you need, but it should give you a starting point. Caveat - This example only counts the time that fall within @start and @end period. So for example if an employee started work at 11/1/06 6:30AM, and you entered a @start date of 11/1/06 7:30 am, the hour between 6:30AM and 7:30AM would not be counted.

ColdFusion Syntax (Toggle Plain Text)
 
--- counts ONLY the time worked between @start and @end 
SELECT    e.emp_id,
    SUM 
    ( 
        DATEDIFF 
        (n,
        CASE WHEN login_time < @start THEN @start ELSE login_time END,
        CASE WHEN logout_time > @end THEN @end ELSE logout_time END
        ) 
    ) 
    AS TotalTimeWithinDateRange
FROM    LoginTable lt 
    INNER JOIN Store s ON lt.store_id = s.store_id
    INNER JOIN Employee e ON lt.emp_id = e.emp_id
WHERE    s.store_id = @store_id AND 
    (
        (lt.login_time BETWEEN @start AND @end) OR
        (lt.logout_time BETWEEN @start AND @end) OR
        (lt.login_time <= @start and logout_time >= @end)
    )
GROUP BY e.emp_id
--- counts ONLY the time worked between @start and @end 
SELECT    e.emp_id,
    SUM 
    ( 
        DATEDIFF 
        (n,
        CASE WHEN login_time < @start THEN @start ELSE login_time END,
        CASE WHEN logout_time > @end THEN @end ELSE logout_time END
        ) 
    ) 
    AS TotalTimeWithinDateRange
FROM    LoginTable lt 
    INNER JOIN Store s ON lt.store_id = s.store_id
    INNER JOIN Employee e ON lt.emp_id = e.emp_id
WHERE    s.store_id = @store_id AND 
    (
        (lt.login_time BETWEEN @start AND @end) OR
        (lt.logout_time BETWEEN @start AND @end) OR
        (lt.login_time <= @start and logout_time >= @end)
    )
GROUP BY e.emp_id

cfAllie

Reputation Points: 10

Solved Threads: 0

Unverified User

Offline

28 posts
since Mar 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in ColdFusion Forum Timeline: hiding runtime dialog "window" for jrunsvc.exe

Next Thread in ColdFusion Forum Timeline: Need help with n-tier looping

DaniWeb Message

Cancel Changes

User Name
Password