根据选定月份总计六个月付款的Oracle SQL表达式

Question

基本上，我要做的是计算预测期间CreditPayments的平均百分比和CashPayments的平均百分比(回顾3个月，然后计算前6个月所有正确类型的付款的总和)

因此，如果平均值(在正确的回顾期间)是= 75%的信用和25%的现金，那么对于我当月的未偿还应收，我可以计算出什么%将是信用&什么%将是现金。我想运行它12个月，每个MOS的回看都会改变。

现在我的结果总是0，这显然是错误的。我知道最初的内部查询是正确的；但是，当外部查询运行时，问题就出现了。我认为问题出在T1上。MOS，我认为这限制了结果。任何帮助都将不胜感激。

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

Answer 1

外部查询的CASE WHEN子句中存在不匹配的括号：

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

所有四个地方。你能检查一下吗？