SQL : Query to generate a calendar listing

Instead of creating and populating a table containing calendar entries, why not write a query to generate the listing at run time. For example, the last 24 months:

SELECT *
FROM
(
WITH month_counter AS 
(
SELECT LEVEL-1 AS id 
FROM   dual 
CONNECT BY LEVEL <= 24
) 
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE), -id),'YYYY')    year
,      ADD_MONTHS((SYSDATE), -id - 1) + 1 first_day
,      ADD_MONTHS(LAST_DAY(SYSDATE), -id) last_day
,      TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE), -id),'Month')   month
,      TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE), -id),'Mon-YY')  period_name
FROM   month_counter
)
;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s