# current & previous monthly/quarter values

i am attempting determine if the start (GPA_PRD_STDT) and end (GPA_PRD_ENDDT) dates share the same month then field1 = "Monthly and if not then check the start and end date to determine if they fall within the past quarter.  Then repeat the process to determine previous month/quarter.

`````` ,  CASE TRUNC(D.GPA_PRD_STDT, 'MM') = ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
AND TRUNC(D.GPA_PRD_ENDDT, 'MM') = ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1) THEN
D.GPA_STAT = 'MONTHLY'
Else TRUNC(D.GPA_PRD_STDT, 'MM') <> ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
AND TRUNC(D.GPA_PRD_ENDDT, 'MM') <> ADD_MONTHS(TRUNC(D.GPA_PRD_STDT, 'MM'), -1) THEN
D.GPA_STAT = 'QTRLY'
END
``````
What am I missing?
###### Who is Participating?

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.

Commented:
I'm not sure I understand exactly what you are after.  If you can provide some sample data and expected results it will help clear up the requirements.

I'm not sure why you trunc the date by month and compare that with the first day of the previous month:ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)

Until we get the sample data, here is a guess:
CASE
when TRUNC(D.GPA_PRD_STDT, 'MM')=TRUNC(D.GPA_PRD_ENDDT, 'MM') then 'MONTHLY'
END
0
BI ANALYSTAuthor Commented:
Here is a sample of the data, the last column are the results I need.  I need to compare the start date to last date and determine if the date range is for month or quarter. I also need to determine what the current month/quarter are by comparing to the current date minus 1 month/quarter to retrieve the data for the previous month/quarter.

thanks,
K
export.xlsx
0
Commented:
>>Here is a sample of the data,

Row 2 goes from April 1, 2017 to April 30, 2017:  You show Quarterly as the expected result.  I don't understand how you get that.

>> also need to determine what the current month/quarter are by comparing to the current date minus 1 month/quarter to retrieve the data for the previous month/quarter.

I still don't understand this.  Where is it shown in the expected results?
0
BI ANALYSTAuthor Commented:
sorry the data may have had a few typos.

My data will always be for the the current month -1 : so if current month = April then the data I want will be for March and the same for the quarters if current quarter = 2017Q2, then the date range for the date will be 2017Q1.  I am attempting to automatically determine the date range.

So once the date range is determined then I will also need to determine hat the previous month/quarter for the select date range.

Hope this helps.

K
0
Commented:
Sorry but that explanation doesn't help.  Can you correct your sample data and expected results?

By "current month -1" do you mean the month before sysdate?

In the spreadsheet you uploaded for row 5 you have 1-Jun-17      30-Jun-17 as Monthly.  I don't see where SYSDATE is involved in that calculation.
0
BI ANALYSTAuthor Commented:
sydate = todays date - to be used to figure out which date's worth of date is required.  So if today's date = 4/17/2018 then the data needed for report will be all data where the date ranges are between 3/1/2018 thru 3/31/2018 for monthly requirement.

FOR PREVIOUS MONTH
IF DATE = 4/18/2018 - CURRENT RANGE = 3/1/2018 - 1 MONTH = 2/1/2018-2/28/2018

for Quarter requirement if sysdate = 4/17/2018 the date range for the quarter will be 1/01/2018-3/31/2018 & quarter value = 2018_Q1 and the previous quarter should be 2017_Q4.

i have attached a revised data sheet containing color coding from the data to the results.

export.xlsx
0
Commented:
I think I'm starting to understand and think it is easier than what you are explaining.

It seems like you want all the data from the previous quarter no matter what.  Then you look at the start and end date to decide the result.

See if this is what you are after.  I've included my test case setup so if there is something wrong, please add to my test case and explain why you've added it.
``````select supplier_name, gpa_prd_stdt, gpa_prd_enddt, ratings, evaldesc,
ceil(months_between(gpa_prd_enddt,gpa_prd_stdt)) bob,
case
when ceil(months_between(gpa_prd_enddt,gpa_prd_stdt))=1 then 'MONTHLY'
when ceil(months_between(gpa_prd_enddt,gpa_prd_stdt))=3 then 'QTRLY'
else 'Unknown'
end results
from tab1
/
``````

``````drop table tab1 purge;
create table tab1(SUPPLIER_NAME varchar2(15), GPA_PRD_STDT date, GPA_PRD_ENDDT date, RATINGS number, EVALDESC varchar2(10));
insert into tab1 values('ABC COMPANY',to_date('1-Mar-18','DD-Mon-YY'),to_date('31-Mar-18','DD-Mon-YY'),5,'Schedule');
insert into tab1 values('ABC COMPANY',to_date('1-Mar-18','DD-Mon-YY'),to_date('31-Mar-18','DD-Mon-YY'),5,'Cost');
insert into tab1 values('ABC COMPANY',to_date('1-Jan-18','DD-Mon-YY'),to_date('31-Mar-18','DD-Mon-YY'),5,'Management');
insert into tab1 values('ABC COMPANY',to_date('1-Jan-18','DD-Mon-YY'),to_date('31-Mar-18','DD-Mon-YY'),5,'Cost');
insert into tab1 values('ABC COMPANY',to_date('1-Dec-17','DD-Mon-YY'),to_date('31-Dec-17','DD-Mon-YY'),5,'Quality');
insert into tab1 values('ABC COMPANY',to_date('1-Oct-17','DD-Mon-YY'),to_date('31-Dec-17','DD-Mon-YY'),3.8,'Cost');
insert into tab1 values('XYZ COMPANY',to_date('1-Jan-18','DD-Mon-YY'),to_date('31-Jan-18','DD-Mon-YY'),5,'Schedule');
insert into tab1 values('XYZ COMPANY',to_date('1-Oct-17','DD-Mon-YY'),to_date('31-Oct-17','DD-Mon-YY'),5,'Management');
insert into tab1 values('XYZ COMPANY',to_date('1-Jan-18','DD-Mon-YY'),to_date('31-Mar-18','DD-Mon-YY'),3.8,'Quality');
insert into tab1 values('XYZ COMPANY',to_date('1-Jan-18','DD-Mon-YY'),to_date('31-Mar-18','DD-Mon-YY'),5,'Cost');
insert into tab1 values('XYZ COMPANY',to_date('1-Jan-18','DD-Mon-YY'),to_date('31-Mar-18','DD-Mon-YY'),5,'Schedule');
insert into tab1 values('XYZ COMPANY',to_date('1-Jan-18','DD-Mon-YY'),to_date('31-Mar-18','DD-Mon-YY'),2,'Management');
commit;
``````
0

Experts Exchange Solution brought to you by