Case Conditional in SQL Statement - MS SQL 2000

[Alex]

Hi,

I'm trying to do calculations in a SQL statement, but depending on one
variable (a.type in example) I'll need to pull another variable from
seperate tables.

Here is my code thus far:

select a.DeptCode DeptCode,
a.Type Type,
(a.ExpenseUnit / (select volume from TargetData b where b.type =
a.type)
) Expense
From calc1 a

The problem... a.Type can be FYTD, Budget, or Target... and depending
on which one it is, I need to make b either FYTDData, TargetData, or
BudgetData. I'm thinking a case statement might do the trick, but I
can't find any syntax on how to use Case in an MS SQL statement. Even
If statements will work (if that's possible), though case would be
less messy.

Any suggestions would be much appriciative. Thanks...

Alex.

[John Bell]

Hi

Is it not totally clear how you are joining these tables, but this may be a
start.

SELECT a.DeptCode DeptCode,
a.Type Type,
a.ExpenseUnit / ( CASE WHEN a.Type = 'FYTD' THEN b.volume
WHEN a.Type = 'Budget' THEN
c.volume
WHEN a.Type = 'Target' THEN
d.volume
ELSE 1 END ) AS Expense
From calc1 a
LEFT JOIN FYTDData d ON b.type = a.type
LEFT JOIN BudgetData d ON c.type = a.type
LEFT JOIN TargetData d ON d.type = a.type

John

"Alex" <al**@totallynerd.com> wrote in message
news:2b**************************@posting.google.c om...

Hi,

I'm trying to do calculations in a SQL statement, but depending on one
variable (a.type in example) I'll need to pull another variable from
seperate tables.

Here is my code thus far:

select a.DeptCode DeptCode,
a.Type Type,
(a.ExpenseUnit / (select volume from TargetData b where b.type =
a.type)
) Expense
From calc1 a

The problem... a.Type can be FYTD, Budget, or Target... and depending
on which one it is, I need to make b either FYTDData, TargetData, or
BudgetData. I'm thinking a case statement might do the trick, but I
can't find any syntax on how to use Case in an MS SQL statement. Even
If statements will work (if that's possible), though case would be
less messy.

Any suggestions would be much appriciative. Thanks...

Alex.

[Erland Sommarskog]

Alex (al**@totallynerd.com) writes:

The problem... a.Type can be FYTD, Budget, or Target... and depending
on which one it is, I need to make b either FYTDData, TargetData, or
BudgetData. I'm thinking a case statement might do the trick, but I
can't find any syntax on how to use Case in an MS SQL statement.

Books Online is a very resource for this kind of information, just
look up CASE. Be careful to notice that this is not a statement, but
an expression.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Alex]

"John Bell" <jb************@hotmail.com> wrote in message news:<3f**********************@reading.news.pipex. net>...

Hi

Is it not totally clear how you are joining these tables, but this may be a
start.

SELECT a.DeptCode DeptCode,
a.Type Type,
a.ExpenseUnit / ( CASE WHEN a.Type = 'FYTD' THEN b.volume
WHEN a.Type = 'Budget' THEN
c.volume
WHEN a.Type = 'Target' THEN
d.volume
ELSE 1 END ) AS Expense
From calc1 a
LEFT JOIN FYTDData d ON b.type = a.type
LEFT JOIN BudgetData d ON c.type = a.type
LEFT JOIN TargetData d ON d.type = a.type

John

Hi John...

I did get it going yesterday after spending about an hour testing
syntax. Below is the final SQL statement. Works great!
select a.DeptCode,
a.Type,
(((a.TotalPaidHoursUnit/(Case a.type
When 'FYTD04' Then null
When 'Budget' Then (select b.monthly from it_budvol b where
b.deptcode = a.deptcode)
When 'Prior Year' Then (select b.avemonth from it_pyvolume b
where b.deptcode = a.deptcode)
Else (select b.AveMonthVolume from solucient_dss b where
b.deptcode = a.deptcode and b.type = a.type)
end)
- a.FYTD_TotalPaidHoursUnit ) / a.Hours) * a.FYTD_Volume) LaborFTE

From dss_calc a

Thanks for the feedback.

Alex.

[John Bell]

al**@totallynerd.com (Alex) wrote in message news:<2b**************************@posting.google. com>...

Hi John...

I did get it going yesterday after spending about an hour testing
syntax. Below is the final SQL statement. Works great!
select a.DeptCode,
a.Type,
(((a.TotalPaidHoursUnit/(Case a.type
When 'FYTD04' Then null
When 'Budget' Then (select b.monthly from it_budvol b where
b.deptcode = a.deptcode)
When 'Prior Year' Then (select b.avemonth from it_pyvolume b
where b.deptcode = a.deptcode)
Else (select b.AveMonthVolume from solucient_dss b where
b.deptcode = a.deptcode and b.type = a.type)
end)
- a.FYTD_TotalPaidHoursUnit ) / a.Hours) * a.FYTD_Volume) LaborFTE

From dss_calc a

Thanks for the feedback.

Alex.

Hi

You should make sure that you are not dividing by zero.

John