SQL statement giving me trouble

tpoettker · #1 (**permalink**) 02-16-07, 11:46

I have a table with the following fields
ClaimID
IndemnityPaid
IndemnityCollected
IndemnityReserved
IndemnityIncurred
NetIndemnity
ExpensesPaid
ExpensesCollected
ExpensesReserved
ExpensesIncurred
NetExpenses

I need to find the claimID when total Indemnity + Expenses > 500000
Indemnity will equal the greater of IndemnityReserved or NetIndemnity
Expenses will equal the greater of ExpensesReserved or NetExpenses

I don't know how to put this into a sql statement. Would this be in the where conditions as a case or what???

RedNeckGeek · #2 (**permalink**) 02-16-07, 12:29

Depending on whether your platform supports CASE construct
(Access doesn't)

Code:

SELECT CLAIMID, 
'INDEMNITY' = CASE WHEN INDEMNITYRESERVED>NETINDEMNITY THEN INDEMNITYRESERVED ELSE NETINDEMNITY END,
'EXPENSES'=  CASE WHEN EXPENSESRESERVED>NETEXPENSES THEN EXPENSESRESERVED ELSE NETEXPENSES END
FROM #TMPCLAIMS
WHERE (CASE WHEN INDEMNITYRESERVED>NETINDEMNITY THEN INDEMNITYRESERVED ELSE NETINDEMNITY END + CASE WHEN EXPENSESRESERVED>NETEXPENSES THEN EXPENSESRESERVED ELSE NETEXPENSES END)>500000

Of course, you don't need to include INDEMNITY and EXPENSES in the SELECT statement, I just put it there so you could see the values it would pull. You can delete that, and just leave the WHERE clause as is.

tpoettker · #3 (**permalink**) 02-16-07, 13:11

thank you. it worked.

Internet Services

Web Development

Digital Marketing

Consumer Tech