conversion of excel formula to case statement in tsql

  • Hi  all,

    need your help on conversion of excel formula into case statement. i tried but missing only one case. below is excel formula:

    =IF(AND(Q141="EndOfDay", AD141="EndOfDay",V141=""),8,

    MIN(8,

    IF(OR(Q141="EndOfDay"), 0,

    IF( AND( OR(Q141= "Paused", Q141= "PlannedBreak", Q141= "Completed"), AD141="EndOfDay" ),

    MAX(0,T141-7),

    IF(Z141="",0,

    (ROUNDDOWN(Z141/10000,0)+(ROUNDDOWN(RIGHT(Z141,4)/100,0)/60)+(ROUNDDOWN(RIGHT(Z141,2),0)/(60*60)) )

    - (ROUNDDOWN(J141/10000,0)+(ROUNDDOWN(RIGHT(J141,4)/100,0)/60) +(ROUNDDOWN(RIGHT(J141,2),0)/(60*60)) )

    )))

    ))

     

    Thanks

    Abhas

  • Which part of this do you need assistance with?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Abhas wrote:

    Hi  all,

    need your help on conversion of excel formula into case statement. i tried but missing only one case. below is excel formula:

    =IF(AND(Q141="EndOfDay", AD141="EndOfDay",V141=""),8, MIN(8, IF(OR(Q141="EndOfDay"), 0, IF( AND( OR(Q141= "Paused", Q141= "PlannedBreak", Q141= "Completed"), AD141="EndOfDay" ), MAX(0,T141-7), IF(Z141="",0, (ROUNDDOWN(Z141/10000,0)+(ROUNDDOWN(RIGHT(Z141,4)/100,0)/60)+(ROUNDDOWN(RIGHT(Z141,2),0)/(60*60)) ) - (ROUNDDOWN(J141/10000,0)+(ROUNDDOWN(RIGHT(J141,4)/100,0)/60) +(ROUNDDOWN(RIGHT(J141,2),0)/(60*60)) ) )))

    ))

    Step one would be to post the column names that you're using above.  For example, what's in Column "Z"???

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • It would help if you showed some work. The general flow would be something like:

    SELECT
    CASE WHEN Q141 = 'EndofDay' AND AD141 = 'EndofDay' AND V141 = '' THEN 8
    ELSE xx
    END

    RoundDown should be ROUND() in T-SQL. Min() is an aggregate, so that might cause some issues in a query, but hard to tell from here.

  • Abhas wrote:

    Hi  all,

    need your help on conversion of excel formula into case statement. i tried but missing only one case. below is excel formula:

    =IF(AND(Q141="EndOfDay", AD141="EndOfDay",V141=""),8, MIN(8, IF(OR(Q141="EndOfDay"), 0, IF( AND( OR(Q141= "Paused", Q141= "PlannedBreak", Q141= "Completed"), AD141="EndOfDay" ), MAX(0,T141-7), IF(Z141="",0, (ROUNDDOWN(Z141/10000,0)+(ROUNDDOWN(RIGHT(Z141,4)/100,0)/60)+(ROUNDDOWN(RIGHT(Z141,2),0)/(60*60)) ) - (ROUNDDOWN(J141/10000,0)+(ROUNDDOWN(RIGHT(J141,4)/100,0)/60) +(ROUNDDOWN(RIGHT(J141,2),0)/(60*60)) ) )))

    ))

    Thanks

    Abhas

    Let's rephrase this, you need a bit of alchemy, turning turd to gold

    😎

    First of all, try to format the statement properly, secondly, this statement does not parse as it is incomplete

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply