User Name Password Register
DaniWeb IT Discussion Community
All
What is DaniWeb IT Discussion Community?
You're currently browsing the ColdFusion section within the Web Development category of DaniWeb, a massive community of 392,080 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,040 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our ColdFusion advertiser:
Views: 4317 | Replies: 3
Reply
Join Date: Oct 2006
Posts: 8
Reputation: rameshsambu is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
rameshsambu's Avatar
rameshsambu rameshsambu is offline Offline
Newbie Poster

Question finding duration worked between time ranges from login and logout time

  #1  
Nov 1st, 2006
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
AddThis Social Bookmark Button
Reply With Quote  
Join Date: Mar 2007
Posts: 1
Reputation: pankaj_chetry is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
pankaj_chetry pankaj_chetry is offline Offline
Newbie Poster

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

  #2  
Mar 23rd, 2007
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
Reply With Quote  
Join Date: Jan 2007
Location: Austin, TX
Posts: 30
Reputation: Memento is an unknown quantity at this point 
Rep Power: 2
Solved Threads: 0
Memento Memento is offline Offline
Light Poster

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

  #3  
Mar 25th, 2007
Might a SQL query like this work?

  1. SELECT employee, SUM(login_date_time-logout_date_time) AS empTime
  2. FROM login_log_table
  3. WHERE login_date_time BETWEEN X AND Y
  4. GROUP BY employee
Dan Moore
www.danmoore.org
Reply With Quote  
Join Date: Mar 2007
Posts: 28
Reputation: cfAllie is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
cfAllie cfAllie is offline Offline
Light Poster

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

  #4  
Mar 30th, 2007
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.

--- 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
Reply With Quote  
Reply

Only community members can participate in forum threads. You must register or log in to contribute.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

 

DaniWeb ColdFusion Marketplace
Thread Tools Display Modes

Similar Threads
Other Threads in the ColdFusion Forum

All times are GMT -4. The time now is 12:20 pm.
Forum system based on vBulletin Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC