# 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:

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!"

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)