determine Quarter based on sysdate

I need to create a where statement so I can pull the previous quarters data base on sysdate and if that date falls between the start and end dates..  What is wrong with my current statement?

TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'Q'),-1)) BETWEEN D.GPA_PRD_STDT AND D.GPA_PRD_ENDDT
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:
Didn't we already cover a quarters query in a previous question?  Did that not work?

If D.GPA_PRD_STDT AND D.GPA_PRD_ENDDT are DATE data types, don't use TO_CHAR on your ADD_MONTHS.

What isn't your current query giving you?
--just to force the dates to come back in a specific format showing times:
alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

SQL> select TRUNC(SYSDATE,'Q') from dual;

TRUNC(SYSDATE,'Q')
-------------------
04/01/2018 00:00:00

SQL> select ADD_MONTHS(TRUNC(SYSDATE,'Q'),-1) from dual;

ADD_MONTHS(TRUNC(SY
-------------------
03/01/2018 00:00:00

Open in new window


Using the 03/01/2018 00:00:00 from what your ADD_MONTHS returns, do you want where it is between D.GPA_PRD_STDT AND D.GPA_PRD_ENDDT ?

If not, explain again what you want?

Please don't explain it.  Provide sample test data and expected results from the test data you provide.  Then we can provide SQL that gets the results form the data you provide.
0
slightwv (䄆 Netminder) Commented:
This looks like what you are asking for from your previous question:
https://www.experts-exchange.com/questions/29087571/Limit-query-results-by-quarters.html#a42491974
0
SujithData ArchitectCommented:
At any given date, if you are trying to get the records which covers ANY date in the prior quarter; you should use the filter like this -

add_months(sysdate, -3) BETWEEN D.GPA_PRD_STDT AND D.GPA_PRD_ENDDT
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
Please keep in mind that for rows having a start date plus end date, that these might SPAN a quarter and there are several possible conditions to consider. We don't know what data you have to deal with or you want to do with the possible conditions when comparing to "a quarter".

    Q1s      Q2s
s-E |        |        ignore, starts & ends before the quarter

 s--|--------|-E      spans the quarter -------------------------------------------- is this included?
  s-|---E    |        start before, but ends in, the quarter
    s----E   |        starts at beginning of the quarter, finishes before the quarter ends
    | s---E  |        starts and finished within the quarter
    |   s----E        starts within the quarter, finishes on last day of the quarter
    |     s--|-E      starts within the quarter but finishes after the quarter ---- is this included?

    |        | s-E    ignore, starts & ends after the quarter

s = start
E = end
Q1s = a quarter starts on
q2s = next Quarter starts on

Open in new window


Can you provide a small sample of the data? and the result you expect from that data?
0
SujithData ArchitectCommented:
@Paul - that is a good illustration.

A query like this should cover the scenarios - here only IDs 100 and 108 are totally out of the quarter boundaries and excluded

SQL> select  * from test_tbl;

        ID GPA_PRD_STDT    GPA_PRD_ENDDT
---------- --------------- ---------------
       100 20-DEC-17       25-DEC-17
       101 20-DEC-17       25-APR-18
       102 20-DEC-17       25-JAN-18
       103 31-DEC-17       25-JAN-18
       104 01-JAN-18       25-JAN-18
       105 10-JAN-18       25-JAN-18
       106 20-JAN-18       31-MAR-18
       107 20-JAN-18       20-APR-18
       108 20-APR-18       25-APR-18
       109 01-JAN-18       31-MAR-18
       110 31-DEC-17       01-APR-18

11 rows selected.

SQL>
SQL> select  *
  2  from    test_tbl
  3  where   trunc(add_months(sysdate, -3), 'Q') between trunc(gpa_prd_stdt, 'Q') and trunc(gpa_prd_enddt, 'Q')
  4  ;

        ID GPA_PRD_STDT    GPA_PRD_ENDDT
---------- --------------- ---------------
       101 20-DEC-17       25-APR-18
       102 20-DEC-17       25-JAN-18
       103 31-DEC-17       25-JAN-18
       104 01-JAN-18       25-JAN-18
       105 10-JAN-18       25-JAN-18
       106 20-JAN-18       31-MAR-18
       107 20-JAN-18       20-APR-18
       109 01-JAN-18       31-MAR-18
       110 31-DEC-17       01-APR-18

9 rows selected.

Open in new window

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
Karen SchaeferBI ANALYSTAuthor Commented:
i am getting the following error on Sujuth suggestion:

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"

trunc(add_months(sysdate, -3), 'Q') between trunc(gpa_prd_stdt, 'Q') and trunc(gpa_prd_enddt, 'Q')
0
slightwv (䄆 Netminder) Commented:
>>00918. 00000 -  "column ambiguously defined"

That was just an example.  You need to add the table alias that you have in your original query.


Can you post some sample data and expected results just in case the query still doesn't provide the results you want?
0
SujithData ArchitectCommented:
@Karen
>> ORA-00918: column ambiguously defined

that has nothing to do with the logic I have given you to identify the records. You have directly plugged in the piece of code, in potentially another query which has multiple tables with the same column names gpa_prd_stdt, gpa_prd_enddt. You need to give a table alias to the tables in your FROM clause and add the alias to the columns so that Oracle is able to understand what tables you are referring to.

For example -

if you have two tables test_tbl and another_table both having columns GPA_PRD_STDT  and GPA_PRD_ENDDT; and your query joins these tables; you need to do something like the below -

select  tt.ID, tt.GPA_PRD_STDT  , tt.GPA_PRD_ENDDT, x.some_column
from    test_tbl tt inner join another_table x ON (some join condition between tt and x)
where   trunc(add_months(sysdate, -3), 'Q') between trunc(tt.gpa_prd_stdt, 'Q') and trunc(ttgpa_prd_enddt, 'Q')
;
0
PortletPaulEE Topic AdvisorCommented:
I would still like to know what is required.

This is best provided as sample data and the expected results.
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
Oracle Database

From novice to tech pro — start learning today.