Sum function in CTE is not calculating the right balance

  • Ysa

    Mr or Mrs. 500

    Points: 528

    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),

  • flouage

    Newbie

    Points: 2

    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

  • Ysa

    Mr or Mrs. 500

    Points: 528

    Hi

     

    Is not working I have a big data

    Attachments:
    You must be logged in to view attached files.
  • Steve Collins

    Ten Centuries

    Points: 1083

    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_id	id_number	charges	            debits
    P000108195 P 5000.000000 1100.000000
    P000122226 P 15.000000 0.000000
    P000148448 P 7200.000000 0.000000

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Ysa

    Mr or Mrs. 500

    Points: 528

    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