Get the number of cases taking 1 for every 3 days of the group

Question

I have a table for employees who are usually late for work. I need to send a report to Human Resources, which indicates each user who is late, given that I can simply count the warning for each user if this user is 3 days late at least 1 time during the period.

The first data I need is the total number of alerts that will be sent to the HR manager to evaluate the global “lateness”.

Users who are only one day late will receive one warning, but if they are late twice or more, the warnings they receive depend on whether they received a warning within 3 days from the first day.

Let's look at an example:

• Joe Monday 9th
• Mark Monday 9th
• Tim Monday 9th
• Joe tuesday 10th
• Joe Wednesday 11th
• Joe Thursday 12th
• Tim Friday the 13th

As an example, take the data from the table above.

Joe will receive 2 warnings: first on Monday and the second on Thursday. Tuesday and Wednesday will be dropped because they belonged to the same 3-day period.

Mark will receive only one warning on Monday.

Tim will receive 2 warnings. First on Monday, and the second on Friday.

, SQL-, .

Answer 1

... , (Quassnoi Mr. Llama).

, , RDBMS . MySQL 5.5, SQLFiddle.

3- . , ? 3 , (mon-wed, thu-sat)? ? , wed-fri ? , (sun-wed, thu-sat) . " , -, -.

, , - . ? ( ).

:

CREATE TABLE LateEntrances (
  Employee VARCHAR(20),
  DateLate DATE
);
INSERT INTO LateEntrances VALUES('Joe' ,'2014.06.09');
INSERT INTO LateEntrances VALUES('Mark','2014.06.09');
INSERT INTO LateEntrances VALUES('Tim' ,'2014.06.09');
INSERT INTO LateEntrances VALUES('Joe' ,'2014.06.10');
INSERT INTO LateEntrances VALUES('Joe' ,'2014.06.11');
INSERT INTO LateEntrances VALUES('Joe' ,'2014.06.12');
INSERT INTO LateEntrances VALUES('Tim' ,'2014.06.13');

:

SELECT i.Employee, i.YearLate, i.WeekLate, i.PeriodLate, MIN(i.DateLate)
FROM (
  SELECT Employee, DateLate,
    YEAR(DateLate)               AS YearLate,
    WEEKOFYEAR(DateLate)         AS WeekLate,
    FLOOR(DAYOFWEEK(DateLate)/4) AS PeriodLate
  FROM LateEntrances
) i
GROUP BY i.Employee, i.YearLate, i.WeekLate, i.PeriodLate;

( SQLFiddle)

YearLate, WeekLate PeriodLate . :

SELECT i.Employee, i.PeriodLate, MIN(i.DateLate)
FROM (
  SELECT Employee, DateLate,
    CONCAT_WS('*',
      YEAR(DateLate)              ,
      WEEKOFYEAR(DateLate)        ,
      FLOOR(DAYOFWEEK(DateLate)/4)
    ) AS PeriodLate
  FROM LateEntrances
) i
GROUP BY i.Employee, i.PeriodLate;

... alltogether ( SELECT), GROUP BY:

SELECT i.Employee, MIN(i.DateLate)
FROM (
  SELECT Employee, DateLate,
    CONCAT_WS('*',
      YEAR(DateLate)              ,
      WEEKOFYEAR(DateLate)        ,
      FLOOR(DAYOFWEEK(DateLate)/4)
    ) AS PeriodLate
  FROM LateEntrances
) i
GROUP BY i.Employee, i.PeriodLate;

- , , 3 3 . .

... . , . , .