时间戳

首页 > 解决方案 > 动态显示会计年度 - SQL

sql - 动态显示会计年度 - SQL

问题描述

我创建了一个生成当年数据的 Oracle SQL 查询：

SELECT A.ITEMNO, A.DESCRIP, SUM(RD.QTYRETURNED) AS "TOTAL QTY RETURNED"
FROM   RMA R LEFT OUTER JOIN RMA_DETAIL RD ON R.ID=RD.RMA_ID
             LEFT OUTER JOIN ARINVT A ON RD.ARINVT_ID=A.ID
             LEFT OUTER JOIN ARCUSTO C ON C.ID = A.ARCUSTO_ID
WHERE  ((R."CLOSED" IS  NULL ) OR ((R.CLOSED='N' OR R.CLOSED='Y'))) AND
    EXTRACT(YEAR FROM R.RMA_DATE) = EXTRACT(YEAR FROM SYSDATE)
GROUP BY A.ITEMNO, A.DESCRIP
ORDER BY "TOTAL QTY RETURNED" DESC

我需要创建一个返回我们财政年度（12/1 - 11/30）的查询，所以我创建了这个：

SELECT A.ITEMNO, A.DESCRIP, SUM(RD.QTYRETURNED) AS "TOTAL QTY RETURNED"
FROM   RMA R LEFT OUTER JOIN RMA_DETAIL RD ON R.ID=RD.RMA_ID
             LEFT OUTER JOIN ARINVT A ON RD.ARINVT_ID=A.ID
             LEFT OUTER JOIN ARCUSTO C ON C.ID = A.ARCUSTO_ID
WHERE  ((R."CLOSED" IS  NULL ) OR ((R.CLOSED='N' OR R.CLOSED='Y'))) AND
     R.RMA_DATE >= ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -7) AND R.RMA_DATE < 
     ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'),   5)
GROUP BY A.ITEMNO, A.DESCRIP
ORDER BY "TOTAL QTY RETURNED" DESC

这些是用于创建 BI 仪表板的查询。财政年度查询的问题在于，当 sysdate 月份发生变化时，数据也会发生变化。这样做最干净的方法是什么？我只是从 SQL 开始，所以任何指向学习文章或解释的链接都会有很长的路要走。TIA

标签： sqloracle

解决方案

您可以尝试如下查询。它根据您当前所在的月份计算“开始”和“结束”日期

  SELECT A.ITEMNO, A.DESCRIP, SUM (RD.QTYRETURNED) AS "TOTAL QTY RETURNED"
    FROM RMA R
         LEFT OUTER JOIN RMA_DETAIL RD ON R.ID = RD.RMA_ID
         LEFT OUTER JOIN ARINVT A ON RD.ARINVT_ID = A.ID
         LEFT OUTER JOIN ARCUSTO C ON C.ID = A.ARCUSTO_ID
   WHERE     ((R."CLOSED" IS NULL) OR ((R.CLOSED = 'N' OR R.CLOSED = 'Y')))
         AND R.RMA_DATE >=
             TO_DATE (
                    CASE EXTRACT (MONTH FROM SYSDATE)
                        WHEN 12 THEN TO_CHAR (SYSDATE, 'YYYY')
                        ELSE TO_CHAR (ADD_MONTHS (SYSDATE, -12), 'YYYY')
                    END
                 || '-12-01',
                 'YYYY-MM-DD')
         AND R.RMA_DATE <
             TO_DATE (
                    CASE EXTRACT (MONTH FROM SYSDATE)
                        WHEN 12 THEN TO_CHAR (ADD_MONTHS (SYSDATE, 12), 'YYYY')
                        ELSE TO_CHAR (SYSDATE, 'YYYY')
                    END
                 || '-12-01',
                 'YYYY-MM-DD')
GROUP BY A.ITEMNO, A.DESCRIP
ORDER BY "TOTAL QTY RETURNED" DESC

推荐阅读