Modify like this...


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 to_char (ba_timestamp,'HH24 AM');


Mohan.

-----Original Message-----
From: subbarao
[mailto:oracledba-ezmlmshield-x68893746.[Email address protected]
Sent: Wednesday, January 19, 2005 3:32 PM
To: LazyDBA Discussion
Subject: Help in SQL Query - TIMESTAMP


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


Oracle LazyDBA home page