Forum Stats

  • 3,757,576 Users
  • 2,251,248 Discussions
  • 7,869,867 Comments

Discussions

Display YEARS between two dates

A.Mohammed Rafi
A.Mohammed Rafi Member Posts: 328
edited Mar 17, 2010 8:33AM in SQL & PL/SQL
Dear Reader,

There are two requirements for which I need you help.

Requirement 1:

Based on the from and to date the years should be displayed.

For example the from date is 01-JAN-07 and end date is 31-SEP-10 the query should return as follows :

2007
2008
2009
2010

Requirement 2 :

For example the from date is 01-JAN-07 and end date is 31-FEB-08 the query should return as follows :

2007-Quarter1
2007-Quarter2
2007-Quarter3
2007-Quarter4
2008-Quarter1
2008-Quarter2
2008-Quarter3
2008-Quarter4

Kindly help in providing the soltuion.

Regards

Answers

  • 728534
    728534 Member Posts: 1,386
    Hi,
    requirement1 Solution:
    with dat as(
    select to_date('01-JAN-2007','DD-MON-YYYY') from_date,to_date('30-SEP-2010','DD-MON-YYYY') end_date from dual)
    select 
    to_char(from_date,'YYYY')+level-1 yea
    
     from dat
    connect by level <= to_char(end_date,'YYYY')-to_char(from_date,'YYYY')+1
    Requirement2 Solution:
    with dat as(
    select to_date('01-JAN-2007','DD-MON-YYYY') from_date,to_date('28-FEB-2008','DD-MON-YYYY') end_date from dual)
    select distinct to_char(from_date+level-1,'YYYY'),
    decode(to_char(from_date+level-1,'Q'),'1','QUARTER1',2,'QUARTER2',3,'QUARTER3',4,'QUARTER4','DONT KNOW') Quarters
     from dat
     connect by level <= add_months(trunc(end_date,'YYYY'),12)-trunc(from_date,'YYYY')
     order by to_char(from_date+level-1,'YYYY'),
     decode(to_char(from_date+level-1,'Q'),'1','QUARTER1',2,'QUARTER2',3,'QUARTER3',4,'QUARTER4','DONT KNOW')
    I will assume you have a version where with and connect by level works.

    Regards,
    Bhushan
    728534
  • Excellent.

    For the requirement 2 I need the output in a single column as

    2007-Quarter1
    2007-Quarter2
    2007-Quarter3
    2007-Quarter4

    Can you please modify the query and send the same.

    Thanks for your help.
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    just concatenate the two columns together,... it's not that hard..
  • 728534
    728534 Member Posts: 1,386
    Hi,
    you said:
    or the requirement 2 I need the output in a single column as
    Like this?
    with dat as(
    select to_date('01-JAN-2007','DD-MON-YYYY') from_date,to_date('28-FEB-2008','DD-MON-YYYY') end_date from dual)
    select distinct to_char(from_date+level-1,'YYYY')||'-'||
    decode(to_char(from_date+level-1,'Q'),'1','QUARTER1',2,'QUARTER2',3,'QUARTER3',4,'QUARTER4','DONT KNOW') Quarters
     from dat
     connect by level <= add_months(trunc(end_date,'YYYY'),12)-trunc(from_date,'YYYY')
     order by to_char(from_date+level-1,'YYYY')||'-'||
     decode(to_char(from_date+level-1,'Q'),'1','QUARTER1',2,'QUARTER2',3,'QUARTER3',4,'QUARTER4','DONT KNOW')
    Regards,
    Bhushan
  • I tried as suggested but it does not work.

    WITH dat AS(
    SELECT TO_DATE('01-JAN-2007','DD-MON-YYYY') from_date,TO_DATE('28-FEB-2008','DD-MON-YYYY') end_date FROM dual)
    SELECT DISTINCT TO_CHAR(from_date+LEVEL-1,'YYYY') YEAR ||
    DECODE(TO_CHAR(from_date+LEVEL-1,'Q'),'1','QUARTER1',2,'QUARTER2',3,'QUARTER3',4,'QUARTER4','DONT KNOW') Quarter
    FROM dat
    CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(end_date,'YYYY'),12)-TRUNC(from_date,'YYYY')
    ORDER BY TO_CHAR(from_date+LEVEL-1,'YYYY'),
    DECODE(TO_CHAR(from_date+LEVEL-1,'Q'),'1','QUARTER1',2,'QUARTER2',3,'QUARTER3',4,'QUARTER4','DONT KNOW')
  • Fantastic..it worked...

    Thanks
  • Please let me know your gmail ID or yahoo ID..need to learn more from you
  • 728534
    728534 Member Posts: 1,386
    Hi Mohammed,
    Thank you for your kind comments.
    The best way to learn is trying to answer the questions in the forum.
    There are very very experienced guys here who may have more exposure and are abreast with the latest features for a release.
    there are lot of names that come to my mind as i type this.
    If you really want to learn more try answering the questions in the forum.
    To start with you can first try to understand the solution provided and then start applying to the problems you see.

    You comer across any problems/issues/hurdles everyone here is always there to help you out.

    Happy learning :)

    Cheers!!!
    Bhushan
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Mar 17, 2010 8:33AM
    I like model clause B-)

    I guess that if increment target columns more than 1,
    model clause is more effective than recursive with clause
    select to_char(nen) || '-Quarter' || to_char(Q) as Val
      from dual
     model return updated rows
    dimension by(0 as nen,0 as Q)
    measures(0 as dummy)
    rules(dummy[for nen from extract(year from date '2007-01-01')
                          to extract(year from date '2008-02-28')
                increment 1,for Q in(1,2,3,4)] = 0);
    
    Val
    -------------
    2007-Quarter1
    2007-Quarter2
    2007-Quarter3
    2007-Quarter4
    2008-Quarter1
    2008-Quarter2
    2008-Quarter3
    2008-Quarter4
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    edited Mar 17, 2010 7:23AM
    Here are other ways:

    Requirement 1:
    SELECT to_char(:start_date, 'yyyy') - 1 + level year
    FROM   dual
    CONNECT BY level <= months_between(trunc(:end_date, 'yyyy'), trunc(:start_date, 'yyyy'))/12 + 1
    Requirement 2:
    SELECT to_char(add_months(trunc(:start_date, 'yyyy'), 3*(level -1)), 'yyyy"-Quarter"q') quarter
    FROM   dual
    CONNECT BY level <= months_between(trunc(:end_date, 'yyyy'), trunc(:start_date, 'yyyy'))/3 + 4
This discussion has been closed.