conversion of excel formula to case statement in tsql

• Abhas

  SSC Enthusiast

  Points: 181

  More actions

  December 4, 2020 at 3:19 pm

  #3816965

  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

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  December 4, 2020 at 3:24 pm

  #3816968

  Which part of this do you need assistance with?

  ---

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  December 4, 2020 at 4:51 pm

  #3817001

  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.
  

  ---

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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738063

  More actions

  December 4, 2020 at 6:48 pm

  #3817049

  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.

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  December 4, 2020 at 7:06 pm

  #3817054

  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