OraFAQ Forum: SQL & PL/SQL » Custom Month-to-date Expression

Home » SQL & PL/SQL » SQL & PL/SQL » Custom Month-to-date Expression

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Custom Month-to-date Expression [message #2563]

Fri, 26 July 2002 12:49

Sam
Messages: 255
Registered: April 2000

Senior Member

Folks/Gurus,

I would like to develop a custom month-to-date SQL with the following logic:

1- If its regular day of the month, I need a month-to-date (1st of the month to yesterday's) data, eg if its july 26th, data should be from july 1-july 25th.
2- if its 1st of the month, I need last month's month-to-date data. if its july 1, data should be from june 1-june 30th.

If this possible in one statement?

Thanks for your time and help in advance,
Sam

Report message to a moderator

Re: Custom Month-to-date Expression [message #2564 is a reply to message #2563]

Fri, 26 July 2002 13:19

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

sql>declare
  2    today  date;
  3    v_date_min  date;
  4    v_date_max  date;
  5  begin
  6    today := sysdate;
  7  
  8    select min(the_date), max(the_date)
  9      into v_date_min, v_date_max
 10      from t
 11     where the_date between
 12               decode(to_char(today, 'dd'),
 13                 '01', add_months(trunc(today), -1),
 14                 trunc(today, 'mm'))
 15         and trunc(today - 1) + (1 - (1/24/60/60));
 16  
 17    dbms_output.put_line( 'Today is ' || today ||
 18      ', Date range is from ' || v_date_min || ' to ' || v_date_max );       
 19  
 20    today := trunc(sysdate, 'mm');  -- pretend that this is the first of July
 21  
 22    select min(the_date), max(the_date)
 23      into v_date_min, v_date_max
 24      from t
 25     where the_date between
 26               decode(to_char(today, 'dd'),
 27                 '01', add_months(trunc(today), -1),
 28                 trunc(today, 'mm'))
 29         and trunc(today - 1) + (1 - (1/24/60/60));
 30  
 31    dbms_output.put_line( 'Today is ' || today ||
 32      ', Date range is from ' || v_date_min || ' to ' || v_date_max );
 33  end;
 34  /
 
<b>Today is 26-JUL-02, Date range is from 01-JUL-02 to 25-JUL-02
Today is 01-JUL-02, Date range is from 01-JUN-02 to 30-JUN-02</b>
 
PL/SQL procedure successfully completed.

Report message to a moderator

Previous Topic:	fetching the previous sum and current sum
Next Topic:	How to debug the trigger?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 12:22:40 CDT 2024