need a help [message #348423] |
Tue, 16 September 2008 13:33  |
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 #348538 is a reply to message #348423] |
Wed, 17 September 2008 03:00   |
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  |
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
|
|
|