Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: Change syntax from Update Case Statement to Update Union Statement

    Good day!

    Theirs a programmer told me that could I used Union Statement to solve my issue in my query, but i can't imagine how can I do that, because my query I used case statement, I have no idea how can I used union instead of case statement.

    Here is my problem, I need to get the rendered hours and the rendered hours should be only 8 hours or less 8 hours if the employee was time in late.

    I have 3 shifts 21:35 - 05:35, 05:35- 13:35, 13:35-21:35

    using case statement it works only in one shift, I want to get the rendered with different scheduled.

    Like for example :
    Employee 1 time in = 21:00 time out = 05:40 the rendered = 8 hours only event he time in early and time out late it only compute the 21:35 - 05:35
    Employee 2 time in = 06:35 time out = 13:50 the rendered should be = 7 hours only because he was late 1 hour because his schedule is 05:35 but he time in 06:35 even he time out late its not considered because only time from 05:35-13:35 will get
    Employee 3 time in = 13:35 time out = 24:35 the rendered should be = 8 only even he timeout late.

    Rendered should be compute only the 8 hours of work, no matter he time out late only 8 hours would be get based on their schedule. and subtrace if he time in late.

    here is my syntax in case statement:

    PHP Code:
    UPDATE employee SET rendered sec_to_time(time_to_sec('08:00:00') + 
                case 
                  
    when time_to_sec(time(timein)) < time_to_sec('21:35:00'then 0
                  
    else time_to_sec('21:35:00') - time_to_sec(time(timein))
                
    end +
                case 
                  
    when time_to_sec(time(timeout)) > time_to_sec('05:35:00'then 0
                  
    else time_to_sec(time(timeout)) - time_to_sec('05:35:00')
                
    end
    it is only for one shift and it works, but when I tried this to get the rendered for 3 shifts:

    PHP Code:
    UPDATE employee SET rendered sec_to_time(time_to_sec('08:00:00') + 
                case 
                  
    when time_to_sec(time(timein)) < time_to_sec('21:35:00'then 0
                  
    else time_to_sec('21:35:00') - time_to_sec(time(timein))
                
    end +
                case 
                  
    when time_to_sec(time(timeout)) > time_to_sec('05:35:00'then 0
                  
    else time_to_sec(time(timeout)) - time_to_sec('05:35:00')
                
    end +
    case 
                  
    when time_to_sec(time(timein)) < time_to_sec('05:35:00'then 0
                  
    else time_to_sec('05:35:00') - time_to_sec(time(timein))
                
    end +
                case 
                  
    when time_to_sec(time(timeout)) > time_to_sec('13:35:00'then 0
                  
    else time_to_sec(time(timeout)) - time_to_sec('13:35:00')
                
    end +
        case 
                  
    when time_to_sec(time(timein)) < time_to_sec('13:35:00'then 0
                  
    else time_to_sec('13:35:00') - time_to_sec(time(timein))
                
    end +
                case 
                  
    when time_to_sec(time(timeout)) > time_to_sec('21:35:00'then 0
                  
    else time_to_sec(time(timeout)) - time_to_sec('21:35:00')
                
    end
    ); 
    wrong output was displayed.

    If UNION Statement is the solution? How? Thank you

    Is there any query can i used? I really need to solved this .

    Any help is highly appreciated.

    Thank you so much

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by newphpcoder View Post
    Theirs a programmer told me that could I used Union Statement to solve my issue in my query, but i can't imagine how can I do that...
    so go back to that programmer and ask how
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    136
    He did not response when I ask how :(

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need a new programmer then
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by r937 View Post
    you need a new programmer then :D
    Can you help me???

    Thank you

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think you need a new field which indicates which shift an employee is on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2010
    Posts
    136
    Yes, now i add field for shift and I use:

    data 1 for 21:35 - 05:35
    data 2 for 05:35 - 13:35
    data 3 for 13:35 - 21:35
    data 4 for 08:00 - 08:00

    and now, how can I used shift field to get the rendered?

    Thank you

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm not going to continue to post to two completely identical threads on this site and on another site
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2010
    Posts
    136
    Okay, 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
  •