question

Hi All,

I'm struggling with the below code. i'm trying to work out the total balance of a folio by using its total charges and total payments. i'm almost there, however with the below code the payments is incorrect, it seems to be multiplying the total sum of payments by the number of entries in charges. i'm guessing this is because I've connected the payments and charges, which I needed to do to filter out the checkin date which is only on pms sales. I can't join the tables because there may be a folio with payments but then no charges and vice versa.

The issue I am having is that when running the sql it is multiplying the sum of p1.amount by the number of entries in VR_PMS_SALES. eg folio 4 is returning as 165 instead of 55.

example tables, sql query and desired outcome below.

any help would be greatly apricated

thank you

SELECT DISTINCT 'PMS AD' AS APP,
    FOLIO_ID,
       SUM(TOTAL_CHARGES) as TOTAL_CHARGES,
       SUM(TOTAL_PAYMENTS) as TOTAL_PAYMENTS
FROM ((SELECT DISTINCT P1.FOLIO_ID AS FOLIO_ID, SUM(P1.CHARGE_CODE_AMOUNT) AS TOTAL_CHARGES, 0 AS TOTAL_PAYMENTS
       FROM DEV.VR_PMS_SALES P1
       WHERE P1.CHARGE_CODE_AMOUNT <> 0 AND
             P1.ITEM_OPERATING_DAY IS NOT NULL
        AND P1.ITEM_OPERATING_DAY <= '03-DEC-2014'
        AND P1.CHECKIN_DATE <= '03-DEC-2014'
       GROUP BY P1.FOLIO_ID
      ) UNION ALL
      (SELECT DISTINCT P2.FOLIO_ID AS FOLIO_ID, 0 AS TOTAL_CHARGES, SUM(P2.AMOUNT) AS TOTAL_PAYMENTS
       FROM DEV.VR_PMS_PAYMENTS P2,
    DEV.VR_PMS_SALES P3
       WHERE P2.FOLIO_ID = P3.FOLIO_ID
    AND P2.AMOUNT <> 0
    AND P2.PMS_OPERATING_DAY <= '03-DEC-2014'
    AND P3.CHECKIN_DATE <= '03-DEC-2014'
       GROUP BY P2.FOLIO_ID
      )
     ) F
GROUP BY FOLIO_ID

VR_PMS_SALES FOLIO_ID CHARGE_CODE_AMOUNT ITEM_OPERATING_DAY CHECKIN_DATE 1 5 01-Nov-14 01-Dec-14 1 5 02-Nov-14 01-Dec-14 1 10 03-Nov-14 01-Dec-14 2 1 15-Nov-14 2 20 16-Nov-14 4 5 01-Oct-14 02-Dec-14 4 15 03-Oct-14 02-Dec-14 4 35 06-Oct-14 02-Dec-14 VR_PMS_PAYMENTS FOLIO_ID AMOUNT PMS_OPERATING_DAY 1 20 01-Dec-14 2 50 16-Nov-14 3 100 01-Dec-14 4 40 01-Nov-14 4 15 01-Nov-14 Desired Outcome APP FOLIO_ID TOTAL_CHARGES TOTAL_PAYMENTS PMS ADD 1 20 20 PMS ADD 3 0 100 PMS ADD 4 55 55