I'm trying to work out how to select the year and month number, of all months for the last 5 years. I want this so I can left join in some data but have all of the gaps filled.
ie..
Year Month
2014 1
2014 2
2014 3
....
2013 1
2013 2
....
I can get the last 5 years using something like this:
select extract(year from sysdate) -level year
from dual
connect by level <= 5
But I'm not having much look adding to this to get all of the months aswell. Can anyone help?
Thanks!