Display dates from Leave Date and From and to date

Question

I have two tables one have employee name, employee id and another one is tblleaves have empid,Leave_Date, fromDate, toDate, Description.

If employee choose one date leave it stores the date value to Leave_Date and if employee choose multiple dates it store value of from date and to date.

The output page I want an employee name, Leave Days and Leave Dates. Leave Dates have dates from Leave_date, FromDate and ToDate.

<?php 


        if(isset($_POST['apply'])){

        $ym=$_POST['month'];
        list($Year, $Month) = explode("-", "$ym", 2);

        $sql = "SELECT 
       tblemployees.FirstName,
       tblemployees.LastName,
       count(tblleaves.empid) as Leave_Days,
       GROUP_CONCAT( tblleaves.Leave_Date SEPARATOR ', ' ) AS leave_dates
    FROM
       tblleaves
       JOIN tblemployees
          ON tblleaves.empid = tblemployees.id
    WHERE YEAR(Leave_Date) = $Year
       AND MONTH(Leave_Date) = $Month
    GROUP BY tblemployees.EmpId";

        $query = $dbh -> prepare($sql);
        $query->execute();
        $results=$query->fetchAll(PDO::FETCH_OBJ);

        $cnt=1;
        if($query->rowCount() > 0)
        {
        foreach($results as $result)
        {               ?>  
          <tr>
            <td> <?php echo htmlentities($cnt);?></td>
              <td><?php echo htmlentities($result->FirstName);?>&nbsp;<?php echo htmlentities($result->LastName);?></td>
               <td><?php echo htmlentities($result->Leave_Days);
     ?></td>
<td><?php echo htmlentities($result->leave_dates);

    ?></td><?php $cnt++;}}}?>
</tr>
</tbody>
</table>

The output i want:

  employee name     Leave Days      Leave Dates 
    KrishnanR            3              12-06-2019, 13-06-2019, 14-06-2019
                                         (FromDate and ToDate)
    PrakashR             1              12-06-2019
                                         (Leave_Date)

    SelvaK               3       12-06-2019,13-06-2019&14-06-2019|14-06-2019
                                     (FromDate and ToDate) (Leave_Date)

Answer 1

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)