| need help? [message #86483] |
Mon, 04 October 2004 22:19  |
eboy
Messages: 17
Registered: August 2004
|
Junior Member |
|
|
HAI, Can anyone help me on report on calculating the total_hr
- IC -
- TOTAL_HR -
- DATE_1 -
- 123 -
- 10:20:30 -
- 05-OCT-2004 -
- 123 -
- 15:10:15 -
- 06-OCT-2004 -
- 123456 -
- 11:10:10 -
- 05-OCT-2004 -
- 123456 -
- 15:10:15 -
- 06-OCT-2004 -
- 147 -
- 10:56:21 -
- 06-OCT-2004 -
TABLE NAME = TESTING
TOTAL_HR VARHCAR2(10)
date_1 date;
function totalFormula return Number is
OVERAL_total number;
x number;
begin
select SUM(to_number(substr(:total_hr,1,2))) into X from TESTING where ic = '123' and DATE_1 BETWEEN '05-OCT-2004'AND '06-OCT-04';
OVERAL_total:= x ;
return(OVERAL_total);
end;
if i run this report with above statement, the total hr for ic(123) is = 20 and not 25.
why it cannot read to the next record which is on 06-oct-2004?
how to calculate the total hr for 05-oct-2004 and 06-oct-2004 with ic = 123. (just take 1st and 2nd digit for example)
can anyone help me.
thanks
regards
eboy
|
|
|
|
| Re: need help? [message #86486 is a reply to message #86483] |
Mon, 04 October 2004 22:42  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
Try this.
SQL> desc eboy
Name Null? Type
------------------------------- -------- ----
A NUMBER(10)
B VARCHAR2(50)
C DATE
SQL> select * from eboy;
A B C
--------- -------------------- ---------
123 10:20:30 05-OCT-04
123 15:10:25 06-OCT-04
123456 11:10:10 05-OCT-04
123456 15:10:15 06-OCT-04
147 10:56:21 06-OCT-04
SQL> select sum(to_number(substr(B,1,(instr(b,':')-1)))) from eboy
2 where a=123 and c between '05-oct-2004' and '06-oct-2004';
SUM(TO_NUMBER(SUBSTR(B,1,(INSTR(B,':')-1))))
--------------------------------------------
25
HTH
Regards
Himanshu
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
