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?

[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:
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
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
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.

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
slightwv (䄆 Netminder) 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

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:
Thanks the ceil function did the trick.  Thanks for your time and patience..
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
Query Syntax

From novice to tech pro — start learning today.