Query results - Month and Day on a seperate row

[jasonhuibers]

Here is my code in which will display the results as:

Month Days Month Days Month Days
Dec, 2012 7 Nov, 2012 30 Oct, 2012 19

I want to have each Month and Day on a separate line
IE:
Month Days
Dec, 2012 7
Nov, 2012 30
Oct, 2012 19

Here is my query so far:
select *
from
(select to_char(to_date('dec 7,2012', 'mon dd, yyyy'), 'Mon, yyyy') as Month, to_date('dec 7,2012', 'mm dd,yyyy') - last_day(to_date('nov 7,2012', 'mm dd,yyyy') )as Days from dual)
,
(select to_char(to_date('nov 30,2012', 'mon dd, yyyy'), 'Mon, yyyy') as Month, last_day(to_date('nov 7,2012', 'mm dd,yyyy')) - last_day(to_date('oct 12,2012', 'mm dd,yyyy')) as Days from dual)
,
(select to_char(to_date('oct 12,2012', 'mon dd, yyyy'), 'Mon, yyyy') as Month, last_day(to_date('oct 12,2012', 'mm dd,yyyy')) - to_date('oct 12,2012', 'mm dd,yyyy') as Days from dual)
;

 

[r937]

looks like you posted an oracle question in the microsoft sql server forum

but anyhow...

SELECT TO_CHAR(TO_DATE('dec 7,2012', 'mon dd, yyyy'), 'Mon, yyyy') as Month
     , TO_DATE('dec 7,2012', 'mm dd,yyyy') - 
       LAST_DAY(TO_DATE('nov 7,2012', 'mm dd,yyyy') )as Days 
  FROM dual
UNION ALL
SELECT TO_CHAR(TO_DATE('nov 30,2012', 'mon dd, yyyy'), 'Mon, yyyy') as Month
     , LAST_DAY(TO_DATE('nov 7,2012', 'mm dd,yyyy')) - 
       LAST_DAY(TO_DATE('oct 12,2012', 'mm dd,yyyy')) as Days 
  FROM dual
UNION ALL  
SELECT TO_CHAR(TO_DATE('oct 12,2012', 'mon dd, yyyy'), 'Mon, yyyy') as Month
     , LAST_DAY(TO_DATE('oct 12,2012', 'mm dd,yyyy')) - 
       TO_DATE('oct 12,2012', 'mm dd,yyyy') as Days 
  FROM dual

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jasonhuibers]

Thats perfect... thank you