| Author |
 Topic  |
|
|
jmohan
Starting Member
India
2 Posts |
 Posted - 12/30/2010 : 14:41:18
|
hi everyone,
I need a help on a query to retrieve the amount for 'Quarterly Attendance Incentive'. This query for pay roll project.
if employee is not absent for 3 months , he is eligible for receiving amount 1000. amount will be provided to an employee only if no leave. but one exception i,e he can take leave for illness.
employee table
EMPCODE GRADE ENAME
1 M1 RAJ
2 M2 KUMAR
1 M1 RAJ
2 M2 KUMAR
quarterly table
CODE MASTER_ID DESCP MONTH1 MONTH2 MONTH3 MONTH4 AMOUNT
1 ATTN_INCENTIVE M1 4 7 10 1 1000
2 ATTN_INCENTIVE M2 4 7 10 1 1500
leave table
EMPCODE LEAVE_FROM LEAVE_TO LEAVE_STATUS LEAVE_REASON
1 2010-08-07 2010-08-07 AUTH ILL
1 2010-08-21 2010-08-21 AUTH ILL
1 2010-10-14 2010-10-14 AUTH ILL
2 2010-08-12 2010-08-12 AUTH ILL
2 2010-09-03 2010-09-03 NO TOUR
I wrote the following query,
select adnl.amount from adnl_master adnl, emp_asgn emp, leave_brkup leav
where adnl.descp=emp.grade
and emp.empcode=leav.empcode
and adnl.master_id='ATTN_INCENTIVE'
and (month(getdate())=adnl.month1 or
month(getdate())=adnl.month2 or
month(getdate())=adnl.month3 or
month(getdate())=adnl.month4)
and (select count(leav.empcode) from leave_brkup leav where
leav.empcode=2 and leav.leave_from between '2010-07-01' and '2010-09-30'
and leav.leave_status='AUTH' and leav.leave_reason<>'ILL')=0
But the amount is not retrieved, no error comes.
what are the corrections query.
please provide the query in old join method.
Thanks in Advance,
Mohan J |
|
|
jcelko
Esteemed SQL Purist
USA
547 Posts |
Posted - 01/04/2011 : 11:45:37
|
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html
Why do you believe in a magical, universal "id" that identifies everything in the schema? In RDBMS, we have keys and not Khaballah numbers. Why does the Personnel table not have the employee id in it? You have the starting and ending dates for a leave; you can computer the number of days from them. DATE is a reserved word and well as too vague to be a data element name. Let's fix the DDL first, then worry about queries.
CREATE TABLE Timesheets
(emp_id CHAR(10) NOT NULL
REFERENCES Personnel(emp_id),
work_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (emp_id, work_date),
work_start_time TIME DEFAULT '09:00:00' NOT NULL,
work_end_time TIME DEFAULT '17:00:00' NOT NULL,
CHECK (work_start_time < work_end_time));
CREATE TABLE Personnel
(emp_id CHAR(10) NOT NULL PRIMARY KEY,
emp_name VARCHAR(35) NOT NULL,
etc);
CREATE TABLE LeaveApplications
(emp_id CHAR(10) NOT NULL
REFERENCES Personnel(emp_id)
leave_request_date DATE NOT NULL,
leave_type CHAR(3) NOT NULL
CHECK (leave_type IN (..)),
leave_status CHAR(3) NOT NULL
CHECK (leave_status IN (..)),
leave_start_date DATE NOT NULL,
leave_end_date DATE NOT NULL
CHECK (leave_start_date <= leave_end_date));
Google "Calendar table" and build yourself one. They are handy for so many problems.
SELECT C.cal_date, TP.emp_name, TP.work_status
FROM (SELECT cal_date -- preserved table
FROM Calendar
WHERE cal_date BETWEEN '2010-12-01' AND '2010-12-31') AS C
LEFT OUTER JOIN
(SELECT P.emp_id, T.work_date, 'working' AS work_status
FROM Timesheets AS T, Personnel AS P
WHERE T.emp_id = P.emp_id) AS TP
ON TP.work_date = C.cal_date
To keep it simple, get a subset of dates from the Calendar in the range you want. Join the employee's name to his time sheet (this probably ought to be VIEW). Now do your outer join
By doing doing the LEFT OUTER JOIN I am still getting the same result. I want to get the entries of T.StartTime, EndTime, Date to NULL, the employees who are absent in the given date range, means their entries will not be in the Timesheets Table.
>> If the absentees are found then I also want to check their each absent on the date, whether the entry is present in the LeaveApplications Table means whether They have applied the leaves or not? <<
We can do this with a UNION ALL on this query and one based on LeaveApplications and Personnel. But I think a better way would be to show the work status (working, on leave, sickness, etc.) as an attribute of a timesheet.
--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
|
 |
|
|
MIK_2008
Aged Yak Warrior
Pakistan
826 Posts |
Posted - 01/05/2011 : 00:58:32
|
Well i think its due to the last Check ...
[SNIP]
and (select count(leav.empcode) from leave_brkup leav where
leav.empcode=2 and leav.leave_from between '2010-07-01' and '2010-09-30'
and leav.leave_status='AUTH' and leav.leave_reason<>'ILL')=0
[SNIP]
As per given data information it seems that this the count is not equal to 0. Best way to analyse a query is to analyse the individual portion. Hope this will help you out!
By the way I agree with Jcelko and will have same advise for you too :).
Cheers!
MIK |
|
|
| |
Topic |
|
|
|
Please Help on A Query.
