ya tienes la base piensale poquito no?
Código SQL:
Ver original--Personas con cargo vigente
SELECT t1.id, t1.nombre,t2.cargo, 'Sin Cargo Vigente' AS resultado FROM #personas AS t1
LEFT JOIN (
SELECT * FROM #cargos_personas AS t1
WHERE hasta <= getdate()
) AS t2 ON (t1.id=t2.persona)
WHERE persona IS NOT NULL
AND persona NOT IN (
SELECT persona FROM #cargos_personas AS t1
WHERE hasta >= getdATE()
)
UNION ALL
SELECT t1.id, t1.nombre,t2.cargo, 'No ha tenido Cargo' AS resultado FROM #personas AS t1
LEFT JOIN (
SELECT persona,cargo FROM #cargos_personas AS t1
) AS t2 ON (t1.id=t2.persona)
WHERE persona IS NULL
3 forosDelWeb 1 Sin Cargo Vigente
2 Camatagua123 NULL No ha tenido Cargo