Limiting the "End Date" date range

Database
Fan

Limiting the "End Date" date range

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

Example:

Start date             End dates

feb2018            Feb2018,March2018,April 2018

Jan2018           Jan2018,Feb2018,March2018

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

 

Thanks

Chandrika

4 REPLIES
Teradata Employee

Re: Limiting the "End Date" date range

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 (
    Select ...
    Where Report_Date between :StartDate and Add_Months(:StartDate,3)-1;
);

Then you could:
Exec QtrRpt(date'2018-02-01');
Exec QtrRpt(date'2018-01-01');
Exec QtrRpt(date'2017-02-01');

Fan

Re: Limiting the "End Date" date range

Hi GJ,

 

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?

Teradata Employee

Re: Limiting the "End Date" date range

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

Teradata Employee

Re: Limiting the "End Date" date range

Well, you could forget about the macro and just do:

 

Select ...
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'
UNION ALL
Select 'B' as Grp, ...
Where Report_Date between date'2018-01-01' and  date'2018-03-31'
UNION ALL
Select 'C' as Grp, ...
Where Report_Date between date'2017-02-01' and  date'2017-04-30'
Order By Grp, Report_Date, ...