Oracle SQL Expression to Sum Payments for Six Months based on Selected Month

Question

Basically what I'm trying to do is calculate the average percentage of CreditPayments and the average percentage of CashPayments over a projected period (Lookback 3 months then total all paymnents of the correct type in the previous six months)

So if Average (During Correct Lookback Period) was = 75% Credit & 25% Cash then for my outstanding A/R for that month I could anticapte what % will be Credit & what % will be cash. I'd like to run it for a 12 month period with the lookback changing for each MOS.

Right now my result is always 0, which is obviously wrong. I know the initial inner query is correct; however, the problem happens when the outer query runs. I think the problem lies with the T1. MOS, which I think is limiting the results. Any help would be greatly appreciated.

SELECT
    T1.Facility Facility
    , T1.CustType CustType
    , T1.MOS MOS
    , SUM(CASE WHEN (T1.MOS BETWEEN ADD_MONTHS(T1.MOS,-9) AND (ADD_MONTHS(T1.MOS,-3)-1) THEN T1.CredPmts ELSE 0 END)/SUM(CASE WHEN (T1.MOS BETWEEN ADD_MONTHS(T1.MOS,-9) AND (ADD_MONTHS(T1.MOS,-3)-1) THEN T1.Charges ELSE 0 END)
    , SUM(CASE WHEN (T1.MOS BETWEEN ADD_MONTHS(T1.MOS,-9) AND (ADD_MONTHS(T1.MOS,-3)-1) THEN T1.CashPmts ELSE 0 END)/SUM(CASE WHEN (T1.MOS BETWEEN ADD_MONTHS(T1.MOS,-9) AND (ADD_MONTHS(T1.MOS,-3)-1) THEN T1.Charges ELSE 0 END)
FROM
(
SELECT
    T.FACILITY Facility
    , T.CUSTTYPE CustType
    , trunc(to_date(T.SERVICEDATE,'j'),'MONTH') MOS
    , SUM(CASE WHEN T.TYPE = 'C' THEN T.AMOUNT ELSE 0 END) Charges
    , SUM(CASE WHEN (T.TYPE IN ('P1','62','12','75','P6','23') THEN T.AMOUNT ELSE 0 END CredPmts
    , SUM(CASE WHEN (T.TYPE IN ('92','57','P3','P9','26','39') THEN T.AMOUNT ELSE 0 END CashPmts
FROM TRANSTABLE T
WHERE
    T.FACILITY = '123'
    AND T.SERVICEDATE BETWEEN to_char(ADD_MONTHS(to_date('20120101', 'yyyymmdd'),-9), 'j') AND to_char(to_date('20121231', 'yyyymmdd'), 'j')
GROUP BY
    T.FACILITY
    , T.CUSTTYPE
    , trunc(to_date(T.SERVICEDATE,'j'),'MONTH')
)T1
GROUP BY
    T1.Facility
    , T1.CustType
    , T1.MOS

Could you supply some sample data from `TRANSTABLE`? Or better yet, create a SQL Fiddle example with some data? It looks like the issue is with how you are grouping the data, but it is hard to say for sure. — woemler, Jan 28 '13 at 20:19

score 0 · Answer 1 · answered Jan 28 '13 at 19:49

0

There are mismatched parenthesis in the CASE WHEN clause of the outer query:

WHEN (T1.MOS BETWEEN ADD_MONTHS(T1.MOS,-9) AND (ADD_MONTHS(T1.MOS,-3)-1))

all four places. Can you check it?

answered Jan 28 '13 at 19:49

जलजनक

3,072
2
24
30

Changed and ran again. Still returns zero. Thanks for the quick reply! – RedOctobir Jan 28 '13 at 20:03

Oracle SQL Expression to Sum Payments for Six Months based on Selected Month

1 Answers1