Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2003
    Location
    philippines
    Posts
    5

    Unanswered: Get First Entry and Last Entry

    Hi All,

    I would like to seek your help regarding my problem. I am a newbie in programming with SQL 2000 and would apprciate it if you guys could help.

    This is my problem.

    I have this data: for each employee i have to get the last time in and last time out for each day. There are several out and in for a single day because it is we are using id readers for all doors in the office.

    the problem is how do i program this in creating a stored procedure.

    thanks

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Do you mean the first time in and last time out ???



    I would like to do this with a set based solution rather than a cursor based one ...
    Take all the records for the employees and insert them into a temporary table with the in times and out times as null ...
    Update the temp table with in times by selecting the min(time) from the time records table grouping by employee id
    Update the temp table with in times by selecting the max(time) from the time records table grouping by employee id

    ur intime and outtime table is ready
    Last edited by Enigma; 09-18-03 at 06:12.

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Do you mean the first time in and last time out ???

    I assume your table TimeSheet as follow

    EmpID
    DayOfEntrance
    TimeIn
    TimeOut

    Just do:

    SELECT EmpID, DayOfEntrance, Min(TimeIn), Max(TimeOut)
    FROM TimeSheet
    Group By EmpID, DayOfEntrance
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Sep 2003
    Location
    philippines
    Posts
    5
    Here's the scenario so you guys could understand it

    An employee can have several TimeIN and TimeOUT for each day because we have ID card readers for all entry points in the facility. So whenever you go in a room you have to scan your ID to gain Access and scan to go out.

    heres a sample database sceanrio:

    EMPLOYEEID TIME DATE MESSAGE

    0001 8:00 09-19-2003 ACCESS IN GRANTED
    0001 8:15 09-19-2003 ACCESS IN GRANTED
    0001 8:30 09-19-2003 ACCESS OUT GRANTED
    0001 8:35 09-19-2003 ACCESS IN GRANTED
    0001 11:00 09-19-2003 ACCESS OUT GRANTED
    0001 11:15 09-19-2003 ACCESS IN GRANTED
    0001 12:00 09-19-2003 ACCESS OUT GRANTED
    0001 13:00 09-19-2003 ACCESS IN GRANTED
    0001 13:03 09-19-2003 ACCESS IN GRANTED
    0001 17:40 09-19-2003 ACCESS OUT GRANTED
    0001 17:45 09-19-2003 ACCESS OUT GRANTED

    So from this data what I would want to get is only the two data:

    0001 8:00 09-19-2003 ACCESS IN GRANTED
    0001 17:45 09-19-2003 ACCESS OUT GRANTED

    Another twist to this problem is the company is using shifting schedules, there are 3 shifts namely:

    A - 6:00 to 14:00
    B - 14:00 to 22:00
    C - 22:00 to 6:00(the next day)

    How do I get the data from shift C using the above scenarios? If we filter the data per day the last TimeOUT which is aasumed to be next day will not be captured. A sample illustration is below:

    EMPLOYEEID TIME DATE MESSAGE

    0001 22:00 09-19-2003 ACCESS IN GRANTED
    0001 22:15 09-19-2003 ACCESS IN GRANTED
    0001 23:30 09-19-2003 ACCESS OUT GRANTED
    0001 23:35 09-19-2003 ACCESS IN GRANTED
    0001 24:00 09-19-2003 ACCESS OUT GRANTED
    0001 01:15 09-20-2003 ACCESS IN GRANTED
    0001 02:00 09-20-2003 ACCESS OUT GRANTED
    0001 02:20 09-20-2003 ACCESS IN GRANTED
    0001 03:03 09-20-2003 ACCESS IN GRANTED
    0001 06:40 09-20-2003 ACCESS OUT GRANTED
    0001 06:45 09-20-2003 ACCESS OUT GRANTED

    What could happen here is if the filter is per day the captured data will be:

    0001 22:00 09-19-2003 ACCESS IN GRANTED
    0001 24:00 09-19-2003 ACCESS OUT GRANTED

    When the correct data to be captured should be:

    0001 22:00 09-19-2003 ACCESS IN GRANTED
    0001 06:45 09-20-2003 ACCESS OUT GRANTED


    Hope this makes it easire to understand. Advance thanks to all who will help. Thanks to those who replied.

    Good day!

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Well using a temporary table will be the best solution for you ...

    That way you can take care of the shifts also ...

    Make the updates as i had suggested .. for the guys in the first and second shift ... for the third shift do the reverse ....

    only for this u will need to take an additional table where the shifts are defined for each person.

  6. #6
    Join Date
    Sep 2003
    Location
    India
    Posts
    3
    Hi goyers,

    You could do the following:
    Create a temp table with empid, day, min_timein and max_timein

    insert into temp empid, day, min(timein) group by empid, day

    update temp set max_timein = max(timein) from temp a, timesheet b
    where day(a.timein) = day(b.timein) and (day(b.timeout) = day(a.timein) or day(a.timein) + 1) and a.emmpid = b.empid and hr(b.timeout) <= (day(a.timein) + 1) - 18

    Just change the syntax wherever required. Hope th logic is useful to you.

    Regards,
    Last edited by NaveenI; 09-26-03 at 07:02.

  7. #7
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163

    Re: Get First Entry and Last Entry

    You may use a temporary table with triggers in the times column.
    That way you may keep only two times for each employee.
    You may also associate a formula to the trigger in order to solve the C scenarios. That formula could evaluate to check if it's affter 23:59:59 and then considerate another day.

    I hope that could help you

    Paulo

    Originally posted by goyers
    Hi All,

    I would like to seek your help regarding my problem. I am a newbie in programming with SQL 2000 and would apprciate it if you guys could help.

    This is my problem.

    I have this data: for each employee i have to get the last time in and last time out for each day. There are several out and in for a single day because it is we are using id readers for all doors in the office.

    the problem is how do i program this in creating a stored procedure.

    thanks

  8. #8
    Join Date
    Sep 2003
    Location
    philippines
    Posts
    5

    Unhappy

    hi all,

    im so sorry to inform you guys. but all the suggestions where not able to solve the problem. could anyone be good enough to teach me another way of solving my problem.

    im attaching a test file to help you understand. thanks

    thanks for all the help
    Attached Files Attached Files

  9. #9
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    It would be helpful if you add the date to your In/Out time, and if you add a Shift indication, because you are not grouping per day, but per shift. You will need this grouping for determining the first and last In/Out timestamp.

    After that, your query is as easy as I postd before.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  10. #10
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    This was an interesting problem. I believe what you are after is the first "IN" on any given day and the last "OUT" prior to the next "IN". As is demonstrated by your data file, people do not pass in and out exactly at the start or end of a shift.

    I doubt that this is exactly what you are looking for but I think you can work out the details.
    Attached Files Attached Files
    Paul Young
    (Knowledge is power! Get some!)

  11. #11
    Join Date
    Sep 2003
    Location
    philippines
    Posts
    5
    Paul Young,

    Hi, could you help me add the DOOR (ie 123456 Lledo) data as well to the final output in temporary table #tmp2? How do i do this?

    I'm trying to do this all afternoon but I wasn't able to get the correct solution. Thank you very much for helping.

    goyers

  12. #12
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Post what you have and I will take a look.
    Paul Young
    (Knowledge is power! Get some!)

  13. #13
    Join Date
    Sep 2003
    Location
    India
    Posts
    3
    Hi,
    We had a similar problem in our company where a chat application was running. Here again the user would login at 11:45 pm on previous day and log out at 12:15 am the next day. We had an intelligent workaround. We used to logically throw everybody out of the chat room, but physically they were still chatting.
    That is for our calculation purpose, we used set 00:00 hr as a zero hour and calculate the in and out with respect to 00:00. So in your case you can think of similar workaround where user is calculated as a part of shift on previous day and the rest on the next day.

    Hope this is useful to you.
    Regards,
    Naveen Indusekhar

  14. #14
    Join Date
    Sep 2003
    Location
    philippines
    Posts
    5

    Lightbulb

    Hi Paul,

    Its actually the same data I gave, what I need to have is to show also the IDs ( 123456, 123457, 123458 etc) for each persons because these are employee IDs and its what im going to use to link the data to another table in the database.

    Thanks
    Attached Files Attached Files

  15. #15
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    What I ment was to post your TSQL. All you need to do is replace references to lastname & firstname with a reference to the emp id.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •