asked on

SQL Sum problem with joins

Hello,

I am having a problem with SQL when I use a sum.

Below is the Syntax that gets the correct sum:

Select

      dbo.fnEatonType_CHC(ECB.ClaimID) as PayerType,
      sum(LPC.UsualAndCustomary) as 'U&C',
      sum(ECB.ResponseTotalAmountPaid) as Total,
      sum(LPC.ResponsePatientPayAmount) as PatientPay
From
      vw_Eaton_ClaimsBase ECB left outer join vw_Reporting_LastPaidClaim LPC on LPC.ClaimID = ECB.ClaimID

However,

if I I do:

Select

      dbo.fnEatonType_CHC(ECB.ClaimID) as PayerType,
      sum(LPC.UsualAndCustomary) as 'U&C',
      sum(ECB.ResponseTotalAmountPaid+LPC.ResponsePatientPayAmount) as Total
From
      vw_Eaton_ClaimsBase ECB left outer join vw_Reporting_LastPaidClaim LPC on LPC.ClaimID = ECB.ClaimID

the second sum (Total) is incorrect (it lowers the ECB.ResponseTotalAmountPaid considerably).

I know it has to do with the join - is there any way to add them together in one sum rather than two sums?

Thanks for any help

slightwv (䄆 Netminder)

Not a SQL Server person but in other databases, if you add a null the result is null.

See if this helps:
sum(nullif(ECB.ResponseTotalAmountPaid,0)+nullif(LPC.ResponsePatientPayAmount,0)) as Total

Michael Graham

ASKER

Thanks for your help.

However, I cannot use that syntax with the version of SQL Sever I am using (2012).

slightwv (䄆 Netminder)

nullif has been around since SQL Server 2008

What isn't working?

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/nullif-transact-sql?view=sql-server-2017

APPLIES TO: SQL Server (starting with 2008)

awking00

Try coalesce -
sum(coalesce(ECB.ResponseTotalAmountPaid,0)+coalesce(LPC.ResponsePatientPayAmount,0)) as Total

Sharath S

try like this.

Select 
      
      dbo.fnEatonType_CHC(ECB.ClaimID) as PayerType,
      sum(LPC.UsualAndCustomary) as 'U&C',
      sum(ECB.ResponseTotalAmountPaid+ISNULL(LPC.ResponsePatientPayAmount,0) as Total
From
      vw_Eaton_ClaimsBase ECB left outer join vw_Reporting_LastPaidClaim LPC on LPC.ClaimID = ECB.ClaimID

Open in new window

Michael Graham

ASKER

IT says LNullIf is not a recognized built in function name.

Thanks

Scott Pletcher

Most often it's best when possible to do the SUMs before you join, something like below. Otherwise it can be tricky from keeping the JOIN(s) from decreasing/increasing the sums.

Select
dbo.fnEatonType_CHC(ECB.ClaimID) as PayerType,
LPC.UsualAndCustomary as 'U&C',
ECB.ResponseTotalAmountPaid+LPC.ResponsePatientPayAmount as Total
From
(Select
ClaimID,
sum(ResponseTotalAmountPaid) as ResponseTotalAmountPaid
From vw_Eaton_ClaimsBase
Group By ClaimID
) as ECB left outer join
(Select
ClaimID,
sum(UsualAndCustomary) as UsualAndCustomary,
sum(ResponsePatientPayAmount) as ResponsePatientPayAmount
From vw_Reporting_LastPaidClaim
Group By ClaimID
) as LPC on LPC.ClaimID = ECB.ClaimID

awking00

sigtwv,
Nullif should have also worked. If the asker copied and pasted your suggestion, it may have failed because of the nullif showing up between the L and the PC and not before.

awking00

>>IT says LNullIf is not a recognized built in function name.<<
As I suspected, it was meant to say Nullif(LPC and not LNullif(PC

slightwv (䄆 Netminder)

>>t may have failed because of the nullif showing up between the L and the PC and not before.

Ah yes, typos!!!

I have corrected it.

Michael Graham

ASKER

Hi Scott,

Your solutions seems to be running the best.

It is my fault I forgot to include that I need to put in the where clause:

ECB.DateFilled >= '2017-01-01'
      and ECB.DateFilled <= '2018-01-01'
      and ECB.InventoryName = 'Codman'
      and ECB.PlanName not like '%Stock%'

Is there any way to build that into the subquery?

Thanks,

Mike

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

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

Start Free Trial

Michael Graham

ASKER

I tested both of the queries. The one from Scott and the the original from Slight both worked out.

I am not great at doing these credits - you guys all helped and I learned something new with the ISNULL function.

Thank you guys so much.

Mike