Home » SQL & PL/SQL » SQL & PL/SQL » get previous quarter starting_date and ending_date
get previous quarter starting_date and ending_date [message #280211] |
Mon, 12 November 2007 17:05  |
ashwin_tampa
Messages: 40 Registered: October 2005
|
Member |
|
|
Hi Guys,
Could you please help with following ?
How to get previous quarter starting_date and ending_date ?
drop table dim;
create table dim (bill_date date);
insert all
into dim values ( '01-Jan-2006' )
into dim values ( '31-Mar-2006' )
into dim values ( '01-Apr-2006' )
into dim values ( '30-Jun-2006' )
into dim values ( '01-Jul-2006' )
into dim values ( '30-Sep-2006' )
into dim values ( '01-Oct-2006' )
into dim values ( '31-Dec-2006' )
into dim values ( '01-Jan-2007' )
into dim values ( '31-Mar-2007' )
into dim values ( '01-Apr-2007' )
into dim values ( '30-Jun-2007' )
into dim values ( '01-Jul-2007' )
into dim values ( '30-Sep-2007' )
into dim values ( '01-Oct-2007' )
into dim values ( '31-Dec-2007' )
into dim values ( '01-Jan-2008' )
into dim values ( '31-Mar-2008' )
into dim values ( '01-Apr-2008' )
into dim values ( '30-Jun-2008' )
into dim values ( '01-Jul-2008' )
into dim values ( '30-Sep-2008' )
into dim values ( '01-Oct-2008' )
into dim values ( '31-Dec-2008' )
select * from dual;
commit;
Note : for the current year it is possible to get ,
but how to get previous year last quarter dates
from first quarter of current year?
---Here is what I am able to get only for current year
SELECT DECODE (TO_CHAR (bill_date, 'Q')
, '1', '01-Jan-' || TO_CHAR (bill_date, 'YYYY')
, '2', '01-Apr-' || TO_CHAR (bill_date, 'YYYY')
, '3', '01-Jul-' || TO_CHAR (bill_date, 'YYYY')
, '4', '01-Aug-' || TO_CHAR (bill_date, 'YYYY')
) starting_date
, DECODE (TO_CHAR (bill_date, 'Q')
, '1', '31-Mar-' || TO_CHAR (bill_date, 'YYYY')
, '2', '30-Jun-' || TO_CHAR (bill_date, 'YYYY')
, '3', '30-Sep-' || TO_CHAR (bill_date, 'YYYY')
, '4', '31-Dec-' || TO_CHAR (bill_date, 'YYYY')
) ending_date
FROM dim
WHERE TO_CHAR (bill_date, 'YYYY') = TO_CHAR (SYSDATE, 'YYYY')
AND TO_CHAR (bill_date, 'Q') = TO_CHAR (SYSDATE, 'Q') - 1
AND ROWNUM <= 1
SQL> /
STARTING_DA ENDING_DATE
----------- -----------
01-Jul-2007 30-Sep-2007
-- Assuming SYSDATE is '01-JAN-2007' , then it should return '01-Oct-2006' - '31-DEC-2006'.
SQL> ed
Wrote file afiedt.buf
1 SELECT DECODE (TO_CHAR (bill_date, 'Q')
2 , '1', '01-Jan-' || TO_CHAR (bill_date, 'YYYY')
3 , '2', '01-Apr-' || TO_CHAR (bill_date, 'YYYY')
4 , '3', '01-Jul-' || TO_CHAR (bill_date, 'YYYY')
5 , '4', '01-Aug-' || TO_CHAR (bill_date, 'YYYY')
6 ) starting_date
7 , DECODE (TO_CHAR (bill_date, 'Q')
8 , '1', '31-Mar-' || TO_CHAR (bill_date, 'YYYY')
9 , '2', '30-Jun-' || TO_CHAR (bill_date, 'YYYY')
10 , '3', '30-Sep-' || TO_CHAR (bill_date, 'YYYY')
11 , '4', '31-Dec-' || TO_CHAR (bill_date, 'YYYY')
12 ) ending_date
13 FROM dim
14 WHERE TO_CHAR (bill_date, 'YYYY') = TO_CHAR (SYSDATE, 'YYYY')
15 AND TO_CHAR (bill_date, 'Q') = TO_CHAR ( TO_DATE('01-JAN-2007'), 'Q') - 1
16* AND ROWNUM <= 1
SQL> /
no rows selected
--Okay and here is another try
1 SELECT DECODE (TO_CHAR (bill_date, 'Q')
2 , '1', '01-Jan-' || TO_CHAR (bill_date, 'YYYY')
3 , '2', '01-Apr-' || TO_CHAR (bill_date, 'YYYY')
4 , '3', '01-Jul-' || TO_CHAR (bill_date, 'YYYY')
5 , '4', '01-Aug-' || TO_CHAR (bill_date, 'YYYY')
6 ) starting_date
7 , DECODE (TO_CHAR (bill_date, 'Q')
8 , '1', '31-Mar-' || TO_CHAR (bill_date, 'YYYY')
9 , '2', '30-Jun-' || TO_CHAR (bill_date, 'YYYY')
10 , '3', '30-Sep-' || TO_CHAR (bill_date, 'YYYY')
11 , '4', '31-Dec-' || TO_CHAR (bill_date, 'YYYY')
12 ) ending_date
13 FROM dim
14 WHERE ---TO_CHAR (bill_date, 'YYYY') = TO_CHAR (SYSDATE, 'YYYY') AND
15 TO_CHAR (bill_date, 'Q') = TO_CHAR ( TO_DATE('01-JAN-2007'), 'Q') - 1
16* AND ROWNUM <= 1
SQL> /
no rows selected
So how to get desired output like '01-Oct-2006' - '31-DEC-2006' or in case of 2008 it should be '01-Oct-2007' - '31-DEC-2007'? and so on..
Any guide will be greatly appreciated!
Thanking you all,
[Updated on: Mon, 12 November 2007 18:57] Report message to a moderator
|
|
|
|
|
|
|
|
Re: get previous quarter starting_date and ending_date [message #280408 is a reply to message #280230] |
Tue, 13 November 2007 07:50   |
ashwin_tampa
Messages: 40 Registered: October 2005
|
Member |
|
|
To cmerry's reply.
Not exactly.
What I am after is that starting_date and ending_date of previous quarter
Of the current quarter. That is to say : if my current quarter date is ’01-Jan-2008’
Then I should be able to pull starting_date ’01-Oct-2007’ and ending_date : ’31-Dec-2007’ of previous quarter. only single row output.
In my initial question I mentioned I was able to get this results only for current year
---Here is what I am able to get only for current year
SELECT DECODE (TO_CHAR (bill_date, 'Q')
, '1', '01-Jan-' || TO_CHAR (bill_date, 'YYYY')
, '2', '01-Apr-' || TO_CHAR (bill_date, 'YYYY')
, '3', '01-Jul-' || TO_CHAR (bill_date, 'YYYY')
, '4', '01-Aug-' || TO_CHAR (bill_date, 'YYYY')
) starting_date
, DECODE (TO_CHAR (bill_date, 'Q')
, '1', '31-Mar-' || TO_CHAR (bill_date, 'YYYY')
, '2', '30-Jun-' || TO_CHAR (bill_date, 'YYYY')
, '3', '30-Sep-' || TO_CHAR (bill_date, 'YYYY')
, '4', '31-Dec-' || TO_CHAR (bill_date, 'YYYY')
) ending_date
FROM dim
WHERE TO_CHAR (bill_date, 'YYYY') = TO_CHAR (SYSDATE, 'YYYY')
AND TO_CHAR (bill_date, 'Q') = TO_CHAR (SYSDATE, 'Q') - 1
AND ROWNUM <= 1
SQL> /
STARTING_DA ENDING_DATE
----------- -----------
01-Jul-2007 30-Sep-2007
But I need starting_date and ending_date of previous quarter : if my current quarter date is ’01-Jan-2008’ Then I should be able to pull starting_date ’01-Oct-2007’ and ending_date : ’31-Dec-2007’ from previous quarter.
[Updated on: Tue, 13 November 2007 07:51] Report message to a moderator
|
|
|
|
|
Re: get previous quarter starting_date and ending_date [message #280413 is a reply to message #280211] |
Tue, 13 November 2007 08:15   |
 |
rajavu1
Messages: 1574 Registered: May 2005 Location: Bangalore , India
|
Senior Member |

|
|
Is this waht you are expecting though As per you original query ?
with DATE_POOL as
(select BILL_DATE , TO_CHAR(BILL_DATE,'YYYYQDD') YYYYQ ,
DENSE_RANK() over (order by TO_CHAR(BILL_DATE,'YYYYQ') ) RNK
from orafaq_dim )
select DECODE (TO_CHAR (bill_date, 'Q')
, '1', '01-Jan-' || TO_CHAR (bill_date, 'YYYY')
, '2', '01-Apr-' || TO_CHAR (bill_date, 'YYYY')
, '3', '01-Jul-' || TO_CHAR (bill_date, 'YYYY')
, '4', '01-Aug-' || TO_CHAR (bill_date, 'YYYY')
) starting_date
, DECODE (TO_CHAR (bill_date, 'Q')
, '1', '31-Mar-' || TO_CHAR (bill_date, 'YYYY')
, '2', '30-Jun-' || TO_CHAR (bill_date, 'YYYY')
, '3', '30-Sep-' || TO_CHAR (bill_date, 'YYYY')
, '4', '31-Dec-' || TO_CHAR (bill_date, 'YYYY')
) ending_date from DATE_POOL
where rnk IN(
select rnk -1 from DATE_POOL
where TO_CHAR (bill_date, 'YYYYMM') = TO_CHAR ( TO_DATE('01-JAN-2007'), 'YYYYMM'))
AND ROWNUM <= 1

Rajuvan.
[Updated on: Tue, 13 November 2007 08:27] Report message to a moderator
|
|
|
Re: get previous quarter starting_date and ending_date [message #280415 is a reply to message #280211] |
Tue, 13 November 2007 08:26   |
 |
rajavu1
Messages: 1574 Registered: May 2005 Location: Bangalore , India
|
Senior Member |

|
|
And more precisely (without using rownum and by using Pivot concept,
with DATE_POOL as
(select BILL_DATE , TO_CHAR(BILL_DATE,'YYYYQ') YYYYQ ,
DENSE_RANK() over (order by TO_CHAR(BILL_DATE,'YYYYQ') ) RNK,
DENSE_RANK() over ( Partition by TO_CHAR(BILL_DATE,'YYYYQ') order by BILL_DATE ) RNK1
from orafaq_dim)
select MAX(DECODE(rnk1,1,BILL_DATE)) from_date,
MAX(DECODE(rnk1,2,BILL_DATE)) to_date
from DATE_POOL
where rnk IN(
select rnk -1 from DATE_POOL
where TO_CHAR (bill_date, 'YYYYMM') = TO_CHAR ( TO_DATE('01-JAN-2007'), 'YYYYMM'))

Rajuvan.
[Updated on: Tue, 13 November 2007 08:29] Report message to a moderator
|
|
|
Re: get previous quarter starting_date and ending_date [message #280418 is a reply to message #280411] |
Tue, 13 November 2007 08:29  |
ashwin_tampa
Messages: 40 Registered: October 2005
|
Member |
|
|
Thanks a lot to Chris.
And Apologizes as I did not check Chris’ code before replying.
Now it is giving me what exactly I am after.
Here it is :
SQL> ed
Wrote file afiedt.buf
1 SELECT distinct TRUNC(ADD_MONTHS(bill_date, -3), 'Q') start_prev_qtr
2 , TRUNC(bill_date, 'Q') - 1 end_prev_qtr
3 from dim
4 where
5 TO_CHAR (bill_date, 'Q') = TO_CHAR (SYSDATE, 'Q') ---1
6 and
7 ROWNUM <= 1
8* ---order by bill_date desc
SQL> /
START_PRE END_PREV_
--------- ---------
01-JUL-06 30-SEP-06
SQL>
SQL> SELECT TRUNC(ADD_MONTHS(bill_date, -3), 'Q') start_prev_qtr
2 , TRUNC(bill_date, 'Q') - 1 end_prev_qtr
3 FROM ( SELECT ADD_MONTHS(TO_DATE('01-JAN-2008', 'DD-MON-YYYY')
4 ,ROWNUM - 1) bill_date
5 FROM DUAL
6 CONNECT BY ROWNUM <= 1 );
START_PRE END_PREV_
--------- ---------
01-OCT-07 31-DEC-07
SQL> ED
Wrote file afiedt.buf
1 SELECT TRUNC(ADD_MONTHS(bill_date, -3), 'Q') start_prev_qtr
2 , TRUNC(bill_date, 'Q') - 1 end_prev_qtr
3 FROM ( SELECT ADD_MONTHS(TO_DATE('01-JAN-2006', 'DD-MON-YYYY')
4 ,ROWNUM - 1) bill_date
5 FROM DUAL
6* CONNECT BY ROWNUM <= 1 )
SQL> /
START_PRE END_PREV_
--------- ---------
01-OCT-05 31-DEC-05
SQL> ED
Wrote file afiedt.buf
1 SELECT TRUNC(ADD_MONTHS(bill_date, -3), 'Q') start_prev_qtr
2 , TRUNC(bill_date, 'Q') - 1 end_prev_qtr
3 FROM ( SELECT ADD_MONTHS(TO_DATE('01-APR-2006', 'DD-MON-YYYY')
4 ,ROWNUM - 1) bill_date
5 FROM DUAL
6* CONNECT BY ROWNUM <= 1 )
SQL> /
START_PRE END_PREV_
--------- ---------
01-JAN-06 31-MAR-06
SQL> ED
Wrote file afiedt.buf
1 SELECT TRUNC(ADD_MONTHS(bill_date, -3), 'Q') start_prev_qtr
2 , TRUNC(bill_date, 'Q') - 1 end_prev_qtr
3 FROM ( SELECT ADD_MONTHS(TO_DATE('01-NOV-2006', 'DD-MON-YYYY')
4 ,ROWNUM - 1) bill_date
5 FROM DUAL
6* CONNECT BY ROWNUM <= 1 )
SQL> /
START_PRE END_PREV_
--------- ---------
01-JUL-06 30-SEP-06
SQL> ED
Wrote file afiedt.buf
1 SELECT TRUNC(ADD_MONTHS(bill_date, -3), 'Q') start_prev_qtr
2 , TRUNC(bill_date, 'Q') - 1 end_prev_qtr
3 FROM ( SELECT ADD_MONTHS(TO_DATE('01-JAN-2007', 'DD-MON-YYYY')
4 ,ROWNUM - 1) bill_date
5 FROM DUAL
6* CONNECT BY ROWNUM <= 1 )
SQL> /
START_PRE END_PREV_
--------- ---------
01-OCT-06 31-DEC-06
SQL> ED
Wrote file afiedt.buf
1 SELECT TRUNC(ADD_MONTHS(bill_date, -3), 'Q') start_prev_qtr
2 , TRUNC(bill_date, 'Q') - 1 end_prev_qtr
3 FROM ( SELECT ADD_MONTHS(TO_DATE('01-JAN-2008', 'DD-MON-YYYY')
4 ,ROWNUM - 1) bill_date
5 FROM DUAL
6* CONNECT BY ROWNUM <= 1 )
SQL> /
START_PRE END_PREV_
--------- ---------
01-OCT-07 31-DEC-07
SQL>
Thanking you all again.
|
|
|
Goto Forum:
Current Time: Sat Feb 15 10:12:34 CST 2025
|