Monday, September 17, 2007

Query: Monthly billing on specific day of month

Just a quick challenge.
Had a minor brain overload last Friday, eventually came up with (I think) a sub-optimal query ...
Basic scenario is that I want a billing event to happen on the monthly anniversary of a event, and want a single SQL statement that, given the original and current date, will tell me whether I should be billing on that day.
Problem is that e.g. if original date if 31-Jan-07 then I should bill on the 28-Feb-07. Moreover if the original date is 28-Feb-07 then I should bill on 28-Jan-07. However, the when the original date is 28-Feb-07 the Oracle months_between function isn't suitable as it returns 1 for both the 28-Mar-07 and 31-Mar-07, as follows:

select months_between(to_date('28-JUL-2007'),to_date('28-FEB-2007')) m1
,      months_between(to_date('31-JUL-2007'),to_date('28-FEB-2007')) m2
from   dual;

        M1         M2
---------- ----------
         5          5

1 row selected.
Okay, so here's my solution ... with a loop to test for a year. Anyone got a better alternative to the core query?
set serverout on size 1000000
declare
 org_date date := '28-JAN-03';
 cur_date date := '01-JAN-04';
 l_status varchar2(1);
 l_count number := 0;
 l_pays  number := 0;
begin
 while true loop
  l_count := l_count+1;
  begin
   select 'Y' yes
   into   l_status
   from   dual
   where 
      (
        (to_number(to_char(org_date,'DD')) <= 28 and
         to_number(to_char(org_date,'DD')) = to_number(to_char(cur_date,'DD'))
        )
        or
        ( to_number(to_char(org_date,'DD')) = 29 and
          ( to_number(to_char(org_date,'DD')) = to_number(to_char(cur_date,'DD'))
            or
            ( to_number(to_char(last_day(cur_date),'DD')) < 29 and
              to_number(to_char(last_day(cur_date),'DD')) >= 28 and
              cur_date = last_day(cur_date)
            )
          )
        )
        or
        ( to_number(to_char(org_date,'DD')) = 30 and
          ( to_number(to_char(org_date,'DD')) = to_number(to_char(cur_date,'DD'))
            or
            ( to_number(to_char(last_day(cur_date),'DD')) < 30 and
              to_number(to_char(last_day(cur_date),'DD')) >= 28 and
              cur_date = last_day(cur_date)
            )
          )
        )
        or
        ( to_number(to_char(org_date,'DD')) = 31 and
          ( to_number(to_char(org_date,'DD')) = to_number(to_char(cur_date,'DD'))
            or
            ( to_number(to_char(last_day(cur_date),'DD')) < 31 and
              to_number(to_char(last_day(cur_date),'DD')) >= 28 and
              cur_date = last_day(cur_date)
            )
          )
        )
      );
   dbms_output.put_line('Billing org_date='||org_date||' cur_date='||cur_date);
   l_pays := l_pays + 1;
  exception
  when no_data_found then
   null;
  end;
  if l_count > 366 then
   exit;
  end if;
  cur_date := cur_date + 1;
 end loop;
 dbms_output.put_line('Payments = '||l_pays);
end;
/
Comments more than welcome!

3 comments:

Unknown said...

My only comment so far is that it's probably less unwieldy to put the WHERE clause into a CASE in the SELECT list. You might not need PL/SQL at all at that point.

Gareth Roberts said...

Hi,
The core select statement returning a row (or not) is what I'm aiming for, so need the where clause. The PL/SQL is for testing across a range of dates, and using specified dates other than sysdate.
Gareth

Unknown said...

This is quite informative and elaborately discussed article and very useful too. Looking forward to such more information in the future also.
sap upgrade testing