• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

SQL 2005 Procedure

In the Select statement I have joined a new Table Claims_Ceded_Amounts. What I want to do is if the ClaimNo from wellandp.ClaimFinancial or Table 1 is in Claims_Ceded_Amounts or Table 2 then the SUM(ExpPaid) AS ExpPaid should come from table 2 column ExpPaidCeded ELSE it should be ExpPaid from Table 1
( SELECT    ClaimNo
                                                   ,SUM(IndemResAmt) AS IndemResAmt
                                                   ,SUM(IndemPaid) AS IndemPaid
                                                   ,SUM(ExpResAmt) AS ExpResAmt
                                                   ,SUM(ExpPaid) AS ExpPaid                                                   
                                          FROM      wellandp.ClaimFinancial
                                          LEFT OUTER JOIN dbo.Claims_Ceded_Amounts CCA ON wellandp.ClaimFinancial.ClaimNo = CCA.ClaimNoCeded
                                          WHERE     wellandp.ClaimFinancial.TypeOfBusiness = 'T'
                                                    AND wellandp.ClaimFinancial.InitialAcctgDate <= DATEADD(day,
                                                              DATEDIFF(m, 0,
                                                              + '/1/'
                                                              + @AsOfYear) + 1,
                                          GROUP BY  ClaimNo
                                        ) AS CededFile ON wellandp.ClaimGeneral.ClaimNo = CededFile.ClaimNo

Open in new window

  • 3
  • 2
1 Solution
    SELECT ClaimNo,
           SUM(IndemResAmt) AS IndemResAmt,
           SUM(IndemPaid) AS IndemPaid,
           SUM(ExpResAmt) AS ExpResAmt,
           ExpPaid= ISNULL(SUM(CCA.ExpPaid),SUM(wellandp.ClaimFinancial.ExpPaid))
    FROM   wellandp.ClaimFinancial
           LEFT OUTER JOIN dbo.Claims_Ceded_Amounts CCA
                ON  wellandp.ClaimFinancial.ClaimNo = CCA.ClaimNoCeded
    WHERE  wellandp.ClaimFinancial.TypeOfBusiness = 'T'
           AND wellandp.ClaimFinancial.InitialAcctgDate <= DATEADD(
                   DATEADD(mm, DATEDIFF(m, 0, @AsOfMonth + '/1/' + @AsOfYear) + 1, 0)
) AS CededFile ON wellandp.ClaimGeneral.ClaimNo = CededFile.ClaimNo
If the ClaimNo doesn't match, the expense column from table2 will be NULL. So a simple IsNull() should do it. If not, you could use a CASE instead.

    ... SUM(  ISNULL(CCA.ExpPaidCeded , wellandp.ExpPaid ) ) AS ExpPaid

mburk1968Author Commented:
Perhaps I didn't explain myself properly.
There is always a match for ClaimNo and an amount for ExpPaid in table 1 or wellandp.ClaimGeneral

What I want is if ClaimNo exist in Table 2 dbo.Claims_Ceded_Amounts then disregard the amount in table 1 and sum ExpPaidCeded As ExpPaid Else ExpPaid from Table 1
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

That should be exactly what my example does.  

Maybe my explanation was unclear.  If thereis  a matching ClaimNo in table2 then "ExpPaidCeded" will be used. Otherwise, the "ExpPaid" value from table1 is used.
mburk1968Author Commented:
I think I was expecting a Case statement as down the road there might be additional criteria that I will need to compare against to include a possible date range.
Sure you could use a CASE.  Right now it does the same thing as ISNULL(...), but you can add conditions as needed.

... SUM(  
            CASE WHEN CCA.ClaimNoCeded IS NULL THEN wellandp.ExpPaid
                     ELSE CCA.ExpPaidCeded
         ) AS ExpPaid

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now