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

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

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   frecuency4        08/06/2018         null            annualyour codeyour codeitem_id  file_start_date    file_end_date   frecuency   freq_month   calculation_start_date  claculation_end_date4        08/06/2018         null            annual      12           08/06/2019              07/06/2020But is should be:item_id  file_start_date    file_end_date   frecuency   freq_month   calculation_start_date  claculation_end_date4        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

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