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 Go to next message
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 #280214 is a reply to message #280211] Mon, 12 November 2007 17:37 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>-- Assuming SYSDATE is '01-JAN-2007' , then it should return '01-AUG-2006' - '31-DEC-2006'.
HUH?
As Far As I Know, for most folks no quarter starts on 1-Aug & for all folks a QUARTER (of a year) is THREE months; not FIVE months [August thru December inclusive].

also you table dim is a collection of dates WITHOUT any relationship to each other.
Re: get previous quarter starting_date and ending_date [message #280215 is a reply to message #280214] Mon, 12 November 2007 18:47 Go to previous messageGo to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member
My bad!

Sorry, it is a typo, actually I mean to say: '01-Oct-2006' '31-Dec-2006’, basically last quarter.

and I have shrunk the table data size where I have inserted only starting and ending date of all three years quarters.


Thanking you for looking at the topic.

[Updated on: Mon, 12 November 2007 19:01]

Report message to a moderator

Re: get previous quarter starting_date and ending_date [message #280230 is a reply to message #280211] Mon, 12 November 2007 20:26 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Is this what you are after?

SELECT bill_date
     , TRUNC(ADD_MONTHS(bill_date, -3), 'Q') start_prev_qtr
     , TRUNC(bill_date, 'Q') - 1 end_prev_qtr
FROM ( SELECT ADD_MONTHS(TO_DATE('15-JUL-2006', 'DD-MON-YYYY'), ROWNUM - 1)  bill_date
       FROM   DUAL
       CONNECT BY ROWNUM <= 18 );

BILL_DATE   START_PREV_QTR END_PREV_QTR
----------- -------------- ------------
15-JUL-2006 01-APR-2006    30-JUN-2006
15-AUG-2006 01-APR-2006    30-JUN-2006
15-SEP-2006 01-APR-2006    30-JUN-2006
15-OCT-2006 01-JUL-2006    30-SEP-2006
15-NOV-2006 01-JUL-2006    30-SEP-2006
15-DEC-2006 01-JUL-2006    30-SEP-2006
15-JAN-2007 01-OCT-2006    31-DEC-2006
15-FEB-2007 01-OCT-2006    31-DEC-2006
15-MAR-2007 01-OCT-2006    31-DEC-2006
15-APR-2007 01-JAN-2007    31-MAR-2007
15-MAY-2007 01-JAN-2007    31-MAR-2007
15-JUN-2007 01-JAN-2007    31-MAR-2007
15-JUL-2007 01-APR-2007    30-JUN-2007
15-AUG-2007 01-APR-2007    30-JUN-2007
15-SEP-2007 01-APR-2007    30-JUN-2007
15-OCT-2007 01-JUL-2007    30-SEP-2007
15-NOV-2007 01-JUL-2007    30-SEP-2007
15-DEC-2007 01-JUL-2007    30-SEP-2007
Re: get previous quarter starting_date and ending_date [message #280267 is a reply to message #280211] Tue, 13 November 2007 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table dim (bill_date  date);

Table created.

SQL> insert all
  2   into dim values ( '01-Jan-2006' )
  3   into dim values ( '31-Mar-2006' )
  4   into dim values ( '01-Apr-2006' )
  5   into dim values ( '30-Jun-2006' )
  6   into dim values ( '01-Jul-2006' )
  7   into dim values ( '30-Sep-2006' )
  8   into dim values ( '01-Oct-2006' )
  9   into dim values ( '31-Dec-2006' )
 10   into dim values ( '01-Jan-2007' )
 11   into dim values ( '31-Mar-2007' )
 12   into dim values ( '01-Apr-2007' )
 13   into dim values ( '30-Jun-2007' )
 14   into dim values ( '01-Jul-2007' )
 15   into dim values ( '30-Sep-2007' )
 16   into dim values ( '01-Oct-2007' )
 17   into dim values ( '31-Dec-2007' )
 18   into dim values ( '01-Jan-2008' )
 19   into dim values ( '31-Mar-2008' )
 20   into dim values ( '01-Apr-2008' )
 21   into dim values ( '30-Jun-2008' )
 22   into dim values ( '01-Jul-2008' )
 23   into dim values ( '30-Sep-2008' )
 24   into dim values ( '01-Oct-2008' )
 25   into dim values ( '31-Dec-2008' )
 26   select * from dual;
 into dim values ( '01-Jan-2006' )
                   *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected

'01-Jan-2006' is a string not a date.

Regards
Michel
Re: get previous quarter starting_date and ending_date [message #280333 is a reply to message #280267] Tue, 13 November 2007 04:39 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:


'01-Jan-2006' is a string not a date.


ashwin_tampa
Use to_date function for date field. I learned that from here.

[Updated on: Tue, 13 November 2007 04:41]

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 Go to previous messageGo to next message
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 #280410 is a reply to message #280333] Tue, 13 November 2007 07:53 Go to previous messageGo to next message
ashwin_tampa
Messages: 40
Registered: October 2005
Member

To Michel and Muzahidul


I have not faced this problem during inserting demo table at my schema.
But I accept it is a good practice to be followed.


Thanking you
Re: get previous quarter starting_date and ending_date [message #280411 is a reply to message #280408] Tue, 13 November 2007 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

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.

Is this not what Chris (cmerry) give?

Regards
Michel
Re: get previous quarter starting_date and ending_date [message #280413 is a reply to message #280211] Tue, 13 November 2007 08:15 Go to previous messageGo to next message
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



Thumbs Up
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 Go to previous messageGo to next message
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'))


Thumbs Up
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 Go to previous message
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.

Previous Topic: Export or import data with CSV file
Next Topic: need to show lot no. along with supplier name
Goto Forum:
  


Current Time: Thu Dec 08 16:13:36 CST 2016

Total time taken to generate the page: 0.12511 seconds