Ordering the Months By Federal Fiscal Year

When running the following query I am trying to find a way to display the returned months by federal fiscal year instead of normal sequential value.

(ie I want to display months in the following order Oct, Nov, Dec, Jan, Feb, Mar, Apr, May Jun, Jul, Aug, Sept instead of Jan thru Dec.) Thanks

select wrkgrp,
    sum (case when extract(month from reportdate) = 1 then 1 else 0 end) as January,
    sum (case when extract(month from reportdate) = 2 then 1 else 0 end) as February,
    sum (case when extract(month from reportdate) = 3 then 1 else 0 end) as March,
    sum (case when extract(month from reportdate) = 4 then 1 else 0 end) as April,
    sum (case when extract(month from reportdate) = 5 then 1 else 0 end) as May,
    sum (case when extract(month from reportdate) = 6 then 1 else 0 end) as June,
    sum (case when extract(month from reportdate) = 7 then 1 else 0 end) as July,
    sum (case when extract(month from reportdate) = 8 then 1 else 0 end) as August,
    sum (case when extract(month from reportdate) = 9 then 1 else 0 end) as September,
    sum (case when extract(month from reportdate) = 10 then 1 else 0 end) as October,
    sum (case when extract(month from reportdate) = 11 then 1 else 0 end) as November,
    sum (case when extract(month from reportdate) = 12 then 1 else 0 end) as December,
from workorder
where reportdate between to_date ('2014-10-01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
and   to_date ('2015-09-30 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
and   wrkgrp = 'PublicWorks'
group by 'wrkgrp;'

---

The fields will display in your results (horizontally) in the order you list them in your select statement. Structure your statement with Oct listed first like this:

select wrkgrp,
sum (case when extract(month from reportdate) = 10 then 1 else 0 end) as October,
sum (case when extract(month from reportdate) = 11 then 1 else 0 end) as November,
sum (case when extract(month from reportdate) = 12 then 1 else 0 end) as December,
sum (case when extract(month from reportdate) = 1 then 1 else 0 end) as January,
sum (case when extract(month from reportdate) = 2 then 1 else 0 end) as February,
sum (case when extract(month from reportdate) = 3 then 1 else 0 end) as March,
sum (case when extract(month from reportdate) = 4 then 1 else 0 end) as April,
sum (case when extract(month from reportdate) = 5 then 1 else 0 end) as May,
sum (case when extract(month from reportdate) = 6 then 1 else 0 end) as June,
sum (case when extract(month from reportdate) = 7 then 1 else 0 end) as July,
sum (case when extract(month from reportdate) = 8 then 1 else 0 end) as August,
sum (case when extract(month from reportdate) = 9 then 1 else 0 end) as September
from workorder
where reportdate between to_date ('2014-10-01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') ad to_date ('2015-09-30 00:00:00', 'yyyy/mm/dd hh24:mi:ss') and
wrkgrp = 'PublicWorks'
group by 'wrkgrp;'

---

在您的order by子句中添加case语句以获得所需的排序。

ORDER BY 
CASE WHEN extract(month from reportdate) = 10 THEN 1 
CASE WHEN extract(month from reportdate) = 11 THEN 2 
ASC 

链接地址: http://www.djcxy.com/p/65614.html

上一篇: 使用同一个表修复表中的数据不正确

下一篇: 按联邦会计年度排列月份