Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    problem in getting the max date and min date

    Hi...

    I got an problem in my attendance for the shift of 09:35 PM - 05:35 AM

    I have this example data that I was inserted in my database:

    --09:35 PM - 05:35 AM Shift----

    EMP_NO DATE_DTR DTR
    00300395 2011-11-27 2011-11-27 21:02:39
    00300395 2011-11-28 2011-11-28 05:36:48

    ---05:35 AM - 02:35 PM---

    EMP_NO DATE_DTR DTR
    00300395 2011-11-21 2011-11-21 05:09:09
    00300395 2011-11-21 2011-11-21 13:39:35

    ---02:35 PM - 09:35 PM

    EMP_NO DATE_DTR DTR
    00300395 2011-11-15 2011-11-15 13:15:08
    00300395 2011-11-15 2011-11-15 21:38:23


    This sample data from three shifts and i got problem in 09:35 PM - 05:35 PM
    here is my code to insert it in my database:

    Code:
    $sql = "INSERT INTO regular_dtr (EMP_NO, DATE_DTR, DTR) VALUES ('$EMP_NO', '$Date', '$DTR')";

    As you noticed the TimeIn and TimeOut of employee is in one field.

    And now i have another insert statement to get the min and max date of employee for time in and timeout.
    And i noticed that I have problem in my 09:35 PM - 05:35 AM

    Code:
     $result = mysql_query("INSERT INTO regular_dtr_total(EMP_NO, DATE_DTR, max_dtr, min_dtr, TotalHours) 
    SELECT a.EMP_NO, a.DATE_DTR, max(b.DTR),min(a.dtr),
    TIMEDIFF(max(b.DTR), min(a.DTR))
    FROM regular_dtr a
    LEFT JOIN regular_dtr b ON (a.EMP_NO = b.EMP_NO AND a.DATE_DTR = b.DATE_DTR)
    GROUP BY a.EMP_NO, a.DATE_DTR") 
     or die(mysql_error());
    It works in my 05:35 AM -02:35 PM and 02:35PM - 09:35 PM because in this shift is same in date, but in 09:35PM - 05:35 PM they are different date..

    min_dtr = time in
    max_dtr = time out

    And the result of this insert query is like this:

    -----09:35 PM - 05:35 AM ---

    EMP_NO DATE_DTR max_dtr min_dtr
    00300395 2011-11-27 2011-11-27 21:02:39 2011-11-27 21:02:39
    00300395 2011-11-28 2011-11-28 21:08:35 2011-11-28 05:35:48

    it shoud be like this:

    EMP_NO DATE_DTR max_dtr min_dtr
    00300395 2011-11-27 2011-11-28 05:35:48 2011-11-27 21:02:39

    As you notices this date 2011-11-28 21:08:35 should be the time in for the date of 2011-11-28

    And here is the correct output for 05:35 AM - 02:35 PM and 02:35 PM - 09:35 PM

    EMP_NO DATE_DTR max_dtr min_dtr
    00300395 2011-11-15 2011-11-15 21:38:23 2011-11-15 13:15:06 // 02:35 Pm - 09:35 PM
    00300395 2011-11-21 2011-11-21 13:39:35 2011-11-28 05:09:09 // 05:35 AM - 02:35 PM

    I hope somebody can help me to fix this problem..

    And also i will find the solution for that.

    Thank you so much..

    Any help is highly appreciated and any question is free to ask for further understanding.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    Well, I told you before this was a bad db design. And now you've proven it.

    Sorry, but you are finally out of luck. This bad data design has finally given you a situation there is no good answer for.

    on't I remember you said that sometimes a person would have two checkins or two checkouts for a single shift?

    If so, we could invent all kinds of bad answers, but with your data the way it is, no answer is going to work with all data.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    i have no choice in my data...because it is from attendance system...so from one field of their attendance i get the max and min but I noticed that I got problem in 09:35 Pm - 05:35 AM

    because 05:35 Am become min instead of max because it is time out..

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    I just don't think you are going to be able to do this in SQL.

    What you *need* to do is process that raw data and use it to create a *new* table that has employeeid, checkin, and checkout fields, only. And the checkin and checkout fields should be DATETIME fields.

    I think if you took the "raw" data and simply did
    Code:
    SELECT EMP_NO, DTR FROM regular_dtr ORDER BY EMP_NO, DTR
    Then you could use PHP or some outside logic to find *PAIRS* of VALID records and use them to build a new table with
    Code:
    EMP_NO, START_DTR, END_DTR
    In this way, you could look at adjacent records and, if you found (say) 4 times for the same shift you could just throw out the middle 2 and keep only the first and last.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    I just don't think you are going to be able to do this in SQL.

    What you *need* to do is process that raw data and use it to create a *new* table that has employeeid, checkin, and checkout fields, only. And the checkin and checkout fields should be DATETIME fields.

    I think if you took the "raw" data and simply did
    Code:
    SELECT EMP_NO, DTR FROM regular_dtr ORDER BY EMP_NO, DTR
    Then you could use PHP or some outside logic to find *PAIRS* of VALID records and use them to build a new table with
    Code:
    EMP_NO, START_DTR, END_DTR
    In this way, you could look at adjacent records and, if you found (say) 4 times for the same shift you could just throw out the middle 2 and keep only the first and last.

    You mean I don't need to used min and max?

    I really don't need how does the logic..

    because in 09:35 PM - 05:35 AM they are different date:confused:


    Thank you

  • #6
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I know its wrong query but i tried to remove max and min:

    Code:
    SELECT a.EMP_NO, a.DTR AS START_DTR, b.DTR AS END_DTR FROM regular_dtr a LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO;
    and yet the output is wrong...

    I really don't know how can I solved it..

    Thank you for your help...

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    No, I mean you need to write a *SPECIAL* processing program, in PHP or whatever is your coding language of choice, that will run through your regular_dtr table and throw away the unused data and create a *NEW* table of the form I recommended.

    I don't code PHP or I'd do it for you. If you want it in ASP code I would do it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (12-13-2011)

  • #8
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I used php ...

    Can you tell me the logic?

    Thank you

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    Okay, as I recall, you said that sometimes people would check in (or check out??) more than once. So your goal is to match up the first time a person checks in each day with the last time they checkout, right? Any other DTR records you will just ignore, yes?

    So it's pretty simple:

    (1) Use the simple query
    SELECT EMP_NO, DTR FROM regular_dtr ORDER BY EMP_NO, DTR

    (2) Read one record. Presumably, it will be a CHECKIN DTR. Remember the DTR value from that records as the "checkin" time.
    (3) In a loop, read the next records. When you find one that is obviously for the same SHIFT as the record from (2) you remember its DTR value as the "checkout" time. You may only find one record for the same SHIFT or you may find 2 or 3 more for that shift. [*You* will have to define what a "SHFIT" is. I would assume it is a checkout time that is no more than, say, 12 hours (?? maybe??) from the checkin.
    (4) When you read a DTR time that obviously is *NOT* from the same SHIFT, then you write a record to the new table:
    EMP_NO, CHECKIN_DTR, CHECKOUT_DTR
    (5) After writing that record, you use the DTR time that is not from the same SHIFT as the new checkin time for the *next* SHIFT. And you loop back to (3).

    Notice that if the EMP_NO changes, that is *automatically* a change of SHIFT.

    So let's make up a sample:
    Code:
    EMP_NO   DTR
    110011    Dec 3, 2011, 8:35 AM
    110011    Dec 3, 2011, 9:05 AM
    110011    Dec 3, 2011, 5:20 PM
    110011    Dec 4, 2011, 9:20 PM
    110011    Dec 4, 2011, 9:50 PM
    110011    Dec 5, 2011, 3:50 AM
    110011    Dec 5, 2011, 4:05 AM
    220022    Dec 3, 2011, 8:40 AM
    ...
    Isn't it *OBVIOUS* when looking at those date/times that the following is true?
    Code:
     
    EMP_NO    BEGINSHIFT              ENDSHIFT
    110011    Dec 3, 2011 8:35 AM     Dec 3, 2011 5:20 PM
    110011    Dec 4, 2011 9:20 PM     Dec 5, 2011 4:05 AM
    220022    Dec 3, 2011 8:40 AM     ... etc. ...
    So by making one run through the "raw" DTR data, you should be able to create a table with BEGINSHIFT and ENDSHIFT and then you can do *ALL* your computations (e.g, total time worked, etc.) from that new table.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (12-13-2011)

  • #10
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Actually, on my syntax, i don't need o check his shift I just got their min and max time to become their time in and time out, but as I've said I got the problem in using max and min in 09:35 - 05:35 shift with different dates..

    Someone told me that should I check if waht his previous shift?and also i need to minus one 1 day.

    But, honestly, i don't know how cn i revised my code:(

    Thank you for your help..

  • #11
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Honestly, when you see the DTR data, you never know what his in or out...because the in and out is in the same fields.

    Thank you


  •  

    Posting Permissions

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