I don't have much experience, and I'm having trouble solving this.
I need to bring the results of this SQL showing the data of a "NEXT QUARTER" based on the "SYSDATE" date that will be sent by an API request.
The quarter actual in my db is: Q2.
According to this query that I ran below, it seems that everything is working as expected, because I'm sending the request on today's date, in the current quarter, so this SQL is bringing me the result of the next quarter's projects (APPARENTLY)
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
where
and to_char(dgl.GOLIVE_DATE_PLANNED, 'yyyy - q') = to_char(add_months(sysdate, 1), 'yyyy - q')
AND ( :nextquarter = 'true')
order by goLiveName, planningCurrent asc
and this is the result:
but now I am simulating with any date of July, for example '07/12/22', and as It would already be in Q3(according my db), the result would have to be the projects of Q4, but It continues to bring the results of Q3
like this:
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
where
to_char (dgl.GOLIVE_DATE_PLANNED, 'yyyy - q' ) = to_char (add_months (to_date('07/12/22', 'mm/dd/yy'), 1), 'yyyy - q' ) -- EXEMPLO
AND ( :nextquarter = 'true')
order by goLiveName, planningCurrent asc
Result: RESULT
If I change this line with that 1:
to_char (dgl.GOLIVE_DATE_PLANNED, 'yyyy - q' ) = to_char (add_months (to_date('07/12/22', 'mm/dd/yy'), 1), 'yyyy - q' )
for this line, the difference is that 3 and will bring the projects from Q4:
to_char (dgl.GOLIVE_DATE_PLANNED, 'yyyy - q' ) = to_char (add_months (to_date('07/12/22', 'mm/dd/yy'), 3), 'yyyy - q' )
and this is the result I wanted
What I realized is that if the difference is 1 month, it would only have to be 1, if the difference is 2 months 2 and if the difference is 3 months 3. How to adjust my SQL to bring always the next quarter ?
CodePudding user response：
You can just always add 3 months - that would work today too, you don't need to add one month; that's just the minimum that would work today. Today (in June) plus 3 months would be September, which is still Q3.
Comparing as strings isn't ideal though. If you have an index on your date column then converting to a string would prevent that being used, and you would have to convert the value in every row to compare it (unless you added a function-based index).
It would be better to use date ranges, based on adding three and six months to the start of the current quarter; something like:
where dgl.GOLIVE_DATE_PLANNED >= add_months(trunc(sysdate, 'Q'), 3)
and dgl.GOLIVE_DATE_PLANNED < add_months(trunc(sysdate, 'Q'), 6)
To see what that is doing you can look at the values involved:
select sysdate as today,
trunc(sysdate, 'Q') as current_q_start,
add_months(trunc(sysdate, 'Q'), 3) as next_q_start,
add_months(trunc(sysdate, 'Q'), 6) as following_q_start
from dual
TODAY CURRENT_Q_START NEXT_Q_START FOLLOWING_Q_START
------------------- ------------------- ------------------- -------------------
2022-06-03 23:41:13 2022-04-01 00:00:00 2022-07-01 00:00:00 2022-10-01 00:00:00
So the date range would cover everything from midnight on July 1st, unto - but not including - midnight on October 1st; that is, the entirety of July, August and September, which is all of the next quarter, Q3.
If run on July 12th you would see:
TODAY CURRENT_Q_START NEXT_Q_START FOLLOWING_Q_START
------------------- ------------------- ------------------- -------------------
2022-07-12 00:00:00 2022-07-01 00:00:00 2022-10-01 00:00:00 2023-01-01 00:00:00
and the date range covers all of Q4.