Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jmohan
Starting Member
2 Posts |
Posted - 2010-12-30 : 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 tableEMPCODE GRADE ENAME1 M1 RAJ2 M2 KUMAR1 M1 RAJ2 M2 KUMARquarterly tableCODE MASTER_ID DESCP MONTH1 MONTH2 MONTH3 MONTH4 AMOUNT1 ATTN_INCENTIVE M1 4 7 10 1 10002 ATTN_INCENTIVE M2 4 7 10 1 1500leave tableEMPCODE LEAVE_FROM LEAVE_TO LEAVE_STATUS LEAVE_REASON1 2010-08-07 2010-08-07 AUTH ILL1 2010-08-21 2010-08-21 AUTH ILL1 2010-10-14 2010-10-14 AUTH ILL2 2010-08-12 2010-08-12 AUTH ILL2 2010-09-03 2010-09-03 NO TOURI wrote the following query,select adnl.amount from adnl_master adnl, emp_asgn emp, leave_brkup leavwhere 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')=0But 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
547 Posts |
Posted - 2011-01-04 : 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.htmlWhy 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 NULLREFERENCES 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_dateTo 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 joinBy 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 PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-05 : 00:58:32
|
Well i think its due to the last Check ... [SNIP]and (select count(leav.empcode) from leave_brkup leav whereleav.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 |
|
|
|
|
|
|
|