I have several agents and one database table (`BackgroundJobWork`) recording the work load of every agent. This table would contain an entry per every work item.

When I want to assign a new work item I first check this table and pick up an agent with the least amount of work items already assigned to it.

When an agent is through with a work item, it changes its status to something other than assigned.

There is a twist. An agent reports its heart beat in another table (`BackgroundJobService`). If an agent fails to report it in timely fashion, its status in that table is set to 'Unresponsive'. The SQL query used to determine which agent is least loaded must examine only "live" agents.

Here is my SQL query to select a **least loaded live** agent:

```
SELECT bjw.AllocatedAgentHostName HostName, bjw.AllocatedAgentServiceName ServiceName,
  SUM(CASE WHEN bjw.WorkStatusTypeId IN (2,3,4) THEN 1 ELSE 0 END) AS InProgress
  FROM BackgroundJobWork bjw, BackgroundJobService bjs
  WHERE bjw.AllocatedAgentHostName = bjs.HostName
        AND bjw.AllocatedAgentServiceName = bjs.ServiceName
        AND bjs.AgentStatusTypeId = 2
  GROUP BY bjw.AllocatedAgentHostName, bjw.AllocatedAgentServiceName
  ORDER BY InProgress

```

Where:

* `BackgroundJobWork.WorkStatusTypeId` of 2, 3 or 4 corresponds to being assigned to the particular agent (denoted by the `HostName` + `ServiceName` pair of fields)
* `BackgroundJobService.AgentStatusTypeId` of 2 corresponds to a live agent

Unfortunately, my query has a bug. What happens if an agent has no entries at all in the `BackgroundJobWork` table? In this case, that agent is omitted from the result produced by my query, even though it is a live agent. That is an ugly bug.

I should probably turn my SQL another way round - instead of looking at the work load table (`BackgroundJobWork`) and then match it against the live status table (`BackgroundJobService`) I should first look at the latter and match against the former and this is because the live status table is guaranteed to have exactly one row per any agent.

But I am not sure and until now did not produce a satisfactory query.