I am pulling 3 different start date - Feb 2018(current Month) Jan2018(previous Month) and feb 2017(previous year)
and I want to pull 3 months runout as end date for these start dates
Start date End dates
feb2018 Feb2018,March2018,April 2018
Feb2017 Feb2017,March2017,April 2017
I performed End date - Start date<= 90 which gives me the latency and pulls additional months
Is there any way I can do it? Please help me
This sounds like 3 separate reports and 3 separate queries. You could create a macro that does this for each start date, such as:
Replace Macro QtrRpt (StartDate DATE) as (
Where Report_Date between :StartDate and Add_Months(:StartDate,3)-1;
Then you could:
Thank you for the solution .I can do this as 3 separate reports but I would like to have it in a single report as I have this query running for 5 different states.Is there any way to do it in one query?
I'm not sure I totally understood your needs, but this should be a good way to start :
with cte_anchor_date (Start_Date) as ( select calendar_date from sys_calendar.calendar where calendar_date = add_months(to_date('feb2018', 'monyyyy'), -12) or calendar_date = add_months(to_date('feb2018', 'monyyyy'), - 1) or calendar_date = to_date('feb2018', 'monyyyy') ) select to_char(cad.Start_Date, 'monyyyy') as Start_Date , rtrim(XMLAgg(to_char(cal.calendar_date, 'monyyyy') || ',' order by cal.calendar_date asc) (varchar(200)), ',') as End_Dates from sys_calendar.calendar as cal join cte_anchor_date as cad on cal.calendar_date = cad.Start_Date or cal.calendar_date = add_months(cad.Start_Date, 1) or cal.calendar_date = add_months(cad.Start_Date, 2) group by cad.Start_Date order by cad.Start_Date desc ; Start_Date End_Dates feb2018 feb2018, mar2018, apr2018 jan2018 jan2018, feb2018, mar2018 feb2017 feb2017, mar2017, apr2017
Well, you could forget about the macro and just do:
Where (Report_Date between date'2018-02-01' and date'2018-04-30')
OR (Report_Date between date'2018-01-01' and date'2018-03-31')
OR (Report_Date between date'2017-02-01' and date'2017-04-30')
Order By Report_Date
But then they would all be mixed together. To truly combine the results of 3 queries, you would need to use the UNION set operator and add a field to identify each of the 3 groups, viz.:
Select 'A' as Grp, ...
Where Report_Date between date'2018-02-01' and date'2018-04-30'
Select 'B' as Grp, ...
Where Report_Date between date'2018-01-01' and date'2018-03-31'
Select 'C' as Grp, ...
Where Report_Date between date'2017-02-01' and date'2017-04-30'
Order By Grp, Report_Date, ...