MySQL - How to check continuity of data

Question

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,

What's your expect result? Can you show us your expect output's sample? — Frank AK, Jul 15 '18 at 10:38
I guess you should also have a another table , maybe call `user_table` or something else. and if it is true, you have to provide it to us. Because if there are a person didn't login any of the previous's 60 days, will couldn't be into this table. — Frank AK, Jul 15 '18 at 11:02
@vba_user . . . You sample result, your question, and the names of your columns are not consistent. I can't figure out what you are trying to do. Do you want the number of days on sick leave in the lsat 60 days? Or do you want to know if there were 23 days in a row in the last 60 days? If the latter, what if the period starts more than 60 days ago, but ends in the last 60 days? — Gordon Linoff, Jul 15 '18 at 12:02
Perhaps you are looking for gaps or "islands" in a sequence... https://stackoverflow.com/questions/1312101/how-do-i-find-a-gap-in-running-counter-with-sql — Victor Di Leo, Jul 15 '18 at 14:11

P.Salmon · Answer 1 · 2018-07-15T12:16:28.717

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.

score 1 · Answer 2 · answered Jul 15 '18 at 12:05

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.

MySQL - How to check continuity of data

2 Answers2