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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
PortletPaulfreelancerCommented:
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
 
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
 
PortletPaulfreelancerCommented:
I would still like to know what is required.

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