-1

I want to get count of people who leave on time in some specific date range in an attendance scenario. For that I need total employees who marked their attendance when they left office. If an employee logged at-least two times (1 for in and 2nd for out) only then he should be counted in total employees who marked their out attendance. 

SELECT Max(TIMESTAMP),COUNT(DISTINCT employee_id) totalCount FROM attendance , db2.psnl p WHERE Date(timestamp) BETWEEN STR_TO_DATE('14/05/2019', '%d/%m/%Y') AND STR_TO_DATE('14/05/2019', '%d/%m/%Y') AND  employee_id=p.MyId AND p.PayRollOfficeId=3 GROUP BY DATE(TIMESTAMP) HAVING COUNT(employee_id) >=2

For a single employee a modified version of this query works as group by filters the employee and so count >=2 will return rows when we have at-least 2 entries against employee for any date.

db data

structure

AdPy
  • 176
  • 9
  • Why are you redacting the employee ids? We need ids. They don't have to be real. See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry May 17 '19 at 06:34

1 Answers1

0

Foll query returns records of those employees who have at least logged twice a day

select
attendance_id,
max(timestamp) as last_entry_by_employee,
group_concat(timestamp),
count(*) as c
from
attendance
group by employee_id, date_format(timestamp, "%Y-%m-%d")
having c >= 2
Raja vikraman
  • 169
  • 6