年度，下一季度，下一季度，基于一个财政年度

Question

我需要将此SQL显示6月1日开始的“我的财政年度”的所有数据的结果和其他SQL的结果显示到“下一个财政年度”的所有数据，因为我是根据数据库日历制作的。

顺便说一句，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')

​

​

拜托，伙计们，有人能帮我吗？

非常感谢。

Answer 1

本财政年度(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')