current & previous monthly/quarter values

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

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

>>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?

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

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.

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

Thanks the ceil function did the trick.  Thanks for your time and patience..