# Thread: Date Calculation - 13 months per year

1. Registered User
Join Date
Mar 2013
Posts
2

## Unanswered: Date Calculation - 13 months per year

Hi

I'm hoping for some advice on a weird calculation issue we face here at work.
We are trying to determine which month a magazine subscription will expire. We know the current issue (year/month) of the subscription and the number of issues remaining to be sent. There is normally one issue sent per month. Traditionally the subscription will go for 12 months, can start any time of year, though can also go for over 12 months (24/36/etc). Issue numbers start with last 2 digits of the year and last 2 digits is the month number.
i.e. July Issue 2012 = 1207

The business decided to send an extra issue in December which threw all the expiry date calculations out. We have gone from having the following mag issue sequence :
Oct Issue : 1210
Nov Issue : 1211
Dec Issue : 1212
Jan Issue : 1301
Feb Issue : 1302
to
Oct Issue : 1210
Nov Issue : 1211
Dec Issues : 1212 + 1213
Jan Issue : 1301
Feb Issue : 1302
this meant that those subscriptions that had sufficient issues remaining to include the second December issue had a wrong expiry date calculated.

Old calculation
Example : Current issue 1210
Issues to go : 4
Expiry Month : 1302 <<< Wrong

New calculation
Example : Current issue 1210
Issues to go : 4
Expiry Month : 1301 <<< Correct

The code I have posted below works and I will convert this into a function.
The code generates a list of months, duplicates the december month and finds the Expiry Date by matching the Issues to go with the row number.

I am interested if there is a much simpler solution to this problem. This seems like using an elephant gun to shoot clay targets.

DECLARE
v_current_issue CHAR (4) := '1212';
v_issues_to_go int := 15;
v_expire_date CHAR(4) := 0;

BEGIN

if substr(v_current_issue,3,2) = '13' then
v_current_issue := substr(v_current_issue,1,2) + 1 || '01';
v_issues_to_go := v_issues_to_go - 1;
end if;

select full_date
into v_expire_date
from
(select rownum row_val, month_num, full_date
from
(SELECT to_char(ADD_MONTHS (to_date(v_current_issue,'YYMM'), LEVEL - 1),'MM') month_num, to_char(ADD_MONTHS (to_date(v_current_issue,'YYMM'), LEVEL - 1),'YYMM') full_date
FROM DUAL
CONNECT BY LEVEL <= MONTHS_BETWEEN (ADD_MONTHS(to_date(v_current_issue,'YYMM'),v_issu es_to_go + 1), to_date(v_current_issue,'YYMM'))) a,
(select 1 x from dual union all select 2 x from dual ) b
where (b.x = 1 or (b.x = 2 and mod(a.month_num,12) = 0))
order by row_val)
where row_val = v_issues_to_go + 1;

DBMS_OUTPUT.put_line (v_expire_date);

END;
Last edited by Catch_22; 03-01-13 at 06:12.

2. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
Have a look at this option:
Code:
```SQL> create or replace function f_exp (par_curis in varchar2,
2                                    par_itogo in number
3                                                              )
4    return varchar2
5  is
6    /* par_curis - current issue, for example 1210
7       par_itogo - issues to go , for example 4
8
9      Check whether the "extra issue" date (Dec 2012) lies between the
10      (current issue date) and (current issue date + issues to go).
11      If so, subtract 1 from the expiry month (i.e. (current issue date + issues to go).
12      If not, return (current issue date + issues to go)
13    */
14
15  begin
16    return
17      case when date '2012-12-01' >= to_date(par_curis, 'yymm') and
18                date '2012-12-01' <  add_months(to_date(par_curis, 'yymm'), par_itogo)
19           then to_char(add_months(to_date(par_curis, 'yymm'), par_itogo - 1), 'yymm')
20           else to_char(add_months(to_date(par_curis, 'yymm'), par_itogo)    , 'yymm')
21           end;
22  end f_exp;
23  /

Function created.

SQL> select f_exp('1210', 4) e1,
2         f_exp('1212', 2) e2,
3         f_exp('1207', 4) e3,
4         f_exp('1207', 5) e4,
5         f_exp('1301', 3) e5
6  from dual;

E1   E2   E3   E4   E5
---- ---- ---- ---- ----
1301 1301 1211 1212 1304

SQL>```

3. Registered User
Join Date
Mar 2013
Posts
2
SQL> create or replace function f_exp (par_curis in varchar2,
2 par_itogo in number
3 )
4 return varchar2
5 is
6 /* par_curis - current issue, for example 1210
7 par_itogo - issues to go , for example 4
8
9 Check whether the "extra issue" date (Dec 2012) lies between the
10 (current issue date) and (current issue date + issues to go).
11 If so, subtract 1 from the expiry month (i.e. (current issue date + issues to go).
12 If not, return (current issue date + issues to go)
13 */
14
15 begin
16 return
17 case when date '2012-12-01' >= to_date(par_curis, 'yymm') and
18 date '2012-12-01' < add_months(to_date(par_curis, 'yymm'), par_itogo)
19 then to_char(add_months(to_date(par_curis, 'yymm'), par_itogo - 1), 'yymm')
20 else to_char(add_months(to_date(par_curis, 'yymm'), par_itogo) , 'yymm')
21 end;
22 end f_exp;
23 /

Function created.

SQL> select f_exp('1210', 4) e1,
2 f_exp('1212', 2) e2,
3 f_exp('1207', 4) e3,
4 f_exp('1207', 5) e4,
5 f_exp('1301', 3) e5
6 from dual;

E1 E2 E3 E4 E5
---- ---- ---- ---- ----
1301 1301 1211 1212 1304

SQL>
Hi
Thanks very much for this. I think I can take this and run with it.
There are a few issues which I think I can resolve.
(a). Needs to work for all future years.
(b). Need to cater for scenarios where the subscription term includes more than one December.