Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    13

    Unanswered: SQL statement giving me trouble

    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???

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    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.
    Inspiration Through Fermentation

  3. #3
    Join Date
    May 2004
    Posts
    13
    thank you. it worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •