Home » SQL & PL/SQL » SQL & PL/SQL » attendance sheet (oracle)
| attendance sheet [message #651187] |
Thu, 12 May 2016 08:28  |
hisham99
Messages: 106
Registered: October 2008
Location: united arab emirates
|
Senior Member |
|
|
i have finger print attendance
(COUNTER NUMBER(8),
DEVICENUMB NUMBER(8),
DEVENT DATE,
CARDNUMBER NUMBER(8),
FIRSTNAME VARCHAR2(50)
LASTNAME VARCHAR2(50))
the column DEVICENUMB has two values (0 from in and 1 for out)
the column devent show the date and time when employee in or out
i want my report to show in this way (in and out in one line)
card_number,time_in, time_out
90 8:00 4:00
i try this code but it show me two line one for in and other for out
select min(counter)counter,to_char(devent,'dd\mm\yyyy')devent,decode(Devicenumb,0,devent)in1,cardnumber
from attendance1
where cardnumber=90
group by devent,cardnumber,Devicenumb
pleas see the attachment (one excel sheet show the data for an attendance, report show the required attendance report)
[mod-edit: code tags added by bb and image inserted into message body by bb]
-
Attachment: picture.jpg
(Size: 128.04KB, Downloaded 1486 times)
[Updated on: Thu, 12 May 2016 15:15] by Moderator Report message to a moderator |
|
|
|
| Re: attendance sheet [message #651205 is a reply to message #651187] |
Thu, 12 May 2016 11:29  |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Try the following
SELECT MIN (A.Counter) Counter,
TO_CHAR (A.Devent, 'dd\mm\yyyy') Devent,
TO_CHAR (B.Devent, 'dd\mm\yyyy') Devext,
A.Cardnumber
FROM Attendance1 A, Attendance1 B
WHERE A.Cardnumber = B.Cardnumber
AND A.Devicenumb = 0
AND TRUNC (A.Devent) = TRUNC (B.Devent)
AND B.Devicenumb = 1
AND A.Cardnumber = 90
GROUP BY TO_CHAR (A.Devent, 'dd\mm\yyyy'),
TO_CHAR (B.Devent, 'dd\mm\yyyy'),
A.Cardnumber |
|
|
|
| Re: attendance sheet [message #651212 is a reply to message #651187] |
Thu, 12 May 2016 16:56 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following demonstration uses only the data for April 24th, 25th, and 30th.
-- test data:
SCOTT@orcl_12.1.0.2.0> SELECT counter, devicenumb, devent, cardnumber
2 FROM attendance1
3 ORDER BY cardnumber, devent desc, counter desc
4 /
COUNTER DEVICENUMB DEVENT CARDNUMBER
---------- ---------- ------------------- ----------
3588 1 30/04/2016 15:43:00 90
3587 1 30/04/2016 15:43:00 90
3586 1 30/04/2016 15:43:00 90
3090 0 30/04/2016 12:52:00 90
3081 1 30/04/2016 12:48:00 90
2523 0 30/04/2016 08:26:00 90
2531 1 25/04/2016 16:20:00 90
1260 0 25/04/2016 08:46:00 90
1259 0 25/04/2016 08:46:00 90
2406 1 24/04/2016 16:24:00 90
1109 0 24/04/2016 08:27:00 90
11 rows selected.
-- query:
SCOTT@orcl_12.1.0.2.0> SELECT cardnumber,
2 TO_CHAR (devent, 'DY DD/MM/YYYY') event_date,
3 TO_CHAR (time_in, 'HH24:MI') "IN",
4 TO_CHAR (time_out, 'HH24:MI') "OUT",
5 TO_CHAR (TRUNC((time_out - time_in) * 24), '09') || ':' ||
6 LTRIM (TO_CHAR ((((time_out - time_in) * 24) - TRUNC ((time_out - time_in) * 24)) * 60, '99')) hr_mi
7 FROM (SELECT cardnumber,
8 TRUNC (devent) devent,
9 MIN (DECODE (devicenumb, 0, devent)) time_in,
10 MAX (DECODE (devicenumb, 1, devent)) time_out
11 FROM (SELECT DISTINCT cardnumber, devent, devicenumb,
12 DENSE_RANK () OVER
13 (PARTITION BY cardnumber, TRUNC (devent), devicenumb
14 ORDER BY devent) dr
15 FROM attendance1)
16 GROUP BY cardnumber, TRUNC (devent), dr
17 ORDER BY cardnumber, devent, time_in, time_out)
18 /
CARDNUMBER EVENT_DATE IN OUT HR_MI
---------- ----------------------- ----- ----- -------
90 SUN 24/04/2016 08:27 16:24 07:57
90 MON 25/04/2016 08:46 16:20 07:34
90 SAT 30/04/2016 08:26 12:48 04:22
90 SAT 30/04/2016 12:52 15:43 02:51
4 rows selected.
|
|
|
|
| Re: attendance sheet [message #651280 is a reply to message #651212] |
Sat, 14 May 2016 06:41 |
hisham99
Messages: 106
Registered: October 2008
Location: united arab emirates
|
Senior Member |
|
|
thank you for your replay , but i use the last query its working fine with case i send it to you , but in case if the employee is register (in) twice in different time and after some time and then register (out) again , the attendance sheet show wrong time
please see the attachment (the picture show real scheduled data and the excel sheet show what i get when i run your query in this case)
see the in and out time in excel sheet it gives wrong read,
thank you for you replay again
[mod-edit: imagine inserted into messaged body by bb]
[Updated on: Sat, 14 May 2016 12:56] by Moderator Report message to a moderator |
|
|
|
| Re: attendance sheet [message #651291 is a reply to message #651280] |
Sat, 14 May 2016 15:06 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In the following revised code, since there are rows in your data where the devent is the same and the devicenumb is different, I have relied upon the counter to determine which came first.
-- test data:
SCOTT@orcl_12.1.0.2.0> SELECT counter, devicenumb, devent, cardnumber
2 FROM attendance1
3 ORDER BY cardnumber, devent, counter
4 /
COUNTER DEVICENUMB DEVENT CARDNUMBER
---------- ---------- ------------------- ----------
1 0 24/04/2016 07:08:00 90
2 0 24/04/2016 11:29:00 90
3 0 24/04/2016 11:35:00 90
4 1 24/04/2016 11:35:00 90
5 0 24/04/2016 14:16:00 90
6 0 24/04/2016 14:21:00 90
7 1 24/04/2016 14:21:00 90
8 0 24/04/2016 14:41:00 90
9 0 24/04/2016 14:46:00 90
10 1 24/04/2016 14:46:00 90
11 0 24/04/2016 15:23:00 90
12 0 24/04/2016 15:29:00 90
13 1 24/04/2016 15:29:00 90
1259 0 25/04/2016 08:46:00 90
1260 0 25/04/2016 08:46:00 90
2531 1 25/04/2016 16:20:00 90
2523 0 30/04/2016 08:26:00 90
3081 1 30/04/2016 12:48:00 90
3090 0 30/04/2016 12:52:00 90
3586 1 30/04/2016 15:43:00 90
3587 1 30/04/2016 15:43:00 90
3588 1 30/04/2016 15:43:00 90
22 rows selected.
-- query:
SCOTT@orcl_12.1.0.2.0> SELECT cardnumber,
2 TO_CHAR (devent, 'DY DD/MM/YYYY') event_date,
3 TO_CHAR (time_in, 'HH24:MI') "IN",
4 TO_CHAR (time_out, 'HH24:MI') "OUT",
5 TO_CHAR (TRUNC((time_out - time_in) * 24), '09') || ':' ||
6 LPAD (LTRIM (TO_CHAR ((((time_out - time_in) * 24) - TRUNC ((time_out - time_in) * 24)) * 60, '99')), 2, 0) hr_mi
7 FROM (SELECT cardnumber, TRUNC (devent) devent, MIN (time_in) time_in, time_out
8 FROM (SELECT cardnumber, devent, counter, devicenumb,
9 DECODE (devicenumb, 0, devent) time_in,
10 LEAD (DECODE (devicenumb, 1, devent) IGNORE NULLS) OVER
11 (PARTITION BY cardnumber, TRUNC (devent)
12 ORDER BY devent, counter, devicenumb) time_out
13 FROM attendance1)
14 WHERE devicenumb = 0
15 GROUP BY cardnumber, TRUNC (devent), time_out
16 ORDER BY cardnumber, devent, time_in)
17 /
CARDNUMBER EVENT_DATE IN OUT HR_MI
---------- ----------------------- ----- ----- ------------
90 SUN 24/04/2016 07:08 11:35 04:27
90 SUN 24/04/2016 14:16 14:21 00:05
90 SUN 24/04/2016 14:41 14:46 00:05
90 SUN 24/04/2016 15:23 15:29 00:06
90 MON 25/04/2016 08:46 16:20 07:34
90 SAT 30/04/2016 08:26 12:48 04:22
90 SAT 30/04/2016 12:52 15:43 02:51
7 rows selected.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 16:00:34 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
