Home » SQL & PL/SQL » SQL & PL/SQL » MISSING DATE (oracle 11g , database 11g, windows 7)
MISSING DATE [message #664954] Tue, 15 August 2017 01:53 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
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(Cool,
pr_name varchar2(50),
pr_dept number(Cool)


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 #664962 is a reply to message #664960] Tue, 15 August 2017 05:01 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your code is unreadable, please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Apart from that, I can't make any sense of it.
You are not using data types correctly, the date/time columns are obviously wrong.
The normalization is wrong, you should't be storing names in the attendance table.
Your query refers to a column pr_empno that doesn't exist.
There's probably more.

It's all a bit of a mess. Start again?
Re: MISSING DATE [message #664977 is a reply to message #664962] Tue, 15 August 2017 06:32 Go to previous messageGo to next message
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 #664978 is a reply to message #664962] Tue, 15 August 2017 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
As this has already told him many times as well as to post a complete and working test case and feedback to his topics and he refused to do it, I doubt you will have any success on this. Smile

Re: MISSING DATE [message #664980 is a reply to message #664977] Tue, 15 August 2017 07:37 Go to previous message
John Watson
Messages: 8922
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).
Previous Topic: member procedure execution
Next Topic: qualification_ind value meaning (2 threads merged by bb, including one hijacked from unrelated thread http://www.orafaq.com/forum/stt/76498/)
Goto Forum:
  


Current Time: Thu Mar 28 08:07:28 CDT 2024