SQL Oracle for rolling the period return
Hi ,
Software/Hardware used:
Oracle
i had requirement such that .. if i enter a period , it should return 5 other periods based on that in oracle sql query .
return of period is done as below ..
for Jan 2011 it should return march2010,june2010,sep2010,dec2010,jan2011
for feb it should return march2010,june2010,sep2010,dec2010,feb2011
for march it should return march2010,june2010,sep2010,dec2010,march2011
for April it should return june2010,sep2010,dec2010,mrch2011,april2011
So after each 3 months .. returns period must be rolled
any suggestions are welcome
Thanks in advance
Software/Hardware used:
Oracle
ASKED:
May 13, 2011 5:56 PM
UPDATED:
May 16, 2011 4:59 PM
Answer Wiki:
Depending on the database version, you could use an ugly query like this:
<pre>WITH temp AS (SELECT entered,ADD_MONTHS(entered,DECODE(MOD(TO_CHAR(entered,'mm'),3),0,0,3-MOD(TO_CHAR(entered,'mm'),3))) next
FROM (SELECT TO_DATE('01'||'&per','ddmonyyyy') entered FROM dual) t)
SELECT TO_CHAR(ADD_MONTHS(next,-12),'monyyyy') period,1 FROM temp
UNION SELECT TO_CHAR(add_months(next,-9),'monyyyy'),2 FROM temp
UNION SELECT TO_CHAR(add_months(next,-6),'monyyyy'),3 FROM temp
UNION SELECT TO_CHAR(add_months(next,-3),'monyyyy'),4 FROM temp
UNION SELECT TO_CHAR(entered,'monyyyy'),5 FROM temp
ORDER BY 2;</pre>
Maybe some kind of recursive query could be used as well.
And, I guess there are more elegant (and probably easier) ways to do it.
To see all answers submitted to the Answer Wiki: View Answer History.
Are the periods stored in a table ? If so, what is its structure ?
What have you tried ?
hello,
thanks for reply ..
No , the period must be passed at run time .. and acoording to passed parameter , it should return the value.