RE: Help in SQL Query - TIMESTAMP

RE: Help in SQL Query - TIMESTAMP

 

  

Subbarrao-
Here's one idea... I did a little test It works...

create a view similiar to this. In your case just remove the to_char()
from the first column and the group by clause:

create view tstvw
as select creation_date credt, count(*) cnt
from conc_requests
group by creation_date
/

Then select from the view with the dates formatted with to_char
but order by the date...

select to_char(credt,'MM-DD-YYYY HH24:MI:SS'), cnt
from tstvw
order by credt
/

I think that will work for you. Let me know
Thanks
Chris

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

Hi Chris:

Thanks for the help. It worked. I realized very late that the group by
clause and the 1st column of the query are not matching. Thanks for
pointing to that.

I have one more question:

From the result set below, for the DATE "01-06-2005" , records with time
"12:XX:XX PM" are displayed after the records with time "01:53:03 PM"
and etc..
Should't we see records with 12 PM first and then records with 1 PM , 2
PM , 3 PM and so on later?

Also, I thought, the records will be in order by date. In that case, I
should see the latest records first and then previuos dates one later
and so on. In that case, I should see records of the DATE 14th Jan'2005
first , then 06th
Jan'2005 next , 23rd Dec'2004 next and 22nd Dec'2004 being last. But,
below results show in a different way? How do we get the results in
proper sequence by DATE/TIMESTAMP?

I tried using the order by clause with both "asc" and "desc" and I could
not see the results I wanted. Appreciate any help regarding this.

SQL> select to_char(ba_timestamp,'MM-DD-YYYY HH:MI:SS AM') Hour_of_Day,
2 sum(decode(ba_status,'bdone',decode (ba_price,null,0,ba_price),0))
Amount
3 from bill_auth0 where ba_status in ('bdone') and srvc_prod_id =
'TOPUP' and
4 merchant_id not in ('MoreMagic', 'cwcayman') and ba_channel in
5 ('SMS','WEB','WAP','ATM','J2ME')
6 group by to_char(ba_timestamp,'MM-DD-YYYY HH:MI:SS AM'); 5 6

HOUR_OF_DAY AMOUNT
---------------------- ----------
01-06-2005 01:53:03 PM 5.22
01-06-2005 03:12:13 PM 5
01-06-2005 03:22:07 PM 10
01-06-2005 03:24:09 PM 5.1
01-06-2005 03:25:04 PM 5
01-06-2005 03:26:09 PM 10.5
01-06-2005 12:18:21 PM 10
01-06-2005 12:24:21 PM 5
01-06-2005 12:45:55 PM 5.22
01-06-2005 12:52:56 PM 5.22
01-06-2005 12:52:57 PM 5

HOUR_OF_DAY AMOUNT
---------------------- ----------
01-06-2005 12:52:58 PM 5
01-14-2005 01:58:08 PM 10
01-14-2005 02:16:17 PM 10
01-14-2005 02:16:35 PM 34
01-14-2005 02:16:51 PM 123
01-14-2005 02:17:31 PM 120
01-14-2005 02:17:39 PM 30
01-14-2005 02:17:50 PM 110
01-14-2005 04:22:34 PM 12
01-14-2005 04:22:50 PM 111
01-14-2005 04:22:59 PM 120

HOUR_OF_DAY AMOUNT
---------------------- ----------
01-14-2005 04:23:25 PM 20
12-22-2004 02:34:17 PM 5
12-22-2004 10:53:00 AM 5
12-22-2004 11:49:05 AM 5.01
12-22-2004 11:51:46 AM 10
12-22-2004 12:06:38 PM 5
12-22-2004 12:08:00 PM 5
12-22-2004 12:14:23 PM 10
12-22-2004 12:16:53 PM 5
12-22-2004 12:19:22 PM 5.02
12-22-2004 12:23:43 PM 5

HOUR_OF_DAY AMOUNT
---------------------- ----------
12-22-2004 12:25:14 PM 5.22
12-23-2004 03:19:31 PM 13.27

35 rows selected.






--------
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


--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.

Douglas County, Oregon
www.co.douglas.or.us


Oracle LazyDBA home page