Advance Query Builder Where clause with given parameter

Hi All, I am having the problem on how to create an equivalent Laravel Query with the intention of having the data generated by below:

SELECT EmpID, 
CASE WHEN LastVacDate IS NULL THEN DATEADD(dd, tblRotation.DayOn, tblEmployee.StartDate) 
ELSE DATEADD(dd, tblRotation.DayOn, tblEmployee.LastVacDate) 
END AS ForeseenVacation
FROM tblEmployee INNER JOIN tblRotation ON tblEmployee.RotationID = tblRotation.RotationID
WHERE CASE WHEN LastVacDate IS NULL THEN DATEADD(dd, tblRotation.DayOn, tblEmployee.StartDate) 
ELSE DATEADD(dd, tblRotation.DayOn, tblEmployee.LastVacDate) 
END >= $params['ParamDate']

The table structure are the following:

tblEmployee
EmpID   RotationID  StartDate   LastVacDate
=====   =========   =========   ===========
1       1           01/06/2019  15/09/2019
2       1           02/06/2019  15/09/2019
3       2           02/07/2019  16/09/2019
4       2           01/10/2019  

tblRotation
RotationID  DayOn DayOff
=========   ======   ======
1           90          21
2           75          16

My query builder starts from below:

$all_data = DB::table('tblEmployee')->join('tblRotation', 'tblEmployee.EmpID', '=', 'tblRotation.RotationID');
if ($params['ParamDate']) {
    $all_data = $all_data->addSelect('tblEmployee.EmpID');
};

The purpose is to get the calculated foreseen next vacation date of the employee by giving the desired date parameter where the vacation date starts. Can anybody help, I am stuck? Thanks in advance.

aisalen

6 years ago

Best Answer

Level 1

Ok, I solved my own problem and will write the query builder I made to customize the result based on criteria.

$all_data = DB::table('tblEmployee')->join('tblRotation', 'tblEmployee.EmpID', '=', 'tblRotation.RotationID')
->select('tblEmployee.EmpID');
if ($params['ParamDate']) {
    $all_data = $all_data->addSelect(DB::raw('dateadd(dd, tblRotation.DayOn, employees.LastVacDate) as future_vac_date'));
    $all_data = $all_data->where(DB::raw('dateadd(dd, tblRotation.DayOn, employees.LastVacDate)'), '>=', AppHelper::formatDate($params['ParamDate']));
};
$all_data = $all_data->orderBy('tblEmployee.EmpID');

After many trial and error of all the possibilities, finally got it. Hope that it will help others too as all question here for me to reference are just basic where clause. I am using SQL Server as DB by the way.

Please or to participate in this conversation.