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
Attached is the query with Excel formulas
asked Jul 17, 2017 at 06:14 PM in Default
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.
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.
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: