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:
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.
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
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
Post a Comment