Convert Excel formulae to TSQL

dinesh 41 Reputation points

I have a requirement to convert excel formula to SQL query. Please help me out on below 4 excel formula

=If(AND(N3="A", R3>O3), R3,If(AND(N3="P",S3>O3),S3,If(N3="D","D",If(OR(Q3="P",Q3="A")*AND(P3>TODAY(),P3>O3),P3,O3))))


=IF(W3<TODAY(),"C",IF(W3<(TODAY()+7),"C IN 7", IF(W3<TODAY+30,"CE",IF(W3<TODAY()+60,"CE 31D","CE 61D"))))


All above '3' number related to row3. O,P,R & S are date columns

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,657 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

2 answers

Sort by: Most helpful
  1. dinesh 41 Reputation points

    Hi, I'm looking for help on below formula. I wrote like this but am getting wrong data.

    =If(AND(N3="A", R3>O3), R3,If(AND(N3="P",S3>O3),S3,If(N3="D","",If(OR(Q3="P",Q3="A")*AND(P3>TODAY(),P3>O3),P3,O3))))

    select case when N='A' and R>O THEN R
    when N='P' and S>O then S
    when N='D' then ''
    when (Q='P' or Q='Á') and p>getdate() and P>O then P else O end data
    from tablename

    I have highlighted and condition. In few cases if i put and (in bold) am getting data in other cases if i put OR then am getting right data. Could someone please help me.

  2. MelissaMa-MSFT 24,176 Reputation points

    Hi @dinesh ,

    There is a similar function IIF in SQL Server.

    You could refer below and check whether it is helpful to you.

    select IIF( N='A' and R>O ,R,IIF( N='P' and S>O ,S,  
    IIF( N='D','',IIF((Q='P' or Q='Á') and p>getdate() and P>O ,P ,O)))) data   
    from test  

    Best regards

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.