Home » Developer & Programmer » Reports & Discoverer » Need of Prior Quarter logic
Need of Prior Quarter logic [message #304117] Tue, 04 March 2008 02:29 Go to next message
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
Re: Need of Prior Quarter logic [message #304121 is a reply to message #304117] Tue, 04 March 2008 03:13 Go to previous messageGo to next message
Littlefoot
Messages: 20891
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
Re: Need of Prior Quarter logic [message #304126 is a reply to message #304121] Tue, 04 March 2008 03:35 Go to previous messageGo to next message
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
Re: Need of Prior Quarter logic [message #304128 is a reply to message #304126] Tue, 04 March 2008 03:44 Go to previous messageGo to next message
Littlefoot
Messages: 20891
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 ./fa/3943/0/ ! Do some effort by yourself.
Re: Need of Prior Quarter logic [message #304134 is a reply to message #304128] Tue, 04 March 2008 04:12 Go to previous message
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!!
Previous Topic: Rep-1439 Error
Next Topic: Report on pre-printed stationery with different positions of detail records.
Goto Forum:
  


Current Time: Sun Dec 04 08:22:00 CST 2016

Total time taken to generate the page: 0.10114 seconds