MISSING DATE [message #664954] |
Tue, 15 August 2017 01:53 |
hisham99
Messages: 106 Registered: October 2008 Location: united arab emirates
|
Senior Member |
|
|
I'm making attendance sheet for the company
I need to print the report as department showing full month date even if the employee he didn't sweep his card on any certain day
how can I display the full month date for each employee in one department
I can display only the date which the employee sweep the card but I need to display the missing date (the date that employee
did not come to the company)
|
|
|
Re: MISSING DATE [message #664955 is a reply to message #664954] |
Tue, 15 August 2017 02:09 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
One way would be to OUTER JOIN your table of card sweeps to a table with a row for every date in the month. You need to provide the CREATE TABLE statements and so on if you want any more precise help.
|
|
|
Re: MISSING DATE [message #664960 is a reply to message #664955] |
Tue, 15 August 2017 04:48 |
hisham99
Messages: 106 Registered: October 2008 Location: united arab emirates
|
Senior Member |
|
|
can you send me code please (i need more help)
a) attendance table
create table hr_attend2017
(TXNID NOT NULL NUMBER ,
DEVICEID NUMBER ,
DEVICENUMBER VARCHAR2(50),
DATETIMEOFTXN VARCHAR2(200),
DATETIMEOFRECEPTION VARCHAR2(200),
DATETIMEOFPROCESSING VARCHAR2(200),
CARDNUMBER NUMBER,
FIRSTNAME VARCHAR2(200),
LASTNAME VARCHAR2(200),
EMPLOYEENUMBER VARCHAR2(200))
b) employee table
create table personnel2017
(newno number(,
pr_name varchar2(50),
pr_dept number()
c) this is the code which is used to get the in and out of each employee
select min(txnid)counter,to_date(substr(datetimeoftxn,1,10),'dd/mm/yyyy')devent,
to_number(devicenumber)devicenumber,
decode(devicenumber,0,to_char(to_date(datetimeoftxn,'dd/mm/yyyy hh24:mi:ss'),'dd/mm/yyyy hh24:mi'))in1
,decode(devicenumber,1,to_char(to_date(datetimeoftxn,'dd/mm/yyyy hh24:mi:ss'),'dd/mm/yyyy hh24:mi'))out1,pr_empno,newno
from train4.hr_attend2017,train4.personnel2017
where pr_dept=:dept
and newno between :no1 and :no2
and to_date(substr(datetimeoftxn,1,10),'dd/mm/yyyy') between :stdate and :eddate
and to_char(newno)=to_char(employeenumber)
and pr_term_dt is null
and deviceid in (1,2,12,13)
group by datetimeoftxn,devicenumber,newno
order by counter,datetimeoftxn,devicenumber;
[Updated on: Tue, 15 August 2017 04:49] Report message to a moderator
|
|
|
|
Re: MISSING DATE [message #664977 is a reply to message #664962] |
Tue, 15 August 2017 06:32 |
hisham99
Messages: 106 Registered: October 2008 Location: united arab emirates
|
Senior Member |
|
|
hr_attend2017 table is stored in sql server and we use webintegration to move the data from sql server to our oracle database daily and then we can modified
i hope if you can write an example code of how can i display missing date ,you can use emp table for example then i will modified as my need
[Updated on: Tue, 15 August 2017 06:35] Report message to a moderator
|
|
|
|
Re: MISSING DATE [message #664980 is a reply to message #664977] |
Tue, 15 August 2017 07:37 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I've told you how I approach it: outer join your table to a table of all the month's dates. There is probably a more elegant solution using analytic functions.
I'm not going to write code for you (unless you are asking for consulting services).
|
|
|