So, I have a view report display that filters Period From and Period To, as shown below:

And, I have employee data with dates like this:

Then, when I view the report, the end_date for February doesn't appear because the Period From and To process is only in January. It should appear because the start_date is in January.

This is my query, please help.

$this->db->select("a.id, a.employee_id, a.employee_name, a.leave_name, a.start_date, a.end_date, a.status, 
                   b.id, b.employee_id, b.job_title_name, b.employment_status_name")
         ->join('hr_employee b', 'a.employee_id = b.employee_id', 'left')
         ->where("a.start_date AND a.end_date BETWEEN '$data[from]' AND '$data[to]'");

   return $this->db->get('hr_leaves a');

CodePudding user response：

The start date is indeed between the two date parameters, but you do NOT filter on start date, only on end date! All you require from start date that it is to be set to a non-zero value.

Since end date does not fall into the date range specified by the parameters, the highlighted row is filtered out.

You should either

1. filter on the start date only ("a.start_date BETWEEN '$data[from]' AND '$data[to]') or
2. filter on both start and end dates, but with an or operator, otherwise the highlighted row will not be returned ("(a.start_date BETWEEN '$data[from]' AND $data[to]) OR (a.end_date BETWEEN '$data[from]' AND '$data[to]')).

Please also note that you should use parameters and not string interpolation to include the from and to parameters in the query.

CodePudding user response：

Solution 1: Use this query if it works:

->where("('$data[from]' BETWEEN a.start_date AND a.end_date) OR ('$data['to']' BETWEEN a.start_date AND a.end_date");

Solution 2: create a date interval and query each date between start and end date of employee if true then add to list $sql_res.

    $begin = new DateTime($data['from']);
    $end = new DateTime($data['to']);
    
    $interval = DateInterval::createFromDateString('1 day');
    $period = new DatePeriod($begin, $interval, $end);
    
    $sql_res = array();
    foreach ($period as $dt) {
        $curent_date = $dt->format("Y-m-d");
        $this->db->select("a.id, a.employee_id, a.employee_name, a.leave_name, a.start_date, a.end_date, a.status, 
                   b.id, b.employee_id, b.job_title_name, b.employment_status_name")
         ->join('hr_employee b', 'a.employee_id = b.employee_id', 'left')
         ->where("$curent_date BETWEEN a.start_date AND a.end_date");

         $sql_res[] = $this->db->get('hr_leaves a')->row();
    }

    return $sql_res;

CodePudding user response：

If you want the period filter to find any employee data with a date range overlapping at all, you want:

where least(a.end_date, <period-end-date>) >= greatest(a.start_date, <period-start-date>)