Complex a little bit (attendance) MYSQL QUERY

Question

1.00/5 (1 vote)

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 4-Jan-22 11:21am

Member 13591430

Updated 4-Jan-22 21:47pm

v4

Add a Solution

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

[no name] 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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

PIEBALDconsult · Answer 1 · 2022-01-04T11:46:00

Solution 1

Also, should

SQL

CASE att when > 20 then

be

SQL

CASE when att > 20 then

Posted 4-Jan-22 11:46am

PIEBALDconsult

Comments

[no name] 4-Jan-22 18:04pm

I have tried all, it doesn't work at all

_Asif_ · Answer 2 · 2022-01-04T21:47:00

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