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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
PortletPaulEE Topic AdvisorCommented:
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
Karen SchaeferBI 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PL/SQL

From novice to tech pro — start learning today.