0

I am trying to display reports in which based on employee code, I have to display how many days he/she presents on monthly basis.

Emp_Code    Date    Attendance
1001    2023-01-01  1
1001    2023-01-02  1
1001    2023-01-03  1
1001    2023-01-04  1
1001    2023-01-05  0
1001    2023-01-06  0
1001    2023-01-07  1
1001    2023-01-08  1
.
.
.
1002    2023-01-01  1
1002    2023-01-02  0
1002    2023-01-03  1
.
.
.
1003    2023-01-01  1

Attendance is a boolean column (0,1) 0 - This means the employee is absent, and 1 Means the employee is present on that day.

SQL query to Calculate monthly report as follow:

1001 Jan 2 
1001 Feb 1 
1001 Mar 0 
1001 Apr 2

I wrote the query but shows me the number of present days, can anyone help me to correct it plz?

SELECT Emp_Code,MAX(DATENAME(MM,Date)) AS MonthWisePresentOrAbsent, COUNT(1) AS "DaysAbsent"
FROM tblEmployee GROUP BY MONTH(Date),Emp_Code;

Edit Question 1 it is a simple concept like every day in January month, 1 to 25 days if I am present then the last column contains the 1 and for 26 to 31 it contains 0.

JParmar
  • 79
  • 1
  • 2
  • 10
  • *"Attendance is a boolean column"* SQL Server doesn't have a boolean data type, do you mean a `bit`? – Thom A Jan 12 '23 at 10:56
  • As for the results you get, you have `COUNT(1)`; `COUNT` *counts* non-`NULL` values and `1` is non-`NULL` so it *counts* **every** row. You need to either use a conditional aggregate, `SUM` the value (if it's not a `bit`), or filter the results to the rows you want (the latter doesn't seem what you want here mind). – Thom A Jan 12 '23 at 10:58
  • @Larnu Yes it is a bit in SQL but for better understanding, I used the word boolean. – JParmar Jan 12 '23 at 11:02
  • `bit` *isn't* a boolean value, so it's actually more confusing, @JPalmer. `WHERE BitColumn` would error, for example. – Thom A Jan 12 '23 at 11:05
  • As the value is a `bit` column, you can't `SUM` it (like I mentioned in my prior comment), so I would suggest using a conditional `COUNT`, or you could `SUM` the value cast as an numerical data type (such as an `int`). – Thom A Jan 12 '23 at 11:09
  • @Larnu Edited the question. Please have a look. – JParmar Jan 12 '23 at 11:13
  • I've closed the question as a duplicate of a couple of existing Q&A's; they will give you the solution(s) you want/need. – Thom A Jan 12 '23 at 11:15

0 Answers0