Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: if statement with 3 conditions

    how to use if statement with 3 conditions?

    if time_start and time_end <= '16:00' execute this
    if time_start <= '16:00 and time_end >= '16:00' execute this
    if time_start || time_end >= '16:00' execute this

    is it posssible to have an if statement inside an if statement?

    thanks in advance
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Yes its possible + but......

    Think you can have about 10 to 15 if statements in 1

    if (today="monday" ,"Monday"
    ,if(today="Teusday","Teusday"
    ,if(today="Wednesday","Wednesday"
    ,if(today="Thursday","Thursday"
    ,if(today="Fryday","Fryday"
    ,if(today="Saturday","Saturday"
    ,"Sunday"))))))

    but would rather choose an other way if you have a lot of statements
    See
    CASE WHEN Name = "Peter" THEN "Hello Peter"
    CASE WHEN Name = "John" THEN "Hello John"
    CASE WHEN Name = "Jane" THEN "Hello Jane"
    ELSE "Hello you" END
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you have more than on "IF", then you are starting to verge into the case statement, rather than an IF. mind you unless you are using the result in other processign within the select persoanlly I'd do that sort of IF construct as part of the application logic

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by homer.favenir
    if time_start and time_end <= '16:00' execute this
    if time_start <= '16:00 and time_end >= '16:00' execute this
    if time_start || time_end >= '16:00' execute this
    first of all, SELECT queries don't "execute" anything

    secondly, time_start and time_end isn't a valid expression and cannot be compared to '16:00'

    neither is time_start || time_end

    could you perhaps explain in words what you really want to do with this complicated IF? what are the three different things this IF is supposed to "execute"?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    i have to get the total hours greater than 16:00 (overtime)
    and i have to get the total hours less than 16:00 (regular time)
    for every person and for every book he finished.
    i made an if statement and it executes what i need and im using it now,

    if(min(coding_log.time_start) <= '16:00' and max(coding_log.time_end) >= '16:00', subtime(max(coding_log.time_end),'16:00'),
    if(min(coding_log.time_start) and max(coding_log.time_end) <= '16:00', 'no ot',subtime(max(coding_log.time_end),min(coding_lo g.time_start)))) as 'Over Time',

    is there other way to simplify this conditions?

    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can't comment, don't understand your data, and in particular i have no idea what "for every book he finished" means in relation to overtime

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    by the way, let's let min(coding_log.time_start) be MinS and let's let max(coding_log.time_end) be MaxE

    your expression becomes ...

    if(MinS <= '16:00' and MaxE >= '16:00', subtime(MaxE,'16:00'),
    if(MinS and MaxE <= '16:00', 'no ot',subtime(MaxE,MinS))) as 'Over Time',

    it should now be easier to spot the error

    can't see it?

    if(MinS and MaxE <= '16:00', ...

    this doesn't do what you think it does

    also, you will probably want to do arithmetic on the overtime amount, so setting 'no ot' is wrong, you should set it to 0 instead

    may i make a suggestion? rewrite your nested IFs with a simpler CASE expression
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Please specify following

    Quote Originally Posted by homer.favenir
    .......
    if(min(coding_log.time_start) <= '16:00' and max(coding_log.time_end) >= '16:00', subtime(max(coding_log.time_end),'16:00'),
    if(min(coding_log.time_start) and max(coding_log.time_end) <= '16:00', 'no ot',subtime(max(coding_log.time_end),min(coding_lo g.time_start)))) as 'Over Time',........
    1 What is the data type of : "time_start" AND "time_end"
    2 are they calculated hours ? or acual start time and end time
    3 specify a regular work day in hours, even shifts (i.e. : 09:00 to 17:30 = 8 hours or in your case i think 7:30 till 16:00)
    4 paste an row example of the db

    * Hope you know that the max and min will give you the highest and lowest value in your db REGARDLESS. Think you rather want the diff between start and end and see if someone worked longer than 8 hours
    * How can you have 3 cases if you want to calculate overtime.
    either you have or you don't, whats the third???
    * IN YOUR CASE {if(min(coding_log.time_start) <= '16:00' and max(coding_log.time_end) >= '16:00' }
    WHAT WILL HAPPEN IF : SOMEONE STARTS AT 15:30 AND STOPS AT 16:05 DOES HE GET OVERHOURS ?????
    Where do you work ;-)
    Last edited by Marvels; 02-12-08 at 07:11.
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  9. #9
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Talking

    ok...
    1 What is the data type of : "time_start" AND "time_end" (time)
    2 are they calculated hours ? or acual start time and end time (actual input time)
    3 specify a regular work day in hours, even shifts (i.e. : 09:00 to 17:30 = 8 hours or in your case i think 7:30 till 16:00) (we have regular time of 07:00 to 16:00 including 1:30 of break, after 16:00 is the ot)

    * Hope you know that the max and min will give you the highest and lowest value in your db REGARDLESS. Think you rather want the diff between start and end and see if someone worked longer than 8 hours
    (we have to know the start time and end time of each job)

    * How can you have 3 cases if you want to calculate overtime.
    either you have or you don't, whats the third???
    (the first case is if start time and end time is less than 16:00 there is no overtime, second case is if start time less than 16:00 and end time greater than 16:00, max(end time) minus 16:00 to get the difference between the regular time and the overtime in that particular job, case 3 is if both start time and end time is greater than 16:00 get the difference of end time and start time.

    * IN YOUR CASE {if(min(coding_log.time_start) <= '16:00' and max(coding_log.time_end) >= '16:00' }
    WHAT WILL HAPPEN IF : SOMEONE STARTS AT 15:30 AND STOPS AT 16:05 DOES HE GET OVERHOURS ????? (yes, he has 5min of overtime)

    i have to get the breakdown of each book for every person we work out,ang get the overtime and the regular time for each book, my query is group by date, person and book.
    where do i work? here in manila.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  10. #10
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    Quote Originally Posted by r937
    by the way, let's let min(coding_log.time_start) be MinS and let's let max(coding_log.time_end) be MaxE

    your expression becomes ...

    if(MinS <= '16:00' and MaxE >= '16:00', subtime(MaxE,'16:00'),
    if(MinS and MaxE <= '16:00', 'no ot',subtime(MaxE,MinS))) as 'Over Time',

    it should now be easier to spot the error

    can't see it?

    if(MinS and MaxE <= '16:00', ...

    this doesn't do what you think it does

    also, you will probably want to do arithmetic on the overtime amount, so setting 'no ot' is wrong, you should set it to 0 instead

    may i make a suggestion? rewrite your nested IFs with a simpler CASE expression
    can you please show me how?

    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  11. #11
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Have to get myself a job in Manila !

    Quote Originally Posted by homer.favenir
    ok...
    from 15:30 to 16:05 (yes, he has 5min of overtime)
    THAT DOES NOT COMPUTE!!!, so some is late due to traffic starts at 10 am gets 1,5hour break and to finish his job/book at 16:45 and gets overtime cool. but verry verry verry verry verry ??????
    Dont let the floor-workers get a hold off that code.

    Tink rather you have to do a datediff in hours subtract 1.5(break) hours and there you have your actual worked hours. If its greater than 8 hours he has over time substract 8 from remaining and you have his over time.

    If you want the hours spend on a book, order it by book
    add-up all hours (think it could be possible that a book takes more than 1 work day). Allso possible that a worker gets sick and a other person finishess his book so don't know about the order by User.

    Hope you have a good description of what they want because i'm lost here.
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

Posting Permissions

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