Click here to Skip to main content
15,311,717 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have spent the last 24hrs figuring how things will work.

I have three table(employeeAttendance, employeeInfor, employeeExcuse)

employeeAttendace this gives the record of all attendance made on that (EmployeeNo,Date)
employeeInfor this gives the complete record of employee information (Name, Dept etc)
employeeExcuse this gives a reason for absence when attendance is not upto requirement

SQL
SELECT employeeAttendance.EmployeeNo, COUNT( employeeAttendance.EmployeeNo ) AS `att`,CASE att when>20 then(select attreason from employeeExcuse) else 'explanation' END AS Remarks FROM employeeAttendance INNER JOIN employeeInfor ON employeeAttendance.EmployeeNo = employeeInfor.EmployeeNo GROUP BY employeeAttendance.attyear ORDER BY employeeAttendance.attmonth;


this code will give a *HINT*. My problem is i have joined employeeAttendance and employeeInfor, is working perfectly, the main issue is i want to join table employeeExcuse when employee attendance is less than requirement (let say 10) so as to check if he has an excuse days otherwise there need for an explanation

*ERROR*
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '>20 then(select att from employeeExcuse) else 'explanation' END AS Remarks' at line 1


What I have tried:

SQL
SELECT employeeAttendance.EmployeeNo, COUNT( employeeAttendance.EmployeeNo ) AS `att`,CASE att when>20 then(select attreason from employeeExcuse) else 'explanation' END AS Remarks FROM employeeAttendance INNER JOIN employeeInfor ON employeeAttendance.EmployeeNo = employeeInfor.EmployeeNo GROUP BY employeeAttendance.attyear ORDER BY employeeAttendance.attmonth
Posted
Updated 4-Jan-22 21:47pm
v4
Comments
j snooze 4-Jan-22 17:30pm
   
The SQL doesn't make much sense. In your case statement you have nothing to filter the reason for the specific employee. It selects every attreason from the employeeExcuse table which I don't think you want. What links the reason to the specific employee? Since we don't have the table layouts provided I would assume either there is an attreason field in the employeeattendance table, or an employeeNo in the employeeexcuse table(if the employeeexcuse table is a master table of all possible excuses then I'm guessing the attreason would be housed in the employeeattendance) What about if an employee uses more than one excuse do you need to see all the excuses? My recommendation is to left join to the excuse table on whatever the joining field should be. Hard to give you an exact answer without knowing anything about the tables
Member 13591430 4-Jan-22 17:37pm
   
You suggestion really work fie, but i am having a little bit problem, well let me answer some of your question. there is a bind which connect to all the three table which is the employeeNo. And only one excuse one can give in a month.
the reason am using the CASE STATEMENT is IF att(attendance_requirement)>20 (check_employeeExcuse) ElSE explanation. that is if the attendance is below expectation then there is need to check employeeExcuse whether he/she has some excuse days if not, then he needs to explain his/her absence
j snooze 4-Jan-22 17:47pm
   
Well I'm not sure of MySQL exact syntax, but in MS SQL Server or Oracle I know I can do something like this

Select employeeExcuse.EmployeeNo,subq.AbsenseCount
From employeeExcuse
Inner Join (Select employeeInfor.EmployeeNo,Count(employeeAttendance.EmployeeNo) As AbsenceCount
From employeeAttendance
Inner Join employeeFor
On employeeAttendance.EmployeeNo = employeeFor.EmployeeNo
Group By employeeAttendance.attYear
Having Count(employeeAttendance.EmployeeNo) > 20) subq
On employeeExcuse.EmployeeNo = subq.EmployeeNo

Again, not sure if its quite MySQL ready, but if my understanding is correct and you have an EmployeeNo in the employeeExcuse table, it might do the trick.

Also, should
SQL
CASE att when > 20 then

be
SQL
CASE when att > 20 then
   
Comments
Member 13591430 4-Jan-22 18:04pm
   
I have tried all, it doesn't work at all
This seems a Report that uses SQL to build required query. You have design level issues in your schema like EmployeeExecuse should be day wise excuse reason against each employee.

Anyway I tried covering most of the case with sample data. Hopefully it should give you a *Hint* so that you can modify it on your own.

select	T.EmployeeNo,T.att ,
		CASE when T.att < 4 then (select attreason from (SELECT 'A0002' as EmployeeNo, 'AttReason1' as attreason ) employeeExcuse where EmployeeNo = T.EmployeeNo) else 'explanation' END AS Remarks 
from
(
	SELECT	employeeAttendance.EmployeeNo, 
			COUNT( employeeAttendance.EmployeeNo ) AS 'att'
			--CASE att when>20 then (select attreason from employeeExcuse ) else 'explanation' END AS Remarks 
	FROM (
		SELECT 'A0001' as EmployeeNo, '01-Jan-2021' as AttDate, 2021 as attyear, 1 as attmonth
		UNION ALL SELECT 'A0001' as EmployeeNo, '02-Jan-2021' as AttDate, 2021 as attyear, 1 as attmonth
		UNION ALL SELECT 'A0001' as EmployeeNo, '03-Jan-2021' as AttDate, 2021 as attyear, 1 as attmonth
		UNION ALL SELECT 'A0001' as EmployeeNo, '04-Jan-2021' as AttDate, 2021 as attyear, 1 as attmonth
		UNION ALL SELECT 'A0001' as EmployeeNo, '05-Jan-2021' as AttDate, 2021 as attyear, 1 as attmonth
		UNION ALL SELECT 'A0002' as EmployeeNo, '01-Jan-2021' as AttDate, 2021 as attyear, 1 as attmonth
	) employeeAttendance
	INNER JOIN (
		SELECT 'A0001' as EmployeeNo, 'EmployeeName1' as Name, 'Dept1'as Dept
		UNION ALL
		SELECT 'A0002' as EmployeeNo, 'EmployeeName2' as Name, 'Dept2'as Dept
	) employeeInfor ON employeeAttendance.EmployeeNo = employeeInfor.EmployeeNo 
	GROUP BY employeeAttendance.EmployeeNo, employeeAttendance.attyear 
	--ORDER BY employeeAttendance.attmonth;
) T
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900