COUNT + JOIN WITH DIFFICULTY IN RESULT

Question

I need to get two results... I have a table with a total of 102 drivers and another table with the date he left to make deliveries and another with the arrival date...

I need to know which drivers are on the street and which are off duty.

The problem is that when I make a Join for the tables the result does not come as expected...

Example:

SELECT COUNT ('MOTORISTAS.CODIGO') 
FROM
MOTORISTAS 
WHERE 
MOTORISTAS.SITUACAO = 'A' AND
MOTORISTAS.OBS LIKE '%BAÚ%' AND
MOTORISTAS.CODIGO IN (SELECT VIAGENS.ID_MOTORISTA
            FROM VIAGENS 
            WHERE VIAGENS.ID_MOTORISTA <> 0)

ou este ...
SELECT COUNT (DISTINCT MOTORISTAS.CODIGO) 
FROM
MOTORISTAS INNER JOIN VIAGENS ON MOTORISTAS.CODIGO = VIAGENS.ID_MOTORISTA
WHERE 
MOTORISTAS.SITUACAO = 'A' AND
MOTORISTAS.OBS LIKE '%BAÚ%' AND
VIAGENS.ID_MOTORISTA <> 0

Both present a result of 42

Answer 1

Try using this SQL, make the necessary modifications according to your fields:

SELECT DISTINCT 
       COUNT(IIF(((V.DATA_SAIDA IS NOT NULL)AND(V.DATA_CHEGADA IS NULL)AND(V.ID_MOTORISTA IS NOT NULL))
                ,1
                ,NULL)) AS EM_VIAGEM
      ,COUNT(IIF(((V.DATA_SAIDA IS NULL)OR(V.DATA_CHEGADA IS NOT NULL))
                ,1
                ,NULL)) AS PARADOS
  FROM      MOTORISTAS M
  LEFT JOIN VIAGENS    V ON V.ID_MOTORISTA = M.CODIGO
 WHERE M.SITUACAO = 'A' 
   AND M.OBS      LIKE '%BAÚ%'