Can't help you with the php but the mysql query could do with work.
DROP TABLE IF EXISTS T;
CREATE TABLE T
(ID INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(10),LEAVE_DATE DATE, LEAVE_FROM DATE, LEAVE_TO DATE);
INSERT INTO T (NAME,LEAVE_DATE,LEAVE_FROM,LEAVE_TO) VALUES
('A','2019-05-30','2019-05-30','2019-06-02'),
('A','2019-06-05',NULL,NULL),
('A','2019-06-06',NULL,NULL),
('A','2019-06-30','2019-06-30','2019-07-11'),
('B','2019-05-30','2019-05-30','2019-07-11'),
('C','2019-05-11','2019-05-11','2019-05-12')
;
SELECT NAME,
SUM(CASE WHEN LEAVE_FROM IS NULL THEN 1
ELSE DATEDIFF(IF(LEAVE_TO > '2019-06-30','2019-06-30',LEAVE_TO),IF(LEAVE_FROM < '2019-06-01','2019-06-01',LEAVE_FROM)) + 1
END) LEAVEDAYS,
GROUP_CONCAT(CASE WHEN LEAVE_FROM IS NULL THEN LEAVE_DATE
ELSE CONCAT(IF(LEAVE_FROM < '2019-06-01','<<2019-06-01',LEAVE_FROM),' & ',IF(LEAVE_TO > '2019-06-30','2019-06-30>>',LEAVE_TO))
END) AS LEAVEDATES
FROM T
WHERE (LEAVE_DATE BETWEEN '2019-06-01' AND '2019-06-30') OR
(LEAVE_FROM BETWEEN '2019-06-01' AND '2019-06-30') OR
(LEAVE_TO BETWEEN '2019-06-01' AND '2019-06-30') OR
(LEAVE_FROM < '2019-06-01' AND LEAVE_TO > '2019-06-30')
GROUP BY NAME
ORDER BY ID;
Note the adjustments to top and bottom the leave dates to ensure we are only counting dates in the period. Note also that I have added indicators(<< or >>) to the leave dates to indicate leave dates which begin before the period start date required or extend after the period end date required.
+------+-----------+---------------------------------------------------------------------------+
| NAME | LEAVEDAYS | LEAVEDATES |
+------+-----------+---------------------------------------------------------------------------+
| A | 5 | <<2019-06-01 & 2019-06-02,2019-06-05,2019-06-06,2019-06-30 & 2019-06-30>> |
| B | 30 | <<2019-06-01 & 2019-06-30>> |
+------+-----------+---------------------------------------------------------------------------+
2 rows in set (0.08 sec)
Can't help you with the php but the mysql query could do with work.
DROP TABLE IF EXISTS T;
CREATE TABLE T
(ID INT AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(10),LEAVE_DATE DATE, LEAVE_FROM DATE, LEAVE_TO DATE);
INSERT INTO T (NAME,LEAVE_DATE,LEAVE_FROM,LEAVE_TO) VALUES
('A','2019-05-30','2019-05-30','2019-06-02'),
('A','2019-06-05',NULL,NULL),
('A','2019-06-06',NULL,NULL),
('A','2019-06-30','2019-06-30','2019-07-11'),
('B','2019-05-30','2019-05-30','2019-07-11'),
('C','2019-05-11','2019-05-11','2019-05-12')
;
SELECT NAME,
SUM(CASE WHEN LEAVE_FROM IS NULL THEN 1
ELSE DATEDIFF(IF(LEAVE_TO > '2019-06-30','2019-06-30',LEAVE_TO),IF(LEAVE_FROM < '2019-06-01','2019-06-01',LEAVE_FROM)) + 1
END) LEAVEDAYS,
GROUP_CONCAT(CASE WHEN LEAVE_FROM IS NULL THEN LEAVE_DATE
ELSE CONCAT(IF(LEAVE_FROM < '2019-06-01','<<2019-06-01',LEAVE_FROM),' & ',IF(LEAVE_TO > '2019-06-30','2019-06-30>>',LEAVE_TO))
END) AS LEAVEDATES
FROM T
WHERE (LEAVE_DATE BETWEEN '2019-06-01' AND '2019-06-30') OR
(LEAVE_FROM BETWEEN '2019-06-01' AND '2019-06-30') OR
(LEAVE_TO BETWEEN '2019-06-01' AND '2019-06-30') OR
(LEAVE_FROM < '2019-06-01' AND LEAVE_TO > '2019-06-30')
GROUP BY NAME
ORDER BY ID;
Note the adjustments to top and bottom the leave dates to ensure we are only counting dates in the period. Note also that I have added indicators(<< or >>) to the leave dates to indicate leave dates which begin before the period start date required or extend after the period end date required.
+------+-----------+---------------------------------------------------------------------------+
| NAME | LEAVEDAYS | LEAVEDATES |
+------+-----------+---------------------------------------------------------------------------+
| A | 5 | <<2019-06-01 & 2019-06-02,2019-06-05,2019-06-06,2019-06-30 & 2019-06-30>> |
| B | 30 | <<2019-06-01 & 2019-06-30>> |
+------+-----------+---------------------------------------------------------------------------+
2 rows in set (0.08 sec)
