Hi:

Could some one help me in getting the records order by TIMESTAMP.

BA_TIMESTAMP is a DATE filed.

The issue is, we are seeing records with PM coming ahead of earlier TIME AM.

Example: A Record with a TIME of 5 PM is ahead of 6 AM and we wanted to get the
records in sequence. Like 1 AM , 2 AM ....... 1PM , 2PM , 3 PM ....

Here are the Queries we use and I would appreciate the help.

select to_char (ba_timestamp,'HH AM') Hour_of_Day,
sum(decode(ba_status,'bdone',decode
(ba_price,null,0,ba_price),0)) Amount from bill_auth0 where ba_status in
('bdone') and
srvc_prod_id = 'TOPUP' and merchant_id not in ('MoreMagic', 'cwcayman') and
ba_channel in
('SMS','WEB','WAP','ATM','J2ME') and ba_timestamp between to_date( '"+sdate+"',
'MM/DD/YYYY' ) and to_date( '"+edate+"', 'MM/DD/YYYY' ) group by
to_char(ba_timestamp,'HH
AM') order by Hour_of_Day;



SELECT to_char(ba_timestamp,'HH AM')||' - '||to_char(ba_timestamp+1/24,'HH AM')
"Hour_of_Day", sum(decode(ba_status,'bdone',decode (ba_price,null,0,ba_price),0))
"Amount",trunc(decode(sum(decode(ba_status,'bdone',1,0)),0,0,(sum(decode(ba_status,'bdone'
,decode(ba_price,null,0,ba_price),0))/sum(decode(ba_status,'bdone',1,0)))),2)
"Average",count(*) "Number_of_topup_attempts" from bill_auth0 where ba_status in
('bdone')
and srvc_prod_id = 'TOPUP' and merchant_id not in ('MoreMagic', 'cwcayman') and
ba_channel
in ('SMS','WEB','WAP','ATM','J2ME') and ba_timestamp between to_date(
$P!{StartDate},
'MM/DD/YYYY' ) and to_date( $P!{EndDate}, 'MM/DD/YYYY' ) group by
to_char(ba_timestamp,'HH
AM')||' - '||to_char(ba_timestamp+1/24,'HH AM');

Thanks
-Subbarao





Oracle LazyDBA home page