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

__________________
Alexander Nelson
Programmer

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

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