x

Help using Excelf IF formula in SQL IFF

I work for a library and have been tasked with developing hold-purchase alert report that flags Bibliographic records with a holds-to-copy ratio greater than 5:1.

I have been provided Excel formulas that I must convert to SQL using the IIF function. The computation from the previous column is needed in the next? I have been able to nest these formulas together.

I have looked for examples on the web and thought of using computed columns but not sure how to incorporate them.

I would welcome suggestions on how to make this simpler. I am having trouble with syntax in some columns and the "IF(and) excel formula as it should be used in SQL.

Any help would be greatly appreciated or if you have a better idea, I would be open to that too. I am using SQL 2014; Windows Server 2012

Thank you.

Attached is the query with Excel formulas

link text

more ▼

asked Jul 17 at 06:14 PM in Default

avatar image

jonlellelid
81 6 9 13

 --Excel formula: =IF(L11>M11,L11,M11) This column depends on the previous column so I do not know how to 

Try LAG() -

The number of rows back from the current row from which to obtain a value

https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql

Jul 18 at 09:52 PM KenJ
 --Excel formual =IF(AND((H11+B11)>25, B11<25), 25-B11,L11)    How do I present this column with the "and"    --column M    

you can include AND in a SQL expression, it's not a separate function like Excel. Think of it conceptually as being coded like this:

 IF((H11+B11)>25 AND B11<25, 25-B11,L11)

A more complex way to write it would be:

 IF((H11+B11)>25,IIF(B11<25, 25-B11,L11),L11)
Jul 18 at 09:57 PM KenJ
 --Excel formula: =IF((J11+B11)>100,(100-B11),J11) ...running afoul here...

The code you have for this one is getting very hard to follow. The excel formula has a simple sum of two columns where the TSQL has four or five IIF() expressions to represent J11.

Can you break that into simpler pieces? You already have a temp table; you could create another temp table where some of these complex expressions can be worked out before applying the final formula that came from Excel. That will help determine if you are running afoul just trying to represent cell J11 or if something else is going on here.

Jul 18 at 10:04 PM KenJ
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

As @KenJ suggested, the heavy use of the nested long IF statements will quickly make the query unreadable. Here is the version which should work except I had to remove the final GROUP BY as it does not make any sense. If there is already temp table from which the data is to be selected then there is no way to group by only some of the columns while attempting to include other columns which are not participating in the group by list or subjected to any aggregations. So, the final group by is commented out. All I did was that I "outsourced" the definition of columns H and I (RatioResult and FlorrCalc respectively) so that further columns which include these in the formulas do not continue repeatedly dragging their definitions down the lines making the final formula hard to read.

  • Edit **

Per latest request to add 2 more columns, the second CTE is added which uses the columns J, K and L from the first CTE just for the sake of readability.

  • End Edit **

Here is the relevant part of the script with final group by commented out of course until the reason for its usage is clearly defined:

 -- do not include the columns FlorrCalc1 (Column J), CapCalc (Column K) and CapCalc1 yet, do it outside
 ; with records as (
 SELECT
     H8.BibID,
     H8.Titl,
     H8.Authr,
     H8.NoItems,
     H8.ActHolds, 
     H8.Ratio,
  
     --Excel formula: =IF((A11/B11)>5,ROUNDDOWN((0.2*A11)-B11,0),0) 
     IIF((H8.ActHolds/H8.NoItems)>5,ROUND((0.2*H8.ActHolds)-H8.NoItems,0),0) as [RatioResult], --Colum H
                  
     --Excel formula:     =IF((H11+B11)<=25, H11, ROUNDDOWN((0.125*A11),0)-B11)
     IIF(
         (IIF((H8.ActHolds/H8.NoItems)>5,ROUND((0.2*H8.ActHolds)-H8.NoItems,0),0) + H8.NoItems) <=25, 
         IIF((H8.ActHolds/H8.NoItems)>5,ROUND((0.2*H8.ActHolds)-H8.NoItems,0),0), 
         ROUND((0.125*H8.ActHolds),0) - H8.NoItems
     ) as FlorrCalc --Column I
  
     from ##HoldRatio8 H8
                  
     WHERE H8.ActHolds > 0
 ), ready as (
     -- columns H and I are from the records CTE, columns: J (FlorrCalc), K (CapCalc), L (CapCalc1) are added
     select
         *, -- all columns from the CTE including columns H and I (RatioResult and FlorrCalc)
  
         --Excel formula: =IF(I11<0, 0, I11) Column J or FlorrCalc
         iif(FlorrCalc < 0, 0, FlorrCalc) FlorrCalc1, --Column J
  
         --Excel formula: =IF((J11+B11)>100,(100-B11),J11) J is FlorrCalc1 from line above, B is NoItems
         iif(iif(FlorrCalc < 0, 0, FlorrCalc) + NoItems > 100, 100 - NoItems, iif(FlorrCalc < 0, 0, FlorrCalc)) CapCalc, --Column K
  
         --Excel formula: =IF(K17<0, 0,K17) K is CapCalc from the "line" above
         iif(
             iif(iif(FlorrCalc < 0, 0, FlorrCalc) + NoItems > 100, 100 - NoItems, iif(FlorrCalc < 0, 0, FlorrCalc)) < 0, 
             0,
             iif(iif(FlorrCalc < 0, 0, FlorrCalc) + NoItems > 100, 100 - NoItems, iif(FlorrCalc < 0, 0, FlorrCalc))
         ) CapCalc1 -- Column L
         from records
 )
     select
         *, -- all columns up to and including column L (CapCalc1)
         -- Excel formula: IF(AND((H11+B11)>25, B11<25), 25-B11,L11) as [ColumnM],
         iif(RatioResult + NoItems > 25 and NoItems < 25, 25 - NoItems, CapCalc1) ColumnM,
 
         -- Excel formula: IF(L11>M11,L11,M11) as [ColumnN]
         iif(
             CapCalc1 > iif(RatioResult + NoItems > 25 and NoItems < 25, 25 - NoItems, CapCalc1), 
             CapCalc1, iif(RatioResult + NoItems > 25 and NoItems < 25, 25 - NoItems, CapCalc1)
         ) ColumnN
         from ready
         -- group by BibID, Titl, Authr, NoItems, ActHolds, Ratio;
 ----

Oleg

more ▼

answered Jul 19 at 06:44 PM

avatar image

Oleg
18.4k 3 7 28

nice separation of the initial calculations from their repeated use.

Jul 19 at 09:51 PM KenJ

Thanks Oleg, I am taking a look at this. - Jon

Jul 24 at 04:56 PM jonlellelid

Oleg,

Thanks for providing this solution, I have not worked with cte’s much. I looked at a couple websites and found a similar usage for calculations.

I have two additional columns (M and N) that reference previous columns.

EXCEL=IF(AND((H11+B11)>25, B11<25), 25-B11,L11) as [ColumnM],

IIF(([RatioResult]+NoItems)>25 AND NoItems<25, 25-NoItems,[CapCalc1]) [ColumnM],

EXCEL =IF(L11>M11,L11,M11) as [ColumnN]

IIF([CapCalc1] > [ColumnM], [CapCalc1], [CapCalc1]) as [ColumnN]

It looks like the only columns I can reference are those that are in the cte, Columns H and I, correct?

When I add the two above columns, I receive errors:

Msg 207, Level 16, State 1, Line 92 Invalid column name 'CapCalc1'. Msg 207, Level 16, State 1, Line 92 Invalid column name 'CapCalc1'. Msg 207, Level 16, State 1, Line 92 Invalid column name 'ColumnM'. Msg 207, Level 16, State 1, Line 92 Invalid column name 'CapCalc1'. Msg 207, Level 16, State 1, Line 92 Invalid column name 'CapCalc1'.

What do I need to do to accomplish this?

Thank you for your assistance.

Jon Lellelid

Jul 25 at 02:27 PM jonlellelid

@jonlellelid I modified the query in the answer to accommodate these 2 columns. The CTE is used simply in an attempt to improve readability somewhat, it does not make the query perform any better. In fact, it makes it worse. The only benefit is that it is much easier to write the formulas for further columns without necessity to keep copying/pasting the definitions of the earlier formulas, as this approach quickly makes the formulas difficult to understand. In other words, the same query could be restated without any CTEs, but this would make it longer, hard to read, and prone to errors. Please let me know if the edited query in the answer works. The GROUP BY clause is still commented out because it does not make sense yet until it's purpose is clarified.

Jul 25 at 05:39 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x148
x77
x13
x3

asked: Jul 17 at 06:14 PM

Seen: 54 times

Last Updated: Jul 25 at 05:39 PM

Copyright 2017 Redgate Software. Privacy Policy