SQL Query: Favorable or Unfavorable
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
SQL Query: Favorable or Unfavorable
Is it possible to have a field compute whether the difference between two other fields is either favorable or unfavorable?
Regards,
John
John
-
- NewLounger
- Posts: 15
- Joined: 16 Aug 2022, 05:39
Re: SQL Query: Favorable or Unfavorable
Of course, but usually a query is used for this and not another field in the table.
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: SQL Query: Favorable or Unfavorable
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.
Example: Query results should look like this
Your suggestions are appreciated on how to populate the favorable/unfavorable field.
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
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.
Regards,
John
John
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SQL Query: Favorable or Unfavorable
What are the criteria for the difference being favorable or unfavorable?
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12618
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: SQL Query: Favorable or Unfavorable
It looks like favourable is (Account = "Expense*" AND BudgetYTD>=ActualYTD) OR (Account="Revenue*" AND ActualYTD>=BudgetYTD)
but that is just my guess
but that is just my guess
StuartR
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SQL Query: Favorable or Unfavorable
It's probably something like that, but I assume that Expense01 etc. are just exampkes, not the real account names...
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: SQL Query: Favorable or Unfavorable
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.
Thanks for taking a look.
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.
Regards,
John
John
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SQL Query: Favorable or Unfavorable
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
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: SQL Query: Favorable or Unfavorable
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.
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
Regards,
John
John
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SQL Query: Favorable or Unfavorable
What is your objection to using the SQL that I suggested?
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: SQL Query: Favorable or Unfavorable
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?
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?
Regards,
John
John
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SQL Query: Favorable or Unfavorable
Some implementations of SQL allow you to refer to calculated columns that have been defined earlier in the SQL string, others don't.
Best wishes,
Hans
Hans
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: SQL Query: Favorable or Unfavorable
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);
Regards,
John
John