SQL & PL/SQL

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

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.

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

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

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

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

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