Query question. Not sure if this is possible.

Hello All, I need to run a query that return a total based on two different columns in a table. I will try and explain it the best way I can.

I have a sold_log table, the table can have two salespeople on each entry if they are both associated with the log entry. A salesperson will get credit for 1 full 'Sold Log' if they are the only salesperson on the entry and .5 if there is a second salesperson attached.

Example:

id    dealer_id	salesperson_id		salesperson_two_id
48		2		18    9
19		2		17    null
26		2		17	    18
21		2		17    18
51		2		19    null
9		2		18    null

So in the table above we will use salesperson #17 an salesperson #18.

Salesperson 17 should return a total of: 2, 1 full deal and 2 split deals. Salesperson 18 should return a total of: 2.5, 1 full deal and three split deals.

I am able to return the totals for salesperson and could refactor it for salesperson two.

public function getMtdSalespersonSold($userId, $month, $year, $isNew = true): int
    {
        return SoldLog::where('salesperson_id', $userId)
            ->whereMonth('sold_date', $month)
            ->whereYear('sold_date', $year)
            ->where('is_new', $isNew)
            ->count();
    }

Other than what I have above I am lost and cannot think of how to approach this issue. If anyone can point me in the right direction I would greatly appreacaite it.

bugsysha

4 years ago

Best Answer

Level 61

I would use the approach described here.

RayC

4 years ago

Level 10

@bugsysha thank you for pointing me in the right direction! Was able to put this together and appears to work as expected:

public function getMtdSalespersonSold($userId, $month, $year, $isNew = true)
    {
        $totalDeals = DB::table('sold_logs')
            ->selectRaw("sum(case when salesperson_id = $userId and isnull(salesperson_two_id) then 1 end) as fullDeals")
            ->selectRaw("sum(case when salesperson_id = $userId and salesperson_two_id != $userId or salesperson_two_id = $userId then .5 end) as halfDeals")
            ->whereMonth('sold_date', $month)
            ->whereYear('sold_date', $year)
            ->where('is_new', $isNew)
            ->first();

        return $totalDeals->fullDeals + $totalDeals->halfDeals;
    }

Could be an easier way to do it, but this works for now.

bugsysha

4 years ago

Level 61

@RayC great. Please mark the discussion as complete. Thanks. All the best.

Please or to participate in this conversation.