尝试在对美元金额求和的查询中合并成对的会计期间

Question

我正在苦苦思索如何得到一个查询来对会计期间进行求和，就好像它们是一个值而不是两个值。我们基本上有每个周期，它有一个相应的调整周期(例如4和904 )，我想把它们加起来，因为我的查询将它们作为单独的行返回。我希望904基本上被查询视为4。我需要为每个周期1到12 (以及901到912)执行此操作，因为我只对一个月与另一个月之间是否存在差异感兴趣，而对周期与调整期之间是否存在差异不感兴趣。

SELECT PROJECT_ID, ACCOUNTING_PERIOD, SUM(POSTED_BASE_AMT) as POSTED_AMT
FROM PS_LEDGER
WHERE LEDGER = 'ACTUALS'
AND FISCAL_YEAR = '2019'
AND (PROJECT_ID like 'PG%' OR PROJECT_ID LIKE 'GG%')
AND ACCOUNT BETWEEN '40000' AND '79999'
AND ACCOUNTING_PERIOD <> 999
GROUP BY PROJECT_ID, ACCOUNTING_PERIOD
HAVING SUM(POSTED_BASE_AMT) <> 0
ORDER BY PROJECT_ID, ACCOUNTING_PERIOD;

我的结果如下：

PROJECT_ID             ACCOUNTING PERIOD       POSTED_AMT
GG000000                       4                    -100
GG000000                      904                    100

出于这个查询的目的，我希望它是0

Answer 1

假设您的调整周期始终是901到912，您只需减去900，group by就会将其与原始周期合并。

SELECT PROJECT_ID, 
CASE WHEN ACCOUNTING_PERIOD<900 THEN ACCOUNTING_PERIOD ELSE 900-ACCOUNTING_PERIOD END ACCOUNTING_PERIOD, 
SUM(POSTED_BASE_AMT) as POSTED_AMT
FROM PS_LEDGER
WHERE LEDGER = 'ACTUALS'
AND FISCAL_YEAR = '2019'
AND (PROJECT_ID like 'PG%' OR PROJECT_ID LIKE 'GG%')
AND ACCOUNT BETWEEN '40000' AND '79999'
AND ACCOUNTING_PERIOD <> 999
GROUP BY PROJECT_ID, CASE WHEN ACCOUNTING_PERIOD<900 THEN ACCOUNTING_PERIOD ELSE 900-ACCOUNTING_PERIOD END 
HAVING SUM(POSTED_BASE_AMT) <> 0
ORDER BY PROJECT_ID, CASE WHEN ACCOUNTING_PERIOD<900 THEN ACCOUNTING_PERIOD ELSE 900-ACCOUNTING_PERIOD END;

Answer 2

看起来这就是你想要的：

SELECT PROJECT_ID, 
       ACCOUNTING_PERIOD, 
       SUM(POSTED_BASE_AMT) OVER 
         ( PARTITION BY MOD(Accounting_Period,100)
         ) as POSTED_AMT
FROM PS_LEDGER
WHERE LEDGER = 'ACTUALS'
AND FISCAL_YEAR = '2019'
AND (PROJECT_ID like 'PG%' OR PROJECT_ID LIKE 'GG%')
AND ACCOUNT BETWEEN '40000' AND '79999'
AND ACCOUNTING_PERIOD <> 999;

Answer 3

来自Paul X的解决方案真的很有帮助，但我需要首先运行查询，并将已过帐的基本金额不求和，然后将其放入查询中的嵌套select中，如下所示：

选择PROJECT_ID，ACCOUNTING_PERIOD，SUM( POSTED_BASE_AMT )作为金额来源(SELECT PROJECT_ID，CASE WHEN ACCOUNTING_PERIOD >900 THEN ACCOUNTING_PERIOD - 900 ELSE ACCOUNTING_PERIOD END ACCOUNTING_PERIOD，POSTED_BASE_AMT FROM PS_LEDGER WHERE LEDGER = 'ACTUALS‘ACCOUNTING_PERIOD FISCAL_YEAR = '2019’AND (PROJECT_ID LIKE 'PG%‘或PROJECT_ID like 'GG%') AND ACCOUNT BY PROJECT_ID，ACCOUNTING_PERIOD ) GROUP BY PROJECT_ID，ACCOUNTING_PERIOD

具有按PROJECT_ID、ACCOUNTING_PERIOD排序的SUM(POSTED_BASE_AMT) <> 0；

感谢大家的帮助！