jeudi 16 décembre 2021

Case statement sum time difference return zero with multiple conditions

I am working with laravel eloquent query with this case statement structure (sum time difference between start and end time, if records created within a current week in timelogs table and project is billable in projects table)result return always zero. please help I don't understand.

$billable_data = Project::where(['active' => 1, 'company_id' => session('company_id'), 'is_deleted' => 0])
        ->join('timelogs','timelogs.project_id','=','projects.id')
        ->select(DB::raw('CASE WHEN (billable = 1 AND timelogs.created_at BETWEEN "'.$monday.' 00:00:00" AND "'.$friday.' 23:59:59") THEN SUM(TIMESTAMPDIFF(second, timelogs.start_time, timelogs.end_time))  ELSE 0 END as billable_hours,
            CASE WHEN (billable = 0 AND timelogs.created_at BETWEEN "'.$monday.' 00:00:00" AND "'.$friday.' 23:59:59") THEN SUM(TIMESTAMPDIFF(second, timelogs.start_time, timelogs.end_time))  ELSE 0 END as non_billable_hours'))
        ->first()->toArray();

dd result of $billable_data :

array:3 [▼
"billable_hours" => "0"
"non_billable_hours" => "0"
]

But when I used this raw query within DB::raw everything worked fine (return time sum correctly within a week).

$billable_data = Project::where(['active' => 1, 'company_id' => session('company_id'), 'is_deleted' => 0])
        ->join('timelogs','timelogs.project_id','=','projects.id')
        ->select(DB::raw('(SELECT SUM(TIMESTAMPDIFF(second, start_time, end_time))  FROM timelogs WHERE timelogs.created_at BETWEEN "'.$monday.' 00:00:00" AND "'.$friday.' 23:59:59" AND  projects.billable = 0 GROUP BY projects.id) as non_billable_hours,
            (SELECT SUM(TIMESTAMPDIFF(second, start_time, end_time))  FROM timelogs WHERE timelogs.created_at BETWEEN "'.$monday.' 00:00:00" AND "'.$friday.' 23:59:59" AND  projects.billable = 1 GROUP BY projects.id) as billable_hours'))
        ->first()->toArray();


via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire