Year,NextYear,Quartere以財政年度為基礎的下一季度

我需要帶上這條 SQL 的結果，顯示從 6 月 1 日開始的“MY FISCAL YEAR”的所有資料，以及其他 SQL 來顯示“NEXT FISCAL YEAR”的所有資料，因為我是根據資料庫日歷制作的.

順便說一句，上一個財政年度于 5 月 31 日結束

用戶將通過 API 請求發送帶有 sysdate 的請求。例如，如果用戶在 2022 年 10 月發送請求，我的 SQL 的結果需要是我的財政年度（2022-2023）的所有專案，正如我所說的從 6 月 1 日開始。

我的 SQL 正在根據資料庫日歷回傳 2022 年的所有專案

select
 dgl.LABEL                as goLiveName  
,dgl.GOLIVE_DATE_ACTUAL   as planningCurrent
,dgl.GOLIVE_DATE_PLANNED  as planningBaseline
,dgl.EFFECTIVE_START_DATE as effectiveStartDate
,dgl.EFFECTIVE_END_DATE   as effectiveEndDate
from   DATALAKE.DWL_GOLIVE dgl
      ,DATALAKE.DWB_PROJECT dp
where dgl.PROJECT_ID = dp.PROJECT_ID
and   to_char(dgl.GOLIVE_DATE_PLANNED, 'YYYY') = to_char(SYSDATE, 'YYYY') 
AND   ( :year = 'true')

這是我的 SQL，它根據資料庫日歷顯示 2023 年的所有專案，但正如我所說，在這個 SQL 中，我需要下一個財政年度 2023-2024 的所有專案

select
 dgl.LABEL                as goLiveName  
,dgl.GOLIVE_DATE_ACTUAL   as planningCurrent
,dgl.GOLIVE_DATE_PLANNED  as planningBaseline
,dgl.EFFECTIVE_START_DATE as effectiveStartDate
,dgl.EFFECTIVE_END_DATE   as effectiveEndDate
from   DATALAKE.DWL_GOLIVE dgl
      ,DATALAKE.DWB_PROJECT dp
where dgl.PROJECT_ID = dp.PROJECT_ID
and   to_char(dgl.GOLIVE_DATE_PLANNED, 'YYYY') = to_char (add_months (SYSDATE, 12), 'YYYY' )
AND   ( :nyear = 'true')

同樣，我將需要基于我提到的財政年度的實際季度和下一季度。因為我是根據資料庫季度做的。現在的資料庫季度是 Q2。但在我的日歷財政年度中，需要 Q1 才能顯示 6 月的結果。

select
 dgl.LABEL                as goLiveName  
,dgl.GOLIVE_DATE_ACTUAL   as planningCurrent
,dgl.GOLIVE_DATE_PLANNED  as planningBaseline
,dgl.EFFECTIVE_START_DATE as effectiveStartDate
,dgl.EFFECTIVE_END_DATE   as effectiveEndDate
from   DATALAKE.DWL_GOLIVE dgl
      ,DATALAKE.DWB_PROJECT dp
where dgl.PROJECT_ID = dp.PROJECT_ID
and   to_char(dgl.GOLIVE_DATE_PLANNED, 'yyyy-q') = to_char(SYSDATE, 'yyyy-q')
AND   ( :quarter = 'true')

下個季度基于資料庫日歷

select
 dgl.LABEL                as goLiveName  
,dgl.GOLIVE_DATE_ACTUAL   as planningCurrent
,dgl.GOLIVE_DATE_PLANNED  as planningBaseline
,dgl.EFFECTIVE_START_DATE as effectiveStartDate
,dgl.EFFECTIVE_END_DATE   as effectiveEndDate
from   DATALAKE.DWL_GOLIVE dgl
      ,DATALAKE.DWB_PROJECT dp
where dgl.PROJECT_ID = dp.PROJECT_ID
and   dgl.GOLIVE_DATE_PLANNED >= add_months(trunc(sysdate, 'Q'), 3)
and   dgl.GOLIVE_DATE_PLANNED <  add_months(trunc(sysdate, 'Q'), 6)
--and   to_char (dgl.GOLIVE_DATE_PLANNED, 'yyyy - q' ) >= to_char (add_months (to_date('12/08/22', 'dd/mm/yy'), 3), 'yyyy - q' ) -- EXEMPLO
--and   to_char (dgl.GOLIVE_DATE_PLANNED, 'yyyy - q' ) < to_char (add_months (to_date('12/08/22', 'dd/mm/yy'), 6), 'yyyy - q' )
AND   ( :nquarter = 'true')

請問各位，有誰能幫幫我嗎？

非常感謝。

uj5u.com熱心網友回復：

本財政年度（2022-06-01 - 2023-05-30）：

SELECT dgl.LABEL                as goLiveName  
      ,dgl.GOLIVE_DATE_ACTUAL   as planningCurrent
      ,dgl.GOLIVE_DATE_PLANNED  as planningBaseline
      ,dgl.EFFECTIVE_START_DATE as effectiveStartDate
      ,dgl.EFFECTIVE_END_DATE   as effectiveEndDate
      ,TO_CHAR(ADD_MONTHS(dgl.GOLIVE_DATE_PLANNED, -5), 'YYYY') AS fiscalyear
FROM   DATALAKE.DWL_GOLIVE dgl
       INNER JOIN DATALAKE.DWB_PROJECT dp
       ON (dgl.PROJECT_ID = dp.PROJECT_ID)
WHERE  dgl.GOLIVE_DATE_PLANNED
         >= ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE, -5), 'YY'),  5)
AND    dgl.GOLIVE_DATE_PLANNED
         <  ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE, -5), 'YY'), 17)
AND    ( :year = 'true')

下一財年（2023-06-01 - 2024-05-30）：

SELECT dgl.LABEL                as goLiveName  
      ,dgl.GOLIVE_DATE_ACTUAL   as planningCurrent
      ,dgl.GOLIVE_DATE_PLANNED  as planningBaseline
      ,dgl.EFFECTIVE_START_DATE as effectiveStartDate
      ,dgl.EFFECTIVE_END_DATE   as effectiveEndDate
      ,TO_CHAR(ADD_MONTHS(dgl.GOLIVE_DATE_PLANNED, -5), 'YYYY') AS fiscalyear
FROM   DATALAKE.DWL_GOLIVE dgl
       INNER JOIN DATALAKE.DWB_PROJECT dp
       ON (dgl.PROJECT_ID = dp.PROJECT_ID)
WHERE  dgl.GOLIVE_DATE_PLANNED
         >= ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE, -5), 'YY'), 17)
AND    dgl.GOLIVE_DATE_PLANNED
         <  ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE, -5), 'YY'), 29)
AND    ( :nyear = 'true')

本季度（2022-06-01 - 2022-08-31 - 2022Q1）：

SELECT dgl.LABEL                as goLiveName  
      ,dgl.GOLIVE_DATE_ACTUAL   as planningCurrent
      ,dgl.GOLIVE_DATE_PLANNED  as planningBaseline
      ,dgl.EFFECTIVE_START_DATE as effectiveStartDate
      ,dgl.EFFECTIVE_END_DATE   as effectiveEndDate
      ,TO_CHAR(ADD_MONTHS(dgl.GOLIVE_DATE_PLANNED, -5), 'YYYY-Q') AS quarter
FROM   DATALAKE.DWL_GOLIVE dgl
       INNER JOIN DATALAKE.DWB_PROJECT dp
       ON (dgl.PROJECT_ID = dp.PROJECT_ID)
WHERE  dgl.GOLIVE_DATE_PLANNED
         >= ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE, -2), 'Q'), 2)
AND    dgl.GOLIVE_DATE_PLANNED
         <  ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE, -2), 'Q'), 5)
AND    ( :quarter = 'true')

下一季度（2022-09-01 - 2022-11-30 - 2022Q2）：

SELECT dgl.LABEL                as goLiveName  
      ,dgl.GOLIVE_DATE_ACTUAL   as planningCurrent
      ,dgl.GOLIVE_DATE_PLANNED  as planningBaseline
      ,dgl.EFFECTIVE_START_DATE as effectiveStartDate
      ,dgl.EFFECTIVE_END_DATE   as effectiveEndDate
      ,TO_CHAR(ADD_MONTHS(dgl.GOLIVE_DATE_PLANNED, -5), 'YYYY-Q') AS quarter
FROM   DATALAKE.DWL_GOLIVE dgl
       INNER JOIN DATALAKE.DWB_PROJECT dp
       ON (dgl.PROJECT_ID = dp.PROJECT_ID)
WHERE  dgl.GOLIVE_DATE_PLANNED
         >= ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE, -2), 'Q'), 5)
AND    dgl.GOLIVE_DATE_PLANNED
         <  ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE, -2), 'Q'), 8)
AND    ( :nquarter = 'true')

標籤：sql 甲骨文 plsql

上一篇：在NUMBER(6,-2)格式的oracle中可以存盤的最大數量是多少？

下一篇：基于百分比的值累加。Oracle