Case Conditional in SQL Statement - MS SQL 2000

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
Fromcalc1 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.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" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310010840.5910e221@.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.|||Alex (alex@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<3f7b0da9$0$8765$ed9e5944@.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

Fromdss_calc a

Thanks for the feedback.

Alex.|||alex@.totallynerd.com (Alex) wrote in message news:<2ba4b4eb.0310020704.4c08463e@.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
> Fromdss_calc a
> Thanks for the feedback.
> Alex.
Hi

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

John

Case conditional action

Is it possible to check a variable in a case statement then perform a select
query depending on the variable? What I have tried is something like this
which does not work. aNyone know how this can be accomplished?
declare @.@.var int, @.rtn int
set @.var = 3
select case @.var when 1 select @.rtn = catid from categories
when 2 select @.rtn = itmid from categories
when 3 select @.rtn = catmid from categorymixbjamin wrote:
> Is it possible to check a variable in a case statement then perform a sele
ct
> query depending on the variable? What I have tried is something like this
> which does not work. aNyone know how this can be accomplished?
>
> declare @.@.var int, @.rtn int
> set @.var = 3
> select case @.var when 1 select @.rtn = catid from categories
> when 2 select @.rtn = itmid from categories
> when 3 select @.rtn = catmid from categorymix
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
A CASE expression is like this:
CASE <condition>
WHEN <evaluation result 1>
THEN <true result> ELSE <false result>
WHEN <evaluation result 1>
THEN <true result> ELSE <false result>
.. etc. ...
END
Use IF:
IF @.var = 1
SELECT @.rtn = catid FROM categories
IF @.var = 2
SELECT @.rtn = itmid FROM categories
IF @.var = 3
SELECT @.rtn = catmid FROM categorymix
These statements assume that there is only 1 row in each table, which is
probably wrong. What are you really trying to do?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAy4TYechKqOuFEgEQKYbwCgzax4m6K6c5lD
CcRV3sxStCYnhlUAoPRg
pAwpcd7fD1BWfkIGeB21eKuG
=opZI
--END PGP SIGNATURE--|||bjamin (bjamin@.discussions.microsoft.com) writes:
> Is it possible to check a variable in a case statement then perform a
> select query depending on the variable? What I have tried is something
> like this which does not work. aNyone know how this can be
> accomplished?
There isn't any CASE statement in SQL. There is a CASE expression, which is
something different.

> declare @.@.var int, @.rtn int
> set @.var = 3
> select case @.var when 1 select @.rtn = catid from categories
> when 2 select @.rtn = itmid from categories
> when 3 select @.rtn = catmid from categorymix
Could write:
SELECT @.rtn = CASE WHEN 1 THEN (SELECT catid FROM categories)
WHEN 2 THEN (SELECT itmid FROM categories)
WHEN 3 THEN (SELECT catmid FROM categorymix)
EMD
Although, I think most people would prefer to use IF/ELSE in this case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx