Results contain multiples of balances when multiple tables are joined
-
I have 4 tables that contain account information concerning the account, charges, charge details and payments on the account.
For simplicity I have created the 4 tables below and the data that is necessary to see the problem I am having. I am a .Net developer that has been handed the SQL job of retrieving data. I like what I've been seeing with SQL however the cartesian product is giving me fits.
Scripts for creating the tables.
create table test_acc (account varchar(15) not null ,
code varchar(5)
constraint [PK_test_acc] primary key clustered
(
account)
)
create table test_charges (
charge_number int identity(1,1) not null,
account varchar(15) not null,
cdm varchar(10) not null,
qty int not null
constraint [PK_test_charges] primary key clustered
(
charge_number
)
)
create table test_charges_detail (
charge_number int not null,
cpt4 varchar(5) not null,
amount numeric(10,2)
)
create table test_payments (
account varchar(15) not null,
amt_paid numeric (10,2) not null,
contractual numeric (10,2) not null,
write_off numeric (10,2) not null,
write_off_code varchar(10)
)
Code for loading the data
insert into test_acc (account, code)
select 'c2942020', 'NEW'
insert into test_charges ( account, cdm, qty)
select 'c2942020', '5556633', 2 union all
select 'c2942020', '5557744', 1
insert into test_charges_detail ( charge_number, cpt4, amount)
select 1, '81111', 5.00 union all
select 2, '82222', 3.00 union all
select 2, '83333', 2.00
insert into test_payments (account, amt_paid, contractual, write_off, write_off_code)
select 'c2942020', 3.00, 2.00, 0.00, null union all
select 'c2942020', 3.00, 3.00, 4.00, 'SBal'
I need to be able to get the charges from the test_charges by account, linked to the test_charges_details for the charge amount. The amt_paid, contractual, write_off and write_off_code from the test_payments table.
After several attempts the below is the best solution that I can obtain, and it shows the problem I'm having.
select ta.account, sum(qty*amount) as [Total Charges],sum(amt_paid) as [Amt Paid],
sum(contractual) as [contractual] , sum (write_off) as [write off], write_off_code
from test_acc ta
inner join test_charges tc on tc.account = ta.account
inner join test_charges_detail tcd on tcd.charge_number = tc.charge_number
inner join test_payments tp on tp.account = ta.account
group by ta.account, write_off_code
giving the following results
accountTotal ChargesAmt Paidcontractualwrite offwrite_off_code
c294202015.009.006.000.00NULL
c294202015.009.009.0012.00SBal
when the results should be
accountTotal ChargesAmt Paidcontractualwrite offwrite_off_code
c2942020 15.00 6.00 5.00 4.00 SBal
I would appreciate any links to articles or help in this matter.
Thank you.
David K.
-
I think this give the correct results, but I'm not too clear on how to handle multiple test_payments rows with non-null write_off_codes for a single account.
with charges as (select ta.account, sum(qty*amount) as [Total Charges]
from test_acc ta
inner join test_charges tc on tc.account = ta.account
inner join test_charges_detail tcd on tcd.charge_number = tc.charge_number
group by ta.account),
payments as (
select account,
sum(amt_paid) as amt_paid,
sum(contractual) as contractual,
sum(write_off) as write_off,
max(write_off_code) as write_off_code
from test_payments
group by account)
select c.account, c.[Total Charges],
p.amt_paid as [Amt Paid],
p.contractual as [contractual],
p.write_off as [write off],
p.write_off_code
from charges c
inner join payments p on p.account = c.account;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi David,
The problem seems to be that you are grouping by the write off code in the #test_payments table. This forces the query to give a separate row for the same account # if the write off code changes. A null write off code is still a separate grouping than the one with a value.
There are ways around this problem, but we'd need to know this business rule for how you want this to appear. If you only want a single line per account, and want it summarizing all the payments, how do we pick the appropriate write off code to show?
If you don't care about that field (recommended since showing it would likely be misleading), you can just remove it from the select and group by.
If it has to be there you need to give us a rule to explain which write_off_code to choose when an account has 2 payments with different ones.
Another option would be to "pivot" your data by having a column for each write_off_code's total amount.
-
You beat me to it, but I'm posting mine anyhow, because there are some points that I wanted to address.
WITH Charges AS (
SELECT Account, Sum(qty*amount) AS Total_Charges
FROM test_charges AS tc
INNER JOIN test_charges_detail AS tcd ON tc.charge_number = tcd.charge_number
GROUP BY Account
)
, Payments AS (
SELECT Account, Sum(amt_paid) AS Amt_Paid, Sum(write_off) AS write_off, Max(write_off_code) AS write_off_code
-- Your description and sample data/results don't give enough information about what to do with multiple non-null write offs.
FROM test_payments
GROUP BY account
)
SELECT c.account, total_charges, Amt_Paid, write_off, write_off_code
FROM test_acc AS a
LEFT OUTER JOIN Charges AS c
on a.account = c.account
LEFT OUTER JOIN Payments AS p
ON a.account = p.account
WHERE IsNull(c.Account, p.Account) IS NOT NULL
The problem is that you were performing the joins and then trying to summarize and individual charges and individual payments are not correlated, so you need to summarize them separately before performing the join.
The main point that I wanted to make is that in a given time period, you may not have any charges and/or payments, so you need to do a left outer join to the main account table. The where clause filters out records that have neither charges nor payments. You can always leave this off.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
As fate would have it I asked about the multiple write_off_codes, and there can be several. I ran your code on the tables and got exactly what I was looking for, or so I thought. I added another payment with a different write_off_code and again I am lost
Added new record to payments with a differenct write_off_code
insert into test_payments (account, amt_paid, contractual, write_off, write_off_code)select 'c2942020', 3.00, 3.00, 4.00, 'pBal
got the following
accountTotal ChargesAmt Paidcontractualwrite offwrite_off_code
c294202015.006.005.004.00sBal
I'm sorry that I didn't have all the facts on the front end.
-
Thanks for the reply. The main purpose of the query is to find accounts that have a write_off amount. The application will not allow the users to write off an amount without a reason. Any account that does not have a write off amount should not be included in the results. After finding that I could have multiple write off's with different reasons, I have to go back to the drawing board. It is becoming increasingly obvious to me that I am not about to do this without writing a program.
Thanks to all of the individual that help the lost souls that post here, and I am grateful to all that have helped in this.
David K.
-
How many different possible write-off amounts are there? And are they fairly static?
-
There are 15 write off codes and they are defined in a table. One requires a comment because it is OTHER. So they are pretty static.
thanks.
-
One way to accomplish this is to do a crosstab or Pivot on write_off_code.
I just built it in to Mark's code from above
create table test_acc (account varchar(15) not null ,
code varchar(5)
constraint [PK_test_acc] primary key clustered
(
account)
)
create table test_charges (
charge_number int identity(1,1) not null,
account varchar(15) not null,
cdm varchar(10) not null,
qty int not null
constraint [PK_test_charges] primary key clustered
(
charge_number
)
)
create table test_charges_detail (
charge_number int not null,
cpt4 varchar(5) not null,
amount numeric(10,2)
)
create table test_payments (
account varchar(15) not null,
amt_paid numeric (10,2) not null,
contractual numeric (10,2) not null,
write_off numeric (10,2) not null,
write_off_code varchar(10)
)
insert into test_acc (account, code)
select 'c2942020', 'NEW'
insert into test_charges ( account, cdm, qty)
select 'c2942020', '5556633', 2 union all
select 'c2942020', '5557744', 1
insert into test_charges_detail ( charge_number, cpt4, amount)
select 1, '81111', 5.00 union all
select 2, '82222', 3.00 union all
select 2, '83333', 2.00
insert into test_payments (account, amt_paid, contractual, write_off, write_off_code)
select 'c2942020', 3.00, 2.00, 0.00, null union all
select 'c2942020', 3.00, 3.00, 4.00, 'SBal'
insert into test_payments (account, amt_paid, contractual, write_off, write_off_code)
select 'c2942020', 3.00, 3.00, 2.00, 'pBal'
;
with charges as (
select ta.account, sum(qty*amount) as [Total Charges]
from test_acc ta
inner join test_charges tc on tc.account = ta.account
inner join test_charges_detail tcd on tcd.charge_number = tc.charge_number
group by ta.account),
payments as (
select account,
sum(amt_paid) as amt_paid,
sum(contractual) as contractual,
sum(write_off) as write_off,
(write_off_code) as write_off_code
from test_payments
where write_off>0
group by account, write_off_code)
select account,[Total Charges],[Amt Paid],contractual,[pBal],[SBal],[pBal]+[SBal] AS [Total Write Off]
from
(
select c.account, c.[Total Charges],
p.amt_paid as [Amt Paid],
p.contractual as [contractual],
p.write_off as [write off],
p.write_off_code
from charges c
inner join payments p on p.account = c.account) derived
PIVOT
(SUM([write off])
FOR write_off_code IN ([pBal],[SBal])) AS pvt
Obviously I just added the two sample write_off_code's. You would have to include the other possible ones to get them subtotaled out. I also include a write_off>0 in the CTE since you said you wanted to look only at accounts with write offs. If a negative write off is possible, that would have to change to <>.
The other options are to just arbitrarily pick a code to display and not group by it, or to not even show the code and just sum all the write offs.
-
Wow, after looking at the results of your modification of Mark's code. I believe this is exactly what is needed. the 15 codes are not unmanagable as this could be placed in a view and the dates changed as necessary.
Thanks so much. I'm upside down with joy.
David
:hehe:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply