Calculating start and end date based on frequency and start date

General
Enthusiast

Calculating start and end date based on frequency and start date

Hi,

 

I am struggling to find a way of resolving this challenge without using Store Procedures.

 

I receive a file with a start date and a frequency:

 

File:

item    file_start_date       file_end_date           frequency

1        15/01/2017         null                          quarterly 

2        10/06/2017         null                          monthly

3        01/02/2016         null                          annual

 

What I need is to do some aggregation within that time period, but first I need to renew the start_date and end_date automatically so I do not process data in the past.

 

So for example if today is 22/01/2018 the table above should looks like:

 

item    file_start_date       file_end_date           frequency    calcularion_start_date     calculation_end_date

1        15/01/2017         null                          quarterly       15/01/2018                   14/04/2018

2        10/06/2017         null                          monthly        10/01/2018                   09/02/2018

3        01/02/2016         null                          annual          01/02/2018                   31/01/2019

 

Not sure how to iterate over the periods until reach the current one.

 

Any help on this would be much appreciated

 

Thanks

Gonzalo

 

 

 

Tags (2)

Accepted Solutions
Highlighted
Teradata Employee

Re: Calculating start and end date based on frequency and start date

Ok I got it.

Try the following code :

 select item_id
       , frequency
       , case frequency
           when 'monthly'   then  1
           when 'quarterly' then  3
           when 'annual'    then 12
         end as freq_month
       , file_start_dt
       , file_end_dt
       , file_start_dt + ((months_between(current_date, file_start_dt) (int)) / freq_month) * freq_month * interval '1' month as calculation_start_date
       , calculation_start_date + freq_month * interval '1' month - interval '1' day as calculation_end_date
    from mvt_items
order by item_id asc;

 item_id frequency freq_month file_start_dt file_end_dt calculation_start_date calculation_end_date 
 ------- --------- ---------- ------------- ----------- ---------------------- -------------------- 
       1 quarterly          3 2017-01-15    null        2019-01-15             2019-04-14          
       2 monthly            1 2017-06-10    null        2019-01-10             2019-02-09          
       3 annual            12 2016-02-01    null        2018-02-01             2019-01-31          
       4 annual            12 2018-06-08    null        2018-06-08             2019-06-07          
       5 quarterly          3 2018-07-01    null        2019-01-01             2019-03-31          
1 ACCEPTED SOLUTION
3 REPLIES 3
Teradata Employee

Re: Calculating start and end date based on frequency and start date

Hi gfcandau,

 

Edit : some edits due to your edit :)

 

You have to decode your frequencies.

Datas

create multiset volatile table mvt_items, no log
( item_id           byteint
, file_start_dt     date format 'yyyy-mm-dd'
, file_end_dt       date format 'yyyy-mm-dd'
, frequency         varchar(20)
)
unique primary index (item_id)
on commit preserve rows;

insert into mvt_items (item_id, file_start_dt, frequency)values (1, date '2017-01-15', 'quarterly');
insert into mvt_items (item_id, file_start_dt, frequency)values (2, date '2017-06-10', 'monthly'  );
insert into mvt_items (item_id, file_start_dt, frequency)values (3, date '2016-02-01', 'annual'   );

Query

 

select item_id, file_start_dt, file_end_dt, frequency
     , case frequency
         when 'monthly'   then  1
when 'quarterly' then 3 when 'annual' then 12 end as freq_month , trunc(date '2018-01-22', 'yyyy') + (extract(month from file_start_dt) - 1) * interval '1' month + (extract(day from file_start_dt) - 1) * interval '1' day as calculation_start_date , calculation_start_date + freq_month * interval '1' month - interval '1' day as calculation_end_date from mvt_items; item_id file_start_dt file_end_dt frequency freq_month calculation_start_date calculation_end_date ------- ------------- ----------- --------- ---------- ---------------------- -------------------- 1 2017-01-15 {null} quarterly 3 2018-01-15 2018-04-14 2 2017-06-10 {null} monthly 1 2018-06-10 2018-07-09 3 2016-02-01 {null} annual 12 2018-02-01 2019-01-31

I'm not really sure your end date for item_id 1 in your sample is accurate. 

And I'm not sure about the rule for item_id 2 ?

 

 

Enthusiast

Re: Calculating start and end date based on frequency and start date

Thanks @Waldar for your prompt response.  

 

That looks great! it's exactly what I am looking for, done in a simple and straight manner.

 

Only bug is, if we are within the current period that we get from the file, you code is adding 1 more year, which is not correct:

 

item_id  file_start_date    file_end_date   frecuency
4 08/06/2018 null annualyour code

your code
item_id file_start_date file_end_date frecuency freq_month calculation_start_date claculation_end_date
4 08/06/2018 null annual 12 08/06/2019 07/06/2020

But is should be:
item_id file_start_date file_end_date frecuency freq_month calculation_start_date claculation_end_date
4 08/06/2018 null annual 12 08/06/2018 07/06/2019

As  '2018-01-22' still in the valid period

 

Any idea how to fix that bit?

 

Thanks

Gonzalo

Highlighted
Teradata Employee

Re: Calculating start and end date based on frequency and start date

Ok I got it.

Try the following code :

 select item_id
       , frequency
       , case frequency
           when 'monthly'   then  1
           when 'quarterly' then  3
           when 'annual'    then 12
         end as freq_month
       , file_start_dt
       , file_end_dt
       , file_start_dt + ((months_between(current_date, file_start_dt) (int)) / freq_month) * freq_month * interval '1' month as calculation_start_date
       , calculation_start_date + freq_month * interval '1' month - interval '1' day as calculation_end_date
    from mvt_items
order by item_id asc;

 item_id frequency freq_month file_start_dt file_end_dt calculation_start_date calculation_end_date 
 ------- --------- ---------- ------------- ----------- ---------------------- -------------------- 
       1 quarterly          3 2017-01-15    null        2019-01-15             2019-04-14          
       2 monthly            1 2017-06-10    null        2019-01-10             2019-02-09          
       3 annual            12 2016-02-01    null        2018-02-01             2019-01-31          
       4 annual            12 2018-06-08    null        2018-06-08             2019-06-07          
       5 quarterly          3 2018-07-01    null        2019-01-01             2019-03-31