RPTDATE | failcnt |
2012-12-04 04:00:00 | 600 |
2012-12-04 04:10:00 | 562 |
2012-12-04 04:20:00 | 631 |
2012-12-04 04:30:00 | 663 |
2012-12-04 04:40:00 | 644 |
2012-12-04 04:50:00 | 673 |
2012-12-04 05:00:00 | 679 |
2012-12-04 05:10:00 | 617 |
2012-12-04 05:20:00 | 633 |
2012-12-04 05:30:00 | 639 |
2012-12-04 05:40:00 | 691 |
2012-12-04 05:50:00 | 606 |
2012-12-04 06:00:00 | 603 |
2012-12-04 06:10:00 | 651 |
2012-12-04 06:20:00 | 628 |
2012-12-04 06:30:00 | 593 |
2012-12-04 06:40:00 | 552 |
2012-12-04 06:50:00 | 551 |
RPTDATE | FAILCNT |
2012-12-04 04:00:00 | 3773 |
2012-12-04 05:00:00 | 3865 |
2012-12-04 06:00:00 | 3578 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | WITH T AS ( SELECT TO_DATE( '2012-12-04 04:00:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '600' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 04:10:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '562' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 04:20:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '631' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 04:30:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '663' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 04:40:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '644' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 04:50:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '673' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 05:00:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '679' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 05:10:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '617' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 05:20:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '633' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 05:30:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '639' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 05:40:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '691' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 05:50:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '606' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 06:00:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '603' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 06:10:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '651' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 06:20:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '628' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 06:30:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '593' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 06:40:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '552' FAILCNT FROM DUAL UNION ALL SELECT TO_DATE( '2012-12-04 06:50:00' , 'YYYY-MM-DD HH24:MI:SS' ) RPTDATE, '551' FAILCNT FROM DUAL ) SELECT TO_CHAR(TRUNC(rptdate, 'HH24' ), 'yyyy-mm-dd hh24:mi:ss' ) AS rptdate , SUM (failcnt) AS failcnt FROM t GROUP BY TRUNC(rptdate, 'HH24' ) ORDER BY 1 ; |