- Home /

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][1] [1]: /storage/temp/4191-holdem-8-ratio-query.sql

holdem-8-ratio-query.sql
(7.2 kB)

Comment

Kev Riley

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

--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)

--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.

**Answer** by Oleg
·
Jul 19, 2017 at 06:44 PM

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)5,ROUND((0.2*H8.ActHolds)-H8.NoItems,0),0) + 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

nice separation of the initial calculations from their repeated use.

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

@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.

how to export sql database table schema into msexcel file? 2 Answers

SQL Server 2008: SSIS pass variable fails for Excel filename 1 Answer

Problem with Excel Spreadsheet in Data Flow 1 Answer

Dynamic Excel Source file in SSIS using Variable - can't get it working 2 Answers

How can I automatically run a query and save to Excel (not using BCP/SSIS) 4 Answers

Copyright 2018 Redgate Software. Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Ask SSC Site Issues (meta-askssc)
- Explore
- Topics
- Questions
- Users
- Badges