SQL query to calculate the number of employees active by year [duplicate]

There are two tables: one with details of employees tbl_employee and other tbl_resignation which has resignation details. I want to calculate the number of employees active in a year based on their joining and resignation date and who have worked for at least 3 months. This function doesn't always yield the right number. It is important to note that employees who have joined but have resigned yet have no corresponding entry in the second table.

Note: The calculation of the year is done from Ist July-30th June.

tbl_employee:

Emp_code	Joining_Date
86	2019-03-01
34	2014-01-08

tbl_resignation:

Emp_code	Resignation_Date
86	2019-03-01

function NumberEmployeesByYear($connect,$year){
    $query = "Select Count(Emp_Code) as count 
            from tbl_employee 
            where Joining_Date <='". 
                   ($year+1)."-06-30'  
             and Emp_Code not in (Select Emp_Code 
                                 from tbl_resignation 
                                 where ResignationDate <='". 
             ($year+1)."-03-01) ";
            
   $statement = $connect->prepare($query);
   $statement->execute();
   $result = $statement->fetchAll();
   $output=0;         
   foreach($result as $row){
    $output =$row["count"] ;
   }
   return $count;           

}

source https://stackoverflow.com/questions/67918132/sql-query-to-calculate-the-number-of-employees-active-by-year