MySQL: Select data in a date range when date count exceeds a given amount

Question

I want to select a set of employees from an attendance log between two dates who by that time have come to work for more than 10 days. So that the employer may have already exceeded 10 days or may exceed 10 days during the date range that is checked. The log table (see below) contains one record for each employer for everyday.

emp_id | date
1      | 2019-01-25
1      | 2019-01-26
1      | 2019-01-27
1      | 2019-01-28
1      | 2019-01-29
1      | 2019-01-30
1      | 2019-01-31
1      | 2019-02-01
1      | 2019-02-02
1      | 2019-02-03
1      | 2019-02-04

2      | 2019-01-29
2      | 2019-01-30
2      | 2019-01-31
2      | 2019-02-01
2      | 2019-02-02
2      | 2019-02-03
2      | 2019-02-04
2      | 2019-02-05

3      | 2019-01-23
3      | 2019-01-24
3      | 2019-01-25
3      | 2019-01-26
3      | 2019-01-27
3      | 2019-01-28
3      | 2019-01-29
3      | 2019-01-30
3      | 2019-01-31
3      | 2019-02-01
3      | 2019-02-02

Selecting date range 2019-02-01 to 2019-02-05.

Emp 1 - By 2019-02-01 he has worked for 8 days.
Emp 1 - By 2019-02-04 he has worked for 11 days.
Emp 2 - By 2019-02-01 he has worked for 4 days.
Emp 2 - By 2019-02-05 he has worked for 8 days.
Emp 3 - By 2019-02-01 he has worked for 10 days.
Emp 3 - By 2019-02-05 he has worked for 14 days.

So from the above data set only Emp 1 and Emp 3 should be selected. The employer has to exceed 10 days within the date range.

I have written the following query in MySQL but it does not do the work. How should it be written?

SELECT emp_id FROM log
WHERE date BETWEEn '2019-02-01' AND '2019-02-05'
GROUP BY emp_id
HAVING COUNT(emp_id) > 14;

update your question and add the tables schema a proper data sample and the expected result .. (your actual data sample is not useful) — ScaisEdge
– ScaisEdge, Commented Feb 7, 2019 at 8:08

Salman Arshad · Accepted Answer · 2019-02-07 08:44:14Z

1

You can split the date condition between WHERE and HAVING:

SELECT emp_id
FROM log
WHERE date <= '2019-02-05' -- select all entries until this date
GROUP BY emp_id
HAVING MAX(date) >= '2019-02-01' -- employee has at least one log entry on / after this date
AND COUNT(emp_id) > 10 -- more than 10 entries between the two dates

edited Feb 7, 2019 at 8:44

answered Feb 7, 2019 at 8:29

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shah Muhammad Talha Tahir · Accepted Answer · 2019-02-07 08:24:10Z

0

I think you want records of employees whom working days are greater and equal to 10. So you can apply sub query which can formulate count of those employees and return result back to main query.

SELECT l1.emp_id FROM log l1
WHERE (select Count(*) from log l2 
         where l1.emp_id=l2.emp_id 
          && l2.date BETWEEN '2019-02-01' AND '2019-02-05') >=10

answered Feb 7, 2019 at 8:24

Shah Muhammad Talha Tahir

3561 gold badge3 silver badges11 bronze badges

Collectives™ on Stack Overflow

MySQL: Select data in a date range when date count exceeds a given amount

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related