Display YEARS between two dates

A.Mohammed Rafi

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

728534

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

A.Mohammed Rafi

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

just concatenate the two columns together,... it's not that hard..

728534

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

A.Mohammed Rafi

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')

A.Mohammed Rafi

Fantastic..it worked...

Thanks

A.Mohammed Rafi

Please let me know your gmail ID or yahoo ID..need to learn more from you

728534

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

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

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