Year, Next Year, Quarter e Next Quarter based in a FISCAL YEAR-CodePudding

I need to bring the results of this SQL showing all the data of "MY FISCAL YEAR" that started in 1st june, and other SQL to showing all the data of the "NEXT FISCAL YEAR", because I made it based on the database calendar.

btw, the last fiscal year ended in 31 MAY

The user will sent the request with the sysdate by an API request. For example, if a user sent a request in october 2022 the result of my SQL need to be all projects of my fiscal year (2022-2023) as I said that started 1st june.

My SQL is returning all projects in 2022 based on the database calendar

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')

This is my SQL that showing all projects in 2023 based on the database calendar, but as I said, in this SQL, I need all the projects of my Next Fiscal Year 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')

In the same way, I will need the ACTUAL QUARTER and the NEXT QUARTER based on my FISCAL YEAR that I mentioned. because I did it based on the database quarter. and the database quarter now is Q2. But in my calendar fiscal year need to be Q1 to show the result from June.

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')

The next quarter base on the data base calendar

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')

Please guys, anyone can help me ?

Thank you very much.

CodePudding user response：

This fiscal year (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')

Next fiscal year (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')

Current quarter (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')

Next quarter (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')