Monthly Leave Report of employees

Question

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

If user choose one day leave it stores the date value to Leave_Date and if user choose multiple days it store value of from date and to date.

Now I want the monthly report of employees. In this page I want an employee name, Leave Days and Leave Dates. I tried codes but I got employee name repeatedly because they apply many leaves in that month i want to display employee name one time.

<?php 


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

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



     $sql = "SELECT distinct tblleaves.id as lid,tblemployees.FirstName,tblemployees.LastName,tblemployees.EmpId,
tblemployees.id,tblleaves.LeaveType,tblleaves.PostingDate,
tblleaves.Leave_Date from tblleaves join tblemployees on tblleaves.empid=tblemployees.id 
WHERE YEAR(Leave_Date) = 2019 AND MONTH(Leave_Date) = 6";
    echo $sql;
    $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 ?></td>
                                             <td><?php $result->Leave_Date?></td>
                                             <?php $cnt++;}}}?>

I want employee monthly leave report

employee name     Leave Days      Leave Dates 
KrishnanR            3              12-06-2019, 13-06-2019, 14-06-2019

user2834566 · Accepted Answer · 2019-06-08T13:43:23.950

0

Really that sort of formatting should be done in the report itself by having a band that occurs once per emplyee and shows the name and then within band show all the records for that employee.

However you can get a dataset containing the example result you posted by using the MySQL function GROUP_CONCAT().

The following code will give you three columns, FirstName, LastName and leave_dates with one row per employee. The leave_date column will contain all their tblleaves.Leave_Date values, separated by a comma and a space (or whatever appears after the key word SEPARATOR.

You won't need the DISTINCT

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) = 2019
   AND MONTH(Leave_Date) = 6
GROUP BY tblemployees.EmpId

edited Jun 08 '19 at 13:43

answered Jun 08 '19 at 12:41

user2834566

775
9
22

It didnt display the Leave_Date in html table – Krishnan R Jun 08 '19 at 12:49
I only gave you the sql to generate what you want, not how to display it in your report. See this SQL fiddle for it working correctly using your tables http://www.sqlfiddle.com/#!9/cb6e4c/2 (If you haven't used SQLfiddle before, go to that link, click Build Schema to make and populate the tables then click RunSQL to see it working – user2834566 Jun 08 '19 at 13:37
If it still doesn't work with your data make sure that the date is held in such a way that `YEAR(Leave_Date) = 2019 AND MONTH(Leave_Date) = 6` will actually return true sometimes – user2834566 Jun 08 '19 at 13:40
how can i get count of dates for leave days – Krishnan R Jun 08 '19 at 13:45
I've edited my answer and the SQLfiddle to also show the number of leave days, as in your example output. You add `count(tblleaves.empid) as Leave_Days,` to your select clause. The GROUP BY will do the hard work for you, counting days per employee. Also added more data in the SQLfiddle so you can see different numbers of days leave for different employees. Now you have to look at the SQLfiddle here http://www.sqlfiddle.com/#!9/098095/1 – user2834566 Jun 08 '19 at 13:51
How can i get the day of dates – Krishnan R Jun 08 '19 at 14:00
1

use the DAYNAME(date1) function. (Good MySQL tutorial here https://www.w3resource.com/mysql/mysql-tutorials.php). And the MySQL docs here https://dev.mysql.com/doc/ – user2834566 Jun 08 '19 at 14:32
Bro i have multiple leave day apply. fromdate and todate in the database how can i get that and how to get the leave dates in that – Krishnan R Jun 09 '19 at 03:32
https://stackoverflow.com/questions/56511863/monthly-report-for-leave-apply/56511931?noredirect=1#comment99610516_56511931 – Krishnan R Jun 09 '19 at 04:43
Help me in this question bro – Krishnan R Jun 09 '19 at 04:43

Monthly Leave Report of employees

1 Answers1