Share via

Convert Excel formulae to TSQL

dinesh 41 Reputation points
2021-02-08T07:37:10.537+00:00

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(AND(N3="A",W3>TODAY()),"A",IF(AND(N3="P",W3>TODAY()),"P",IF(N3="D","D",IF(OR(Q3="P",Q3="A")*AND(W3>TODAY()),"ERP",IF(AND(S3>R3,S3>P3,S3>0,S3>O3),"P",IF(AND(R3>S3,R3>P3,R3>0,R3>O3),"A",IF(AND(P3>R3,P3>S3,P3>0,P3>(O3-1)),"ERP","SLA")))))))

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

=IF(OR(Q3="P",Q3="A")*AND(P3>TODAY()),TODAY-T3,0)

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,689 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. dinesh 41 Reputation points
    2021-02-15T17:48:27.877+00:00

    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,191 Reputation points
    2021-02-16T07:29:28.18+00:00

    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
    Melissa

    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.