bkreynolds48
asked on
oracle sqlplus date format
I need to format the poc_date field to be 5/1/13 instead of 20130501
select poc_date, sum(sec_05) sec_05 , sum(sec_1) sec_1, sum(sec_105) sec_105 ,sum(sec_2) sec_2, sum(sec_205) sec_205,
sum(sec_3) sec_3,sum(sec_305) sec_305, sum(sec_4) sec_4,sum(sec_405)sec_405, sum(sec_5) sec_5,sum( sec_6)sec_ 6,
sum(sec_6_plus) sec_6_plus, sum(total_plm_count) total_plms, sum(total_resp_time_sec) total_resp_sec
from
(
SELECT trunc (poc_start_timestamp)poc_d ate,
case when RESP_MSEC/plm_cnt/1000 <= 0.5 then plm_cnt else 0 end sec_05,
case when RESP_MSEC/plm_cnt/1000 > 0.5 and RESP_MSEC/plm_cnt/1000 <= 1 then plm_cnt else 0 end sec_1,
case when RESP_MSEC/plm_cnt/1000 > 1 and RESP_MSEC/plm_cnt/1000 <= 1.5 then plm_cnt else 0 end sec_105,
case when RESP_MSEC/plm_cnt/1000 > 1.5 and RESP_MSEC/plm_cnt/1000 <= 2 then plm_cnt else 0 end sec_2,
case when RESP_MSEC/plm_cnt/1000 > 2 and RESP_MSEC/plm_cnt/1000 <= 2.5 then plm_cnt else 0 end sec_205,
case when RESP_MSEC/plm_cnt/1000 > 2.5 and RESP_MSEC/plm_cnt/1000 <= 3 then plm_cnt else 0 end sec_3,
case when RESP_MSEC/plm_cnt/1000 > 3 and RESP_MSEC/plm_cnt/1000 <= 3.5 then plm_cnt else 0 end sec_305,
case when RESP_MSEC/plm_cnt/1000 > 3.5 and RESP_MSEC/plm_cnt/1000 <= 4 then plm_cnt else 0 end sec_4,
case when RESP_MSEC/plm_cnt/1000 > 4 and RESP_MSEC/plm_cnt/1000 <= 4.5 then plm_cnt else 0 end sec_405,
case when RESP_MSEC/plm_cnt/1000 > 4.5 and RESP_MSEC/plm_cnt/1000 <= 5 then plm_cnt else 0 end sec_5,
case when RESP_MSEC/plm_cnt/1000 > 5 and RESP_MSEC/plm_cnt/1000 <= 6 then plm_cnt else 0 end sec_6,
case when RESP_MSEC/plm_cnt/1000 > 6 then plm_cnt else 0 end sec_6_plus,
plm_cnt total_plm_count, resp_msec/plm_cnt/1000 total_resp_time_sec
FROM poc_STATISTIC
where poc_start_timestamp >= to_date('1-May-2013','dd-M ON-YYYY')
and poc_start_timestamp < to_date('1-Jun-2013','dd-M ON-YYYY')
)
group by poc_date
order by 1
20130501 6040 2496 1987 1221 612 290 141 73 55 40 28 51 13034 11767.3513
20130502 5858 2536 2158 1325 550 287 129 62 57 35 24 50 13071 11946.7767
select poc_date, sum(sec_05) sec_05 , sum(sec_1) sec_1, sum(sec_105) sec_105 ,sum(sec_2) sec_2, sum(sec_205) sec_205,
sum(sec_3) sec_3,sum(sec_305) sec_305, sum(sec_4) sec_4,sum(sec_405)sec_405,
sum(sec_6_plus) sec_6_plus, sum(total_plm_count) total_plms, sum(total_resp_time_sec) total_resp_sec
from
(
SELECT trunc (poc_start_timestamp)poc_d
case when RESP_MSEC/plm_cnt/1000 <= 0.5 then plm_cnt else 0 end sec_05,
case when RESP_MSEC/plm_cnt/1000 > 0.5 and RESP_MSEC/plm_cnt/1000 <= 1 then plm_cnt else 0 end sec_1,
case when RESP_MSEC/plm_cnt/1000 > 1 and RESP_MSEC/plm_cnt/1000 <= 1.5 then plm_cnt else 0 end sec_105,
case when RESP_MSEC/plm_cnt/1000 > 1.5 and RESP_MSEC/plm_cnt/1000 <= 2 then plm_cnt else 0 end sec_2,
case when RESP_MSEC/plm_cnt/1000 > 2 and RESP_MSEC/plm_cnt/1000 <= 2.5 then plm_cnt else 0 end sec_205,
case when RESP_MSEC/plm_cnt/1000 > 2.5 and RESP_MSEC/plm_cnt/1000 <= 3 then plm_cnt else 0 end sec_3,
case when RESP_MSEC/plm_cnt/1000 > 3 and RESP_MSEC/plm_cnt/1000 <= 3.5 then plm_cnt else 0 end sec_305,
case when RESP_MSEC/plm_cnt/1000 > 3.5 and RESP_MSEC/plm_cnt/1000 <= 4 then plm_cnt else 0 end sec_4,
case when RESP_MSEC/plm_cnt/1000 > 4 and RESP_MSEC/plm_cnt/1000 <= 4.5 then plm_cnt else 0 end sec_405,
case when RESP_MSEC/plm_cnt/1000 > 4.5 and RESP_MSEC/plm_cnt/1000 <= 5 then plm_cnt else 0 end sec_5,
case when RESP_MSEC/plm_cnt/1000 > 5 and RESP_MSEC/plm_cnt/1000 <= 6 then plm_cnt else 0 end sec_6,
case when RESP_MSEC/plm_cnt/1000 > 6 then plm_cnt else 0 end sec_6_plus,
plm_cnt total_plm_count, resp_msec/plm_cnt/1000 total_resp_time_sec
FROM poc_STATISTIC
where poc_start_timestamp >= to_date('1-May-2013','dd-M
and poc_start_timestamp < to_date('1-Jun-2013','dd-M
)
group by poc_date
order by 1
20130501 6040 2496 1987 1221 612 290 141 73 55 40 28 51 13034 11767.3513
20130502 5858 2536 2158 1325 550 287 129 62 57 35 24 50 13071 11946.7767
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER