asked on

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,
                                                              -1,
                                                              DATEADD(mm,
                                                              DATEDIFF(m, 0,
                                                              @AsOfMonth
                                                              + '/1/'
                                                              + @AsOfYear) + 1,
                                                              0))                                                               
                                          GROUP BY  ClaimNo
                                        ) AS CededFile ON wellandp.ClaimGeneral.ClaimNo = CededFile.ClaimNo

Open in new window

TempDBA

(
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(
DAY,
-1,
DATEADD(mm, DATEDIFF(m, 0, @AsOfMonth + '/1/' + @AsOfYear) + 1, 0)
)
GROUP BY
wellandp.ClaimFinancial.ClaimNo
) AS CededFile ON wellandp.ClaimGeneral.ClaimNo = CededFile.ClaimNo

_agx_

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

mburk1968

ASKER

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

_agx_

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.

mburk1968

ASKER

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.

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial