CODEWITHSUNDEEP
mysql
Bigjo
Bigjo

Reputation: 633

count by person by month between days in mysql

I have a table of absences with 3 columns id, begin_dt, end_dt. I need to give a count of how many id's has at least one day of absence in that month. So for example there is a row as follow:

id  begin_dt    end_dt
1   01/01/2020  02/02/2020
2   02/02/2020  02/02/2020

my result has to be

month   count
01-2020 1
02-2020 2

I thought with a group by on DATE_FORMAT(SYSDATE(), '%Y-%m'), but I don't know how to manage the fact that we had to look for the whole period begin_dt till end_dt

you can find a working creation of table of this example here: https://www.db-fiddle.com/f/rYBsxQzTjjQ9nGBEmeAX6W/0 Schema (MySQL v5.7)

CREATE TABLE absence (
  `id` VARCHAR(6),
  `begin_dt` DATETIME,
  `end_dt` DATETIME
);

INSERT INTO absence
  (`id`, `begin_dt`, `end_dt`)
VALUES
  ('1', DATE('2019-01-01'), DATE('2019-02-02')),
  ('2', DATE('2019-02-02'), DATE('2019-02-02'));

Query #1

    select * from absence;

| id  | begin_dt            | end_dt              |
| --- | ------------------- | ------------------- |
| 1   | 2019-01-01 00:00:00 | 2019-02-02 00:00:00 |
| 2   | 2019-02-02 00:00:00 | 2019-02-02 00:00:00 |

View on DB Fiddle

Upvotes: 1

Views: 43

Answers (1)

Akina
Akina

Reputation: 42632

SELECT DATE_FORMAT(startofmonth, '%Y-%m-01') year_and_month,
       COUNT(*) absent_person_count
FROM absence
JOIN ( SELECT DATE_FORMAT(dt + INTERVAL n MONTH, '%Y-%m-01') startofmonth,
              DATE_FORMAT(dt + INTERVAL n MONTH, '%Y-%m-01') + INTERVAL 1 MONTH - INTERVAL 1 DAY endofmonth
       FROM ( SELECT MIN(begin_dt) dt
              FROM absence ) startdate,
            ( SELECT 0 n UNION ALL 
              SELECT 1 UNION ALL 
              SELECT 2 UNION ALL 
              SELECT 3 UNION ALL 
              SELECT 4 UNION ALL 
              SELECT 5 ) numbers,
            ( SELECT DATE_FORMAT(MIN(begin_dt), '%Y-%m') mindate, 
                     DATE_FORMAT(MAX(end_dt), '%Y-%m') maxdate 
              FROM absence ) datesrange
       WHERE DATE_FORMAT(dt + INTERVAL n MONTH, '%Y-%m') BETWEEN mindate AND maxdate ) dateslist
    ON  begin_dt <= endofmonth
    AND end_dt >= startofmonth
GROUP BY year_and_month;

fiddle

Upvotes: 1

Related Questions