Home » SQL & PL/SQL » SQL & PL/SQL » need a help (oracle 9i , windows xp)
need a help [message #348423] Tue, 16 September 2008 13:33 Go to next message
vijaybalajimathivel
Messages: 1
Registered: September 2008
Junior Member
Hi

i have a live table, which is used for billing, i like to create a view to know the billed value on every hour.

sample table stru
trans_date     bill_no   Bill_amt
9/16/08 10:00   100         1500   
9/16/08 10:01   101         2500  
9/16/08 10:05   102         250 
9/16/08 10:05   103         7500 
9/16/08 11:05   104         2300
9/16/08 11:20   105         1250
9/16/08 11:25   106         1234.50

NOW i want to creat a view to show the result as given below for the system date.
 Hrs            Billed_Value
10AM -11 AM       11750.00
11AM-12PM          4784.500 

Please share your ideas & Query to create the view.
Thanks in Advance.

Regards
Vijaybalajimathivel.

[Mod-Edit: Frank added [code]-tags to improve readability]

[Updated on: Tue, 16 September 2008 23:36] by Moderator

Report message to a moderator

Re: need a help [message #348424 is a reply to message #348423] Tue, 16 September 2008 13:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: need a help [message #348433 is a reply to message #348423] Tue, 16 September 2008 14:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Use SQL*Plus and copy and paste what you tried.


Regards
Michel
Re: need a help [message #348538 is a reply to message #348423] Wed, 17 September 2008 03:00 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

You can take some idea like this :

CREATE TABLE stru (
  tRans_Date DATE,
  Bill_No    NUMBER,
  Bill_Amt   NUMBER);



SQL> insert into stru values (to_date('09/16/08 10:00','MM-DD-YY HH24:MI'),100,1500);

1 row created.

SQL> insert into stru values (to_date('09/16/08 10:01','MM-DD-YY HH24:MI'),101,2500);

1 row created.

SQL> insert into stru values (to_date('09/16/08 10:05','MM-DD-YY HH24:MI'),102,250);

1 row created.

SQL> insert into stru values (to_date('09/16/08 10:05','MM-DD-YY HH24:MI'),103,7500);

1 row created.

SQL> insert into stru values (to_date('09/16/08 11:00','MM-DD-YY HH24:MI'),104,2300);

1 row created.

SQL> insert into stru values (to_date('09/16/08 11:20','MM-DD-YY HH24:MI'),105,1250);

1 row created.

SQL> insert into stru values (to_date('09/16/08 11:25','MM-DD-YY HH24:MI'),106,1234);

1 row created.

SQL> commit;

Commit complete.


SQL> select to_char(trans_date,'MM/DD/YYYY'),to_char(trans_date,'HH') Hrs,sum(bill_amt) Billed_amt
  2  from stru
  3  group by to_char(trans_date,'MM/DD/YYYY'),to_char(trans_date,'HH');

TO_CHAR(TR HR BILLED_AMT
---------- -- ----------
09/16/2008 10      11750
09/16/2008 11       4784
09/17/2008 12       5000
Re: need a help [message #348542 is a reply to message #348423] Wed, 17 September 2008 03:18 Go to previous message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

I have forgotten to copy this record for test cases.

SQL> insert into stru values (sysdate,1000,5000);

1 row created.

SQL> commit;

Commit complete.


Regards
Harshad
Previous Topic: doubt about returning
Next Topic: how to retrieve date with timezone
Goto Forum:
  


Current Time: Thu Dec 08 03:44:22 CST 2016

Total time taken to generate the page: 0.07495 seconds