Results 1 to 3 of 3
  1. #1
    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. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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. #3
    Join Date
    Mar 2013
    Posts
    2

    Thumbs up

    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.
    Appreciate your feedback.
    Kind regards
    Ian.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •