CODEWITHSUNDEEP
mysqlsql
vba_user
vba_user

Reputation: 125

MySQL - How to check continuity of data

I have a database containing information about sick days of employees in the following structure ( example ):

date || login

2018-01-02 || TestLogin1
2018-01-03 || TestLogin2
2018-01-04 || TestLogin5
2018-01-05 || TestLogin1
2018-01-06 || TestLogin2

And I want to check whether someone had 23 Sick Days in a row within previous 60 days.

I know how to do this in PHP, using loops , but was wondering whether there is a possibility to create this app in raw MySQL.

This is the output I want to achieve:

login || NumberOfDaysOnSickLeaveWithinPrevious2Month

TestLogin4  || 32
TestLogin7  || 30
TestLogin12 || 20
TestLogin3  || 15
TestLogin1  || 10

Will be thankful for the support,

Thanks in advance,

Upvotes: 0

Views: 1080

Answers (2)

P.Salmon
P.Salmon

Reputation: 17665

It's a lot easier to develop this if you shrink the numbers for example 2 or more continuous days absent in the last 5 days.

drop table if exists t;
create table t(employee_id int, dt date);

insert into t values
(1,'2018-07-10'),(1,'2018-07-11'),(1,'2018-07-12'),
(2,'2018-07-10'),(2,'2018-07-15'),
(3,'2018-07-10'),(3,'2018-07-11'),(3,'2018-07-13'),(3,'2018-07-14')
;

select employee_id, bn, count(*)
from
(
select t.*, concat(employee_id,year(dt) * 10000 + month(dt) * 100 + day(dt))
         - @p = 1 diff,
         if(
         concat(employee_id,year(dt) * 10000 + month(dt) * 100 + day(dt))
         - @p = 1 ,@bn:=@bn,@bn:=@bn+1) bn,
         @p:=concat(employee_id,year(dt) * 10000 + month(dt) * 100 + day(dt)) p
from t
cross join (select @bn:=0,@p:=0) b
where dt >= date_add(date(now()), interval  -5 day) 
order by employee_id,dt
) s
group by employee_id,bn having count(*)  >= 2 ;

+-------------+------+----------+

| employee_id | bn   | count(*) |
+-------------+------+----------+
|           1 | 1    |        3 |
|           3 | 4    |        2 |
|           3 | 5    |        2 |
+-------------+------+----------+
3 rows in set (0.06 sec)

Note the use of variables to work out a block number ,and the having clause. Concating employee and date creates a psuedo key and simplifies calculation.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270793

Your sample data suggests that you just want aggregation:

select login,
       count(*) as NumberOfDaysOnSickLeaveWithinPrevious2Month
from t
where date >= curdate() - interval 2 month
group by login;

That has nothing to do with "consecutive days". But your sample data doesn't even show two days in a row with the same login -- nor even any dates within the past two months.

Upvotes: 1

Related Questions