Case or if then else on a calc query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Case or if then else on a calc query

Author

Topic

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-11-05 : 07:24:33

Hi, I have the following.
How do I create a case or if then else state depending if the length
of the field is equal to 2.
This is what I have but it does not work.

SELECT a.dispenserid, (a.dispenser1 - b.dispenser1) * c.dispenser1 AS Disp1Amount,
(a.dispenser2 - b.dispenser2) * c.dispenser2 AS Disp2Amount,
(a.dispenser3 - b.dispenser3) * c.dispenser3 AS Disp3Amount,
(a.dispenser4 - b.dispenser4) * c.dispenser4 AS Disp4Amount,
(a.dispenser5 - b.dispenser5) * c.dispenser5 AS Disp5Amount,
= CASE c.hopper1
WHEN LEN(c.hopper1) = 2 THEN
(a.hopper1 - b.hopper1) * c.hopper1 / 2 AS Hop1Amount
ELSE
(a.hopper1 - b.hopper1) * c.hopper1 AS Hop1Amount,
(a.hopper2 - b.hopper2)* c.hopper2 AS Hop2Amount,
(a.hopper3 - b.hopper3) * c.hopper3 AS Hop3Amount
FROM cashloaded AS a INNER JOIN cashdispensed AS b ON a.dispenserid = b.dispenserid INNER JOIN denominations AS c ON a.dispenserid = c.dispenserid
WHERE a.dispenserid = @dispenserid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 07:31:35

[code]SELECT a.dispenserid,
(a.dispenser1 - b.dispenser1) * c.dispenser1 AS Disp1Amount,
(a.dispenser2 - b.dispenser2) * c.dispenser2 AS Disp2Amount,
(a.dispenser3 - b.dispenser3) * c.dispenser3 AS Disp3Amount,
(a.dispenser4 - b.dispenser4) * c.dispenser4 AS Disp4Amount,
(a.dispenser5 - b.dispenser5) * c.dispenser5 AS Disp5Amount,
(a.hopper1 - b.hopper1) * c.hopper1 / CASE WHEN LEN(c.hopper1) = 2 THEN 2 ELSE 1 END AS Hop1Amount,
(a.hopper2 - b.hopper2) * c.hopper2 AS Hop2Amount,
(a.hopper3 - b.hopper3) * c.hopper3 AS Hop3Amount
FROM cashloaded AS a
INNER JOIN cashdispensed AS b ON a.dispenserid = b.dispenserid
INNER JOIN denominations AS c ON a.dispenserid = c.dispenserid
WHERE a.dispenserid = @dispenserid

SELECT a.dispenserid,
(a.dispenser1 - b.dispenser1) * c.dispenser1 AS Disp1Amount,
(a.dispenser2 - b.dispenser2) * c.dispenser2 AS Disp2Amount,
(a.dispenser3 - b.dispenser3) * c.dispenser3 AS Disp3Amount,
(a.dispenser4 - b.dispenser4) * c.dispenser4 AS Disp4Amount,
(a.dispenser5 - b.dispenser5) * c.dispenser5 AS Disp5Amount,
CASE
WHEN LEN(c.hopper1) = 2 THEN (a.hopper1 - b.hopper1) * c.hopper1 / 2
ELSE (a.hopper1 - b.hopper1) * c.hopper1
END AS Hop1Amount,
(a.hopper2 - b.hopper2) * c.hopper2 AS Hop2Amount,
(a.hopper3 - b.hopper3) * c.hopper3 AS Hop3Amount
FROM cashloaded AS a
INNER JOIN cashdispensed AS b ON a.dispenserid = b.dispenserid
INNER JOIN denominations AS c ON a.dispenserid = c.dispenserid
WHERE a.dispenserid = @dispenserid[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-05 : 07:34:28

Replace

= CASE c.hopper1
WHEN LEN(c.hopper1) = 2 THEN
(a.hopper1 - b.hopper1) * c.hopper1 / 2 AS Hop1Amount
ELSE
(a.hopper1 - b.hopper1) * c.hopper1 AS Hop1Amount,

by

CASE
WHEN LEN(c.hopper1) = 2 THEN
(a.hopper1 - b.hopper1) * c.hopper1 / 2 AS Hop1Amount
ELSE
(a.hopper1 - b.hopper1) * c.hopper1 AS Hop1Amount,

Madhivanan

Failing to plan is Planning to fail

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-11-05 : 07:35:32

Thanks much appreciated. Im going with the second one.

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-11-05 : 07:41:09

Can this be done with an IF THEN ELSE statement
for Hop1Amount
Hop2Amount AND Hop3Amount

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-05 : 07:54:23

Whats wrong with CASE WHEN?

Madhivanan

Failing to plan is Planning to fail