I have table with all charges and payment the charges are 'c' and payments r, f, d. The charges have positive a negative amount and the payments positive a negative amount as well.
When I query the table for just one student and add the amount I have the right balance, something is wrong with the sum function, please advise
The sum function for the charges is the issue.
here is the CTE
;With cte_charges ( people_id, id_number, charges, debits)
as(
select C.people_org_code_id, c.PEOPLE_ORG_ID
,sum(case when c.CHARGE_CREDIT_TYPE in ('C') then C.BALANCE_AMOUNT ELSE 0 end)
,sum(case when c.CHARGE_CREDIT_TYPE in ('F','R','D') then c.balance_amount else 0 end)
from chargecredit C
LEFT JOIN CODE_CHARGECREDIT A ON C.charge_credit_code = A.CODE_VALUE_KEY
where C.VOID_FLAG ='N' and C.PEOPLE_ORG_CODE ='P'
AND C.CHARGE_CREDIT_CODE not in ('ANTIFINAID', 'BEGINBAL') AND c.ENTRY_DATE <='2020-07-16 00:00:00.000'
Group by C.people_org_code_id,c.PEOPLE_ORG_ID
)
Select * from cte_charges
******* TABLE
CREATE TABLE [dbo].[CHARGECREDIT](
[PEOPLE_ORG_CODE_ID] [nvarchar](10) NOT NULL,
[PEOPLE_ORG_CODE] [nvarchar](9) NOT NULL,
[CHARGE_CREDIT_CODE] [nvarchar](10) NOT NULL,
[CHARGE_CREDIT_TYPE] [nvarchar](1) NULL,
[AMOUNT] [numeric](18, 6) NULL,
[BALANCE_AMOUNT] [numeric](18, 6) NULL,
[VOID_FLAG] [nvarchar](1) NULL,
[ENTRY_DATE] [datetime] NULL,
INSERT INTO CHARGECREDIT VALUES
(
'P000122226',
‘P’,
‘APPLFEE’,
‘C’,
'15.00',
’15.00’,
'N'
‘2009-05-06 00:00:00.000,
),
(
'P000108195',
‘P’,
‘PKDECALSTU’,
‘C’,
'1500.00',
’1500.00’,
'N'
‘2010-05-06 00:00:00.000),
(
'P000108195',
‘P’,
‘LIONSHOPCH’,
‘C’,
'3500.00',
’3500.00’,
'N'
‘2010-05-06 00:00:00.000),
(
'P000108195',
‘P’,
‘PELL’,
‘F’,
'500.00',
’500.00’,
'N'
‘2010-05-06 00:00:00.000),
(
'P000108195',
‘P’,
‘DLSUB’,
‘F’,
'600.00',
’600.00’,
'N'
‘2010-05-06 00:00:00.000),
(
'P000148448',
‘P’,
‘LIONSHOPCH’,
‘C’,
'3500.00',
’3500.00’,
'N'
‘2020-05-06 00:00:00.000),
(
'P000148448',
‘P’,
‘LIONSHOPCH’,
‘C’,
'3500.00',
’3500.00’,
'N'
‘2020-05-06 00:00:00.000),
(
'P000148448',
‘P’,
‘KEYDEPOSIT’,
‘C’,
'50.00',
’50.00’,
'N'
‘2020-05-06 00:00:00.000),
(
'P000148448',
‘P’,
‘PYMT’,
‘C’,
'150.00',
’150.00’,
'N'
‘2020-05-25 00:00:00.000),
August 11, 2020 at 6:29 am
I couldn't make your code work.
so I had to adapt it.
Also the table Code_Charge_Credit is missing, so I removed it.
CREATE TABLE dbo.CHARGECREDIT( PEOPLE_ORG_CODE_ID NVARCHAR(10) NOT NULL,
PEOPLE_ORG_CODE NVARCHAR(9) NOT NULL,
CHARGE_CREDIT_CODE NVARCHAR(10) NOT NULL,
CHARGE_CREDIT_TYPE NVARCHAR(1) NULL,
AMOUNT NUMERIC(18, 6) NULL,
BALANCE_AMOUNT NUMERIC(18, 6) NULL,
VOID_FLAG NVARCHAR(1) NULL,
ENTRY_DATE DATETIME NULL );
INSERT INTO CHARGECREDIT
VALUES( 'P000122226', 'P', 'APPLFEE', 'C', '15.00', '15.00', 'N', '2009-05-06 00:00:00.000' ), ( 'P000108195', 'P', 'PKDECALSTU', 'C', '1500.00', '1500.00', 'N', '2010-05-06 00:00:00.000' ), ( 'P000108195', 'P', 'LIONSHOPCH', 'C', '3500.00', '3500.00', 'N', '2010-05-06 00:00:00.000' ), ( 'P000108195', 'P', 'PELL', 'F', '500.00', '500.00', 'N', '2010-05-06 00:00:00.000' ), ( 'P000108195', 'P', 'DLSUB', 'F', '600.00', '600.00', 'N', '2010-05-06 00:00:00.000' ), ( 'P000148448', 'P', 'LIONSHOPCH', 'C', '3500.00', '3500.00', 'N', '2020-05-06 00:00:00.000' ), ( 'P000148448', 'P', 'LIONSHOPCH', 'C', '3500.00', '3500.00', 'N', '2020-05-06 00:00:00.000' ), ( 'P000148448', 'P', 'KEYDEPOSIT', 'C', '50.00', '50.00', 'N', '2020-05-06 00:00:00.000' ), ( 'P000148448', 'P', 'PYMT', 'C', '150.00', '150.00', 'N', '2020-05-25 00:00:00.000' );
GO
SELECT *
FROM dbo.CHARGECREDIT;
GO
WITH cte_charges(people_id,
charges,
debits)
AS (SELECT C.people_org_code_id,
SUM(CASE
WHEN c.CHARGE_CREDIT_TYPE IN('C') THEN C.BALANCE_AMOUNT
ELSE 0
END),
SUM(CASE
WHEN c.CHARGE_CREDIT_TYPE IN('F', 'R', 'D') THEN c.balance_amount
ELSE 0
END)
FROM chargecredit C
WHERE C.VOID_FLAG = 'N'
AND C.PEOPLE_ORG_CODE = 'P'
AND C.CHARGE_CREDIT_CODE NOT IN( 'ANTIFINAID', 'BEGINBAL' )
AND c.ENTRY_DATE <= '2020-07-16 00:00:00.000'
GROUP BY C.people_org_code_id)
SELECT *
FROM cte_charges;
And got this result
P000122226 P APPLFEE C 15.000000 15.000000 N 2009-05-06 00:00:00.000
P000108195 P PKDECALSTU C 1500.000000 1500.000000 N 2010-05-06 00:00:00.000
P000108195 P LIONSHOPCH C 3500.000000 3500.000000 N 2010-05-06 00:00:00.000
P000108195 P PELL F 500.000000 500.000000 N 2010-05-06 00:00:00.000
P000108195 P DLSUB F 600.000000 600.000000 N 2010-05-06 00:00:00.000
P000148448 P LIONSHOPCH C 3500.000000 3500.000000 N 2020-05-06 00:00:00.000
P000148448 P LIONSHOPCH C 3500.000000 3500.000000 N 2020-05-06 00:00:00.000
P000148448 P KEYDEPOSIT C 50.000000 50.000000 N 2020-05-06 00:00:00.000
P000148448 P PYMT C 150.000000 150.000000 N 2020-05-25 00:00:00.000
people_id charges debits
P000108195 5000.000000 1100.000000
P000122226 15.000000 0.000000
P000148448 7200.000000 0.000000
Which looks okay.
So can you provide data that reproduces your issue.
Frank
Frank
August 11, 2020 at 12:00 pm
Frank's answer above removes the reference to the CODE_CHARGECREDIT table and removes c.PEOPLE_ORG_ID (which is non-existent) from the GROUP BY. Idk if that makes sense. Fixing the (many) errors and changing the (non-existent) column name c.PEOPLE_ORG_ID to (existent) c.PEOPLE_ORG_CODE and the query executes and seems to sum correctly.
;With cte_charges ( people_id, id_number, charges, debits)
as (
select
C.people_org_code_id, c.PEOPLE_ORG_CODE
,sum(case when c.CHARGE_CREDIT_TYPE in ('C') then C.BALANCE_AMOUNT ELSE 0 end)
,sum(case when c.CHARGE_CREDIT_TYPE in ('F','R','D') then c.balance_amount else 0 end)
from
[CHARGECREDIT] C
LEFT JOIN
CODE_CHARGECREDIT A ON C.charge_credit_code = A.CODE_VALUE_KEY
where
C.VOID_FLAG ='N' and C.PEOPLE_ORG_CODE ='P'
AND C.CHARGE_CREDIT_CODE not in ('ANTIFINAID', 'BEGINBAL') AND c.ENTRY_DATE <='2020-07-16 00:00:00.000'
Group by
C.people_org_code_id,c.PEOPLE_ORG_CODE)
Select * from cte_charges;
people_idid_numbercharges debits
P000108195P 5000.000000 1100.000000
P000122226P 15.000000 0.000000
P000148448P 7200.000000 0.000000
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Thank you, it works!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply