OraFAQ Forum: Reports & Discoverer » Need of Prior Quarter logic

Home » Developer & Programmer » Reports & Discoverer » Need of Prior Quarter logic

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

Need of Prior Quarter logic [message #304117]

Tue, 04 March 2008 02:29

joeslee
Messages: 18
Registered: February 2008
Location: Bangalore

Junior Member

Hi,
Anyone could you please tel me the logic that how to find the previous quarter(PQ)??

For example,If I select PQ in parameter form(Oracle reports) it should give the details of October1st to December 31st.So I need to display the previous quarter.

Please help me ASAP.

Cheers,
Joeslee

Report message to a moderator

Re: Need of Prior Quarter logic [message #304121 is a reply to message #304117]

Tue, 04 March 2008 03:13

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

"Previous quarter" regarding to what? SYSDATE?

Here's an example; it is quite obvious how to find a current quarter:

SELECT TO_CHAR(SYSDATE, 'Q') quarter FROM dual

Here's one way to select beginning and end dates of quarters, depending on a date:

select 
  datum, quarter, 
  add_months(trunc(datum, 'yyyy'), decode(quarter, 1, 0, 2, 3, 3, 6, 4,  9))         q_start,
  add_months(trunc(datum, 'yyyy'), decode(quarter, 1, 3, 2, 6, 3, 9, 4, 12)) - 1     q_end,
  add_months(trunc(datum, 'yyyy'), decode(quarter, 1, 0, 2, 3, 3, 6, 4,  9) - 3)     pq_start,
  add_months(trunc(datum, 'yyyy'), decode(quarter, 1, 3, 2, 6, 3, 9, 4, 12) - 3) - 1 pq_end
from 
 (with test as
   (select to_date('20.03.2008', 'dd.mm.yyyy') datum from dual
    union
    select to_date('15.05.2008', 'dd.mm.yyyy') datum from dual
    union
    select to_date('27.08.2008', 'dd.mm.yyyy') datum from dual
    union
    select to_date('24.12.2008', 'dd.mm.yyyy') datum from dual
   )
  select datum, to_char(datum, 'q') quarter
  from test
 );

DATUM      Q Q_START    Q_END      PQ_START   PQ_END
---------- - ---------- ---------- ---------- ----------
20.03.2008 1 01.01.2008 31.03.2008 01.10.2007 31.12.2007
15.05.2008 2 01.04.2008 30.06.2008 01.01.2008 31.03.2008
27.08.2008 3 01.07.2008 30.09.2008 01.04.2008 30.06.2008
24.12.2008 4 01.10.2008 31.12.2008 01.07.2008 30.09.2008

Q                   - quarter
Q_start and Q_end   - first and last day of a quarter
PQ_start and PQ_end - first and last day of a previous quarter

Report message to a moderator

Re: Need of Prior Quarter logic [message #304126 is a reply to message #304121]

Tue, 04 March 2008 03:35

joeslee
Messages: 18
Registered: February 2008
Location: Bangalore

Junior Member

Ya regarding to today's date(sysdate) I have to find the Previous quarter...

Thanks in advance..

Cheers,
Joeslee

Report message to a moderator

Re: Need of Prior Quarter logic [message #304128 is a reply to message #304126]

Tue, 04 March 2008 03:44

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What "advance"? Solution is here, just adjust it to your needs. I'm not going to spoonfeed you /forum/fa/3943/0/

! Do some effort by yourself.

Report message to a moderator

Re: Need of Prior Quarter logic [message #304134 is a reply to message #304128]

Tue, 04 March 2008 04:12

joeslee
Messages: 18
Registered: February 2008
Location: Bangalore

Junior Member

Hi,
I have found the solution already!!! Instead of saying thanks I hv said Advance... Laughing

sorry for the tongue slip!!!

Anyway your logic helps me..Thanks for that!!

Report message to a moderator

Previous Topic:	Rep-1439 Error
Next Topic:	Report on pre-printed stationery with different positions of detail records.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 21:43:59 CST 2024