p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   CASE WHEN WHERE CLAUSE (http://p2p.wrox.com/showthread.php?t=52826)

deontae45 January 17th, 2007 11:49 AM

CASE WHEN WHERE CLAUSE
 
Hi everybody, I need some help. I'm trying to get a solution from the following code: The WHERE clause is where I'm having problems. I'm trying to combine two statements in the where clause if I run them seprate I get the answer I'm looking for but I want to combine the two to get one answer. I think I need a CASE WHEN in the WHERE Clause. Can anyone help me??

SELECT SUM(mPremium) as 'Total Gross Prem', SUM(mPremium)+
SUM((Case When mPremium < 0 Then mPremium Else 0 End)) as 'Total Net Prem',
SUM((Case When mPremium < 0 Then mPremium Else 0 End))as 'Total Return Prem',
(SUM(mPremium)+ SUM((Case When mPremium < 0 Then mPremium Else 0 End)))* .06 as 'Total Tax Due'
From tblPolicy
Where (dtSubmitDate >= '1/1/2005' and dtSubmitDate <= '12/31/2005'and dtPolicyIncepDate < '1/1/2005'and
vchTransactionType <> 'NEW POLICY' and vchTransactionType <> 'Renewal')
and vchSLBLicNo = 'A172757'
and
Case When (dtPolicyIncepDate >='1/1/2005' and
dtPolicyIncepDate <= '12/31/2005' and vchSLBLicNO = 'A172757') end

Alexander Nelson
Programmer

agossage January 17th, 2007 12:31 PM

The CASE statement is incomplete. THEN is required to determine the result of the case.
(i.e. SELECT * FROM table WHERE table_id = CASE WHEN (1 = 1) THEN 1 ELSE 0 END)

What are the two original statements?


Adam Gossage
Lake Wylie, SC, USA

kevlangdo January 11th, 2011 10:03 AM

Add a Case expression to a Where clause
 
Here are a couple of examples on how to add a Case expression to a where clause. For more complex case expressions or boolean operations, you could use a scalar function and just return a value:

How to Add a Case expression to a Where clause using a function


All times are GMT -4. The time now is 12:32 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.