order by to_char(ba_timestamp, 'yyyymmddhh24miss')

Mir M. Mirhashimali
Oracle Systems Manager
Web Services, Rice University
(713) 348-6365



subbarao wrote:

>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
>
>
>
>
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
>
>!DSPAM:41eec1fd96832452016434!
>
>.
>
>
>

Oracle LazyDBA home page