Limit query results by quarters

I need to modify the following query to limit the data by quarters.  1 row of data for the previous & 1 row per current quarters.  I currently am using a Union query  (this is only 1 part of the Union qry) to create the unique rows of data, but I need help limiting the data by previous & current quarters.

I have tried using ranking and using the sysdate to create the quarter, however how do I limit the date I am using (gpa_prd_enddt) in the WHERE clause to display a row of data for each quarter requied?

  
    Select DISTINCT A.SUPPLIER_ID
          , (  SELECT to_char(sysdate,'YYYY-"Q"Q') FROM dual) as CurQtr
         -- , (  SELECT to_char(add_months(trunc(sysdate,'mm'),-3),'YYYY-"Q"Q') FROM dual)as PrevQtr 
         , to_char(A.gpa_prd_enddt,'YYYY-"Q"Q') ENDDATE
          , C.SUPPLIER_NAME
         , Null GPA_Cost
         , Null GPA_Mgmt
         , Null GPA_Qual
         , Null GPA_Sch
         , GPA_Tech
         , Null Cost_CMT
         , Null     Mgmt_CMT
         , Null     Qual_CMT
         , Null     Sch_CMT
         , Eval_CMT Tech_CMT
    FROM GPA_DIM A
        INNER JOIN gpa_evaluation B  ON A.GPA_ID = B.GPA_ID
        INNER JOIN PERFORMANCE_BUS_GRP C ON A.ESDW_SUPPLIER_ID = C.ESDW_SUPPLIER_ID
    WHERE Evaluation_Cat =  'T'
       -- AND to_char(A.gpa_prd_enddt,'YYYY-"Q"Q')  = (SELECT to_char(sysdate,'YYYY-"Q"Q') FROM dual)
        ;

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
Karen SchaeferConnect With a Mentor BI ANALYSTAuthor Commented:
HERE IS MY FINAL SOLUTION:

SELECT * 
FROM DIM 
WHERE Gpa_Prd_Enddt  >= TRUNC(SYSDATE, 'Q')
      AND Gpa_Prd_Enddt  <= ADD_MONTHS(TRUNC(SYSDATE, 'Q')-1, +3)
      and gpa_stat ='A'

Open in new window


Thanks for all the help.
0
 
slightwv (䄆 Netminder) Commented:
You don't need to select from dual inside the main query:

Select DISTINCT A.SUPPLIER_ID
          ,to_char(sysdate,'YYYY-"Q"Q') as CurQtr
         -- , to_char(add_months(trunc(sysdate,'mm'),-3),'YYYY-"Q"Q') as PrevQtr
...


That said:  I do not understand what you are trying to do and why it might involve a UNION query.

Can you provide more detail about your requirements?
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Ok,

I need two rows of data
Row 1 (Limited to current Quarter)

Supplier_ID, Qtr1, Supplier_Name, etc.
Supplier_ID, Qtr2, Supplier_Name, etc.

I also need to include a Where Statement

where gpa_prd_enddt = current date(current quarter) for Row 1 then repeat value for Row 2.
and gpa_prd_enddt  = Current Date -3 months for previous quarter.

I have attached sample data.  ColB = (  SELECT to_char(add_months(trunc(sysdate,'mm'),-3),'YYYY-"Q"Q') FROM dual)as PrevQtr
colC = to_char(A.gpa_prd_enddt,'YYYY-"Q"Q') ENDDATE

What am I missing in the Where clause to limit the query to display  data for each quarter criteria?
Thanks,

Karen
export.csv
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
slightwv (䄆 Netminder) Commented:
Given that sample data, what are your expected results?

Just to confirm, I only need to care about the ENDDATE column and I can ignore the PREVQTR in your sample data?
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
thanks for your time, however, I was able to figure out the problem.  However, I am unable to post the revised query do to the proprietary nature.

Thanks,

Karen
0
 
Jessica MorenoCommented:
Where DatePart("yy",A.gap_prd_enddt) = DatePart("yy", sysdate) and
(DatePart("qq", A.gpa_prd_enddt) = DatePart("qq", sysdate) or DatePart("qq", A.gpa_prd_enddt) = DatePart("qq", sysdate) -1)

will filter for current year, current quarter and previous quarter.
0
 
slightwv (䄆 Netminder) Commented:
If you cannot post the SQL and accept your own answer, please delete the question.
0
 
PortletPaulfreelancerCommented:
Why would the solution query be more secretive than the query already displayed in the question?
perhaps you could just alter some names to make it more generic

nb. We prefer awarding of points for the solution or direct contribution toward a solution, not just for participation.
0
 
slightwv (䄆 Netminder) Commented:
It would be nice if your final solution was based on the same data you provided.

Given the ENDDATE in the sample data, your solution will not return data.
TRUNC(SYSDATE, 'Q') returns  01/01/2018 00:00:00
and  ADD_MONTHS(TRUNC(SYSDATE, 'Q')-1, +3)  returns  03/31/2018 00:00:00

Nothing in your sample data for enddate matches this.

Are you sure you solved this?  Your latest question appears to be very similar:
https://www.experts-exchange.com/questions/29087871/Join-2-with-statements-into-a-single-query.html
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
found solution else where
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.