SQL Query: Favorable or Unfavorable

[jstevens]

Is it possible to have a field compute whether the difference between two other fields is either favorable or unfavorable?

[EileenStifone]

Of course, but usually a query is used for this and not another field in the table.

[jstevens]

EileenStifone,

That is good to hear. I have revenue and expense account values compared to their respective budget accounts in a Temp table. The Variance field is computed and I believe the Fav_Unfav field needs an update routine or some other method.

Code: Select all

-- Microsoft SQL not mySQL
SELECT Account
	  , ActualYTD
	  , BudgetYTD
	  , ActualYTD - BudgetYTD AS Variance
	  ,'' AS Fav_Unfav 
FROM #Temp	  

Example: Query results should look like this

Account	ActualYTD	BudgetYTD	Variance	Fav_Unfav
Expense01	1000	500	500	Unfavorable
Expense02	2000	3000	-1000	Favorable
Revenue01	7000	8400	-1400	Unfavorable
Revenue02	5000	2000	3000	Favorable

Your suggestions are appreciated on how to populate the favorable/unfavorable field.

[HansV]

What are the criteria for the difference being favorable or unfavorable?

[StuartR]

It looks like favourable is (Account = "Expense*" AND BudgetYTD>=ActualYTD) OR (Account="Revenue*" AND ActualYTD>=BudgetYTD)
but that is just my guess

[HansV]

It's probably something like that, but I assume that Expense01 etc. are just exampkes, not the real account names...

[jstevens]

Stuart/Hans,

Please see the formula in Column G. It depends on the account type (expense/revenue) and whether the variance amount is positive or negative.

	A	B	C	D	E	F	G
1	Type	Account	ActualYTD	BudgetYTD	Variance	Fav_Unfav	Formula
2	Expense	Expense01	1000	500	500	Unfavorable	=IF(AND($A2="Expense",$E2>0),"Unfavorable","Favorable")
3	Expense	Expense02	2000	3000	-1000	Favorable	=IF(AND($A3="Expense",$E3>0),"Unfavorable","Favorable")
4	Revenue	Revenue01	7000	8400	-1400	Unfavorable	=IF(AND($A4="Revenue",$E4<0),"Unfavorable","Favorable")
5	Revenue	Revenue02	5000	2000	3000	Favorable	=IF(AND($A5="Revenue",$E5<0),"Unfavorable","Favorable")

Thanks for taking a look.

[HansV]

Code: Select all

-- Microsoft SQL not mySQL
SELECT Account,
    ActualYTD,
    BudgetYTD,
    ActualYTD - BudgetYTD AS Variance,
    IIf(Type='Expense' AND ActualYTD>BudgetYTD OR Type='Revenue' AND ActualYTD<BudgetYTD,'Unfavorab;e','Favorable' AS Fav_Unfav 
FROM #Temp

[jstevens]

Hans,

Your suggestion works and I appreciate the assistance. I got hung up with an UPDATE approach and the IIF is what I'm after.

I tried to streamline the IIF by substituting ActualYTD>BudgetYTD with Variance>0. After running the query I receive an error: Invalid column name 'Variance'. I believe the error message being returned relates to the Variance field not being in #TempTBL.

Code: Select all

DROP TABLE IF EXISTS #TempTBL

CREATE TABLE #TempTBL (
			 Type nvarchar(10)
	                 ,Account nvarchar(20)
	                 ,ActualYTD money not null
	                 ,BudgetYTD money not null
	                 )

INSERT INTO #TempTBL (Type,Account,ActualYTD,BudgetYTD) VALUES ('Expense','Expense01',1000,500)
INSERT INTO #TempTBL (Type,Account,ActualYTD,BudgetYTD) VALUES ('Expense','Expense02',2000,3000)
INSERT INTO #TempTBL (Type,Account,ActualYTD,BudgetYTD) VALUES ('Revenue','Revenue01',7000,8400)
INSERT INTO #TempTBL (Type,Account,ActualYTD,BudgetYTD) VALUES ('Revenue','Revenue02',5000,2000)

SELECT Account
       ,ActualYTD
       ,BudgetYTD
       ,ActualYTD-BudgetYTD AS Variance
--	   ,IIf(Type='Expense' AND ActualYTD>BudgetYTD OR Type='Revenue' AND ActualYTD<BudgetYTD,'Unfavorab;e','Favorable') AS Fav_Unfav
       ,IIf(Type='Expense' AND Variance>0 OR Type='Revenue' AND Variance<0,'Unfavorable','Favorable') AS Fav_Unfav
FROM #TempTBL

[HansV]

What is your objection to using the SQL that I suggested?

[jstevens]

Hans,

I really don't have an objection to your suggestion. I'm just curious as why the calculated variance field was not used. Would it not accomplish the same thing?

[HansV]

Some implementations of SQL allow you to refer to calculated columns that have been defined earlier in the SQL string, others don't.

[jstevens]

Hans,

Thanks for the clarification.

[jstevens]

I found a solution for utilizing a calculated column in a SQL query here.

Code: Select all

SELECT
  ColumnA,
  ColumnB,
  sub.calccolumn1,
  sub.calccolumn1 / ColumnC AS calccolumn2
FROM tab t
CROSS APPLY (VALUES (t.ColumnA + t.ColumnB)) AS sub(calccolumn1);