conversion of excel formula to case statement in tsql

Question

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Which part of this do you need assistance with?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

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: 736183 More actions · Answer 3

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: 182911 More actions · Answer 4

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