| problem on sql [message #8514] |
Wed, 27 August 2003 09:55 |
lara
Messages: 4
Registered: September 2002
|
Junior Member |
|
|
Hello there,
I have data like this .......
ATTENDANCE_DATE in out
-------------------- ----- -----
01-AUG-03 06:33 18:04
02-AUG-03 06:34 17:00
04-AUG-03 06:32 16:00
05-AUG-03 06:37 18:07
06-AUG-03 14:40 23:02
07-AUG-03 17:27
08-AUG-03 17:40 06:30
09-AUG-03 17:40 06:04
10-AUG-03 06:01
11-AUG-03 06:36 16:01
12-AUG-03 06:35 17:09
13-AUG-03 06:36 17:00
14-AUG-03 06:35 17:00
15-AUG-03 06:26 16:01
data of aug-7,8 and 9 should look like this.....
07-AUG-03 17:27 06:30
08-AUG-03 17:40 06:04
09-AUG-03 17:40 06:01
so, I write an sql statements like this .......
SQLWKS> create or replace view attendance_transactions_view
2> as
3> select a.id_number, a.attendance_date, a.actual_time_in,
4> DECODE(SIGN((to_number(to_char(a.actual_time_in,'HH24MI'))/1500)-1),-1,
5> a.actual_time_out,1,
6> b.OUT, a.actual_time_out)"ACTUAL_TIME_OUT",
7> a.balik_overtime_in, a.balik_overtime_out,
8> a.with_logbox_validation, a.working_time_code
9> from ATTENDANCE_TRANSACTIONS_W a,
10> ( select id_number, attendance_date, actual_time_out "OUT"
11> from ATTENDANCE_TRANSACTIONS_W)b
12> where a.id_number = b.id_number
13> and a.attendance_date = b.attendance_date - 1
14>
Statement processed.
and i got the following results.......
ATTENDANCE_DATE in out
-------------------- ----- -----
01-AUG-03 06:33 18:04
04-AUG-03 06:32 16:00
05-AUG-03 06:37 18:07
06-AUG-03 14:40 23:02
07-AUG-03 17:27 06:30
08-AUG-03 17:40 06:04
09-AUG-03 17:40 06:01
10-AUG-03
11-AUG-03 06:36 16:01
12-AUG-03 06:35 17:09
13-AUG-03 06:36 17:00
14-AUG-03 06:35 17:00
12 rows selected.
My problem is... data of aug-2 and aug-15 has gone....
Please help me.... what is wrong with my sql????
thanks,
lara
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
