CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Blossom
Blossom

Reputation: 55

Query to select full quarter dates based on todays date

I have written a query to select a particular dates.

If I run the query on first fiscal Monday, i.e first Monday after quarter end, then it should select entire previous month's dates.

If I run the query on some other Monday, then it should select the data for the present quarter till today's date.

Following is the query which I have tried:

SELECT 
CASE 
WHEN Q.GL_PERIOD_NUMBER IN (1,4,7,10,12) AND Q.DAY_NAME='MONDAY' AND Q.MONTHEND_FLAG='Y' THEN 'FULL_QUARTER'
WHEN Q.GL_PERIOD_NUMBER IN (2,3,5,6,8,9,11) AND Q.DAY_NAME='MONDAY' AND Q.MONTHEND_FLAG='N' THEN 'PARTIAL QUARTER'
ELSE 'NO RUN'
END RUN
FROM 
(SELECT A.GL_MONTHEND_FLAG MONTHEND_FLAG, B.DAY_NAME DAY_NAME , B.gl_period_number GL_PERIOD_NUMBER
FROM 
(SELECT TRIM(GL_MONTHEND_FLAG) GL_MONTHEND_FLAG
FROM RSE.RSE_CD_D_CALENDAR WHERE TRUNC(CALENDAR_DATE)=TRUNC(SYSDATE-12)) A,
(SELECT TRIM(DAY_NAME) DAY_NAME, gl_period_number GL_PERIOD_NUMBER
FROM RSE.RSE_CD_D_CALENDAR WHERE TRUNC(CALENDAR_DATE)=TRUNC(SYSDATE-10)) B) Q;

I have figured out the conditions, but I'm not able to design syntax for selecting dates.

It would be help me if someone could hint!

Following is table structure:

desc RSE_CD_D_CALENDAR
Name                      Null Type              
------------------------- ---- ----------------- 
CALENDAR_DATE                  DATE              
DATE_REFERENCE                 VARCHAR2(10 CHAR) 
GL_DAYEND_COMPLETE_FLAG        VARCHAR2(1 CHAR)  
DAY_OF_WEEK                    NUMBER            
DAY_NAME                       VARCHAR2(15 CHAR) 
DAY_NUMBER                     NUMBER            
DW_PROCESSING_WEEK_ID          NUMBER            
DW_FISCAL_WEEK_ID              NUMBER            
CALENDAR_WEEK_NUMBER           NUMBER            
GL_WEEK_NUMBER                 NUMBER            
GL_WEEKEND_FLAG                VARCHAR2(1 CHAR)  
GL_WEEKEND_COMPLETE_FLAG       VARCHAR2(1 CHAR)  
DW_MONTH_ID                    NUMBER            
GL_PERIOD                      VARCHAR2(6 CHAR)  
GL_PERIOD_NUMBER               NUMBER            
MONTH_SHORT_NAME               VARCHAR2(3 CHAR)  
MONTH_LONG_NAME                VARCHAR2(15 CHAR) 
GL_PERIOD_KEY                  NUMBER            
GL_MONTHEND_FLAG               VARCHAR2(1 CHAR)  
GL_MONTHEND_COMPLETE_FLAG      VARCHAR2(1 CHAR)  

DW_QTR_ID                      NUMBER            
QTR_NUMBER                     NUMBER            
GL_QTR_END_FLAG                VARCHAR2(1 CHAR)  
GL_QTR_END_COMPLETE_FLAG       VARCHAR2(1 CHAR)  
GL_YEAR                        NUMBER            
GL_YEAREND_FLAG                VARCHAR2(1 CHAR)  
GL_YEAREND_COMPLETE_FLAG       VARCHAR2(1 CHAR)  
MANUAL_ADJ_COMPLETE_FLAG       VARCHAR2(1 CHAR)  
PROCESSING_WEEK_RELATIVE       NUMBER            
FISCAL_WEEK_RELATIVE           NUMBER            
MONTH_RELATIVE                 NUMBER            
QTR_RELATIVE                   NUMBER            
YEAR_RELATIVE                  NUMBER            
CALENDAR_MONTH_NUMBER          NUMBER            
CALENDAR_MONTH_SHORT_NAME      VARCHAR2(4000)    
BILLING_DAY_FLAG               VARCHAR2(1)       
QUARTER_NAME                   VARCHAR2(6)       
DW_CALENDAR_MONTH_ID           NUMBER            
CALENDAR_MONTH_RELATIVE        NUMBER            
CALCULATION_DATE               DATE              
BILLING_DAY_USA                NUMBER            
BILLING_DAY                    NUMBER

The relative fields are present for current and 0,-1,-2 for respective previous months weeks etc.

Upvotes: 0

Views: 230

Answers (1)

michal
michal

Reputation: 323

I think I would go with this approach:

with quarter_start_dt as (
select 
case when trunc(sysdate-7, 'Q') = trunc(sysdate, 'Q') then -- current quarter
trunc(sysdate, 'Q') 
 when trunc(sysdate-7, 'Q') <> trunc(sysdate, 'Q') then -- previous quarter
trunc(sysdate-7, 'Q')
end qs_dt
from dual)
select * from table, quarter_start_dt where
condition_dt >=  qs_dt;

additionally I guess, that for previous quarter you'd like to pick the quarter end date and use it as an additional condition to cut out anything done after the quarter end date.

EDIT: The "with" query is used to pick the boundary date - qs_dt (quarter start date). trunc(date, 'Q') returns start of the quarter date, so I'm checking if it was the same or different quarter a week ago - if it is the same quarter then use current quarter start date as boundary date, if week ago the quarter start date was different, it means it's a start of the new quarter and you want to use the start of previous quarter as the boundary date. Then take your query (which I replaced with simple select for readability) and use qs_dt as the quarter start date in the where clause.

Hope it helps.

Upvotes: 2

Related Questions