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 

Open in new window

What am I missing?
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor 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
where gpa_prd_stdt >= add_months(trunc(sysdate,'Q'),-6)
/

Open in new window


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;

Open in new window

0
 
slightwv (䄆 Netminder) 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'
    when TRUNC(D.GPA_PRD_STDT, 'Q')=add_months(trunc(sysdate,'Q'),-3) and TRUNC(D.GPA_PRD_ENDDT, 'Q')=add_months(trunc(sysdate,'Q'),-3) then 'QTRLY'
END
0
 
Karen SchaeferBI 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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
slightwv (䄆 Netminder) 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
 
Karen SchaeferBI 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
 
slightwv (䄆 Netminder) 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
 
Karen SchaeferBI 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
 
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks the ceil function did the trick.  Thanks for your time and patience..
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.