We’ve been asked to run an SQL query where the support people are interested in rows that meet two very similar criteria and they want the sum of one set of rows minus the sum of the other set of rows.
They want the sum of all values for each unique IDENT, DATE_POST and DATE_VALUE combination where XCRED = ‘X’ minus the sum of all values for the same IDENT, DATE_POST, DATE_VALUE combination where XCRED = ‘ ‘.
XCRED of ‘X’ represents a credit value and XCRED of ’ ‘ represents a debit value
I can get the individual sums for the different XCRED types and I can get a combined set of data showing both types of XCRED intermingled, but I’m stumped on trying to get a total for subtracting one figure from the other. I could extract one set of data and stick in into a temp table, but I’m sure there must be a way of doing it in a single SQL statement. Does anybody care to take up the challenge? :)
SUM(TAB3.A_AMOUNT) AS TOTAL where this = Sum(XCRED = ‘X’) – Sum(XCRED = ‘ ‘)
INNER JOIN TAB2
ON TAB1.CLIENT = TAB2.CLIENT
AND TAB1.IDENT = TAB2.IDENT
AND TAB2.SETT_TYPE = '002'
AND TAB2.XDELETE = 'P'
INNER JOIN TAB3
ON TAB2.CLIENT = TAB3.CLIENT
AND TAB2.IDENT = TAB3.IDENT
AND TAB2.SETTM_DATE = TAB3.DATE_POST
AND TAB2.SETT_TYPE = TAB3.SETTL_TYPE
AND TAB3.CRUSR = 'TB_BATCH'
AND TAB3.XCRED IN (' ', 'X')
WHERE TAB1.CLIENT = 'CCC'
AND TAB1.VALID_UNTIL = '99991231235959'
AND TAB1.PRODINT = 'TYPE13'
AND TAB1.STATUS = '30'
GROUP BY TAB1.IDENT, TAB3.DATE_POST, TAB3.DATE_VALUE
ORDER BY TAB1.IDENT, TAB3.DATE_POST, TAB3.DATE_VALUE
FOR READ ONLY WITH UR;