SELECT *
FROM (
/*
Se obtienen los docs. básicos que debe tener por cada integrante
*/
SELECT i.NumEntFed, i.AnioInscripcion, i.ProgFamilia,
i.Identificador, i.Nombre, i.ApellidoPaterno, i.ApellidoMaterno,
i.FechaNacimiento, i.Parentesco, i.Estudia,
pd.idDocumento, pd.prioridad, cd.abreviatura
FROM essv1.dbo.oprIntegrantes i LEFT JOIN sce.dbo.parentesco_documento pd
ON i.Parentesco = pd.claveParentesco
LEFT JOIN sceimg.dbo.catalogoDocs cd
ON pd.idDocumento = cd.idDocumento
WHERE i.NumEntFed = '2'
AND i.AnioInscripcion = '02'
AND ProgFamilia = '00001'
-- Docs. personales bás. (1), docs. seg. bás. (10).
AND pd.prioridad IN (1,10)
UNION ALL
/*
Se obtienen personas que requieren constancia de estudios,
*/
SELECT i.NumEntFed, i.AnioInscripcion, i.ProgFamilia,
i.Identificador, i.Nombre, i.ApellidoPaterno, i.ApellidoMaterno,
i.FechaNacimiento, i.Parentesco, i.Estudia,
pd.idDocumento, pd.prioridad, cd.abreviatura
FROM essv1.dbo.oprIntegrantes i LEFT JOIN sce.dbo.parentesco_documento pd
ON i.Parentesco = pd.claveParentesco
LEFT JOIN sceimg.dbo.catalogoDocs cd
ON pd.idDocumento = cd.idDocumento
WHERE i.NumEntFed = '2'
AND i.AnioInscripcion = '02'
AND i.ProgFamilia = '00001'
-- Hijo, nieto, sobrino, hijo adoptado, hijastro.
AND i.Parentesco IN (3,03,9,09,10,14,16) AND i.Discapacidad = 0
AND CAST(datediff(dd,i.FechaNacimiento,dateadd(dd,1,GETDATE())) / 365.25 AS INT) >= 18
-- Constancia de estudios, clave 11
AND pd.idDocumento = (11)
) dr
LEFT JOIN (
/*
Documentos existentes en expediente
*/
SELECT
i.Identificador,
cd.idDocumento, cd.descripcion
FROM essv1.dbo.oprIntegrantes i, sceimg.dbo.documentos d, sceimg.dbo.catalogoDocs cd
WHERE i.NumEntFed = d.numEntFed
AND i.AnioInscripcion = d.anioInscripcion
AND i.ProgFamilia = d.progFamilia
AND i.Identificador = d.idPropietario
AND d.idDocumento = cd.idDocumento
AND i.NumEntFed = '2'
AND i.AnioInscripcion = '02'
AND i.ProgFamilia = '00001'
) de ON dr.idDocumento = de.idDocumento
AND dr.Identificador = de.Identificador
WHERE dr.idDocumento= CASE
WHEN dr.idDocumento IN (8) AND EXISTS
(SELECT dii.idDocumento FROM essv1.dbo.oprIntegrantes ii, sceimg.dbo.documentos dii
WHERE ii.Identificador = dr.Identificador
AND ii.numEntFed = dr.NumEntFed
AND ii.anioInscripcion = dr.AnioInscripcion
AND ii.progFamilia = dr.ProgFamilia
AND dii.numEntFed = ii.NumEntFed
AND dii.anioInscripcion = ii.AnioInscripcion
AND dii.progFamilia = ii.ProgFamilia
AND dii.idPropietario = ii.Identificador
AND dii.idDocumento IN (8)
) THEN dr.idDocumento
WHEN dr.idDocumento IN (9) AND EXISTS
(SELECT dii.idDocumento FROM essv1.dbo.oprIntegrantes ii, sceimg.dbo.documentos dii
WHERE ii.Identificador = dr.Identificador
AND ii.numEntFed = dr.NumEntFed
AND ii.anioInscripcion = dr.AnioInscripcion
AND ii.progFamilia = dr.ProgFamilia
AND dii.numEntFed = ii.NumEntFed
AND dii.anioInscripcion = ii.AnioInscripcion
AND dii.progFamilia = ii.ProgFamilia
AND dii.idPropietario = ii.Identificador
AND dii.idDocumento IN (9)
) THEN dr.idDocumento
WHEN dr.idDocumento IN (8) AND NOT EXISTS
(SELECT dii.idDocumento FROM essv1.dbo.oprIntegrantes ii, sceimg.dbo.documentos dii
WHERE ii.Identificador = dr.Identificador
AND ii.numEntFed = dr.NumEntFed
AND ii.anioInscripcion = dr.AnioInscripcion
AND ii.progFamilia = dr.ProgFamilia
AND dii.numEntFed = ii.NumEntFed
AND dii.anioInscripcion = ii.AnioInscripcion
AND dii.progFamilia = ii.ProgFamilia
AND dii.idPropietario = ii.Identificador
AND dii.idDocumento IN (8)
) AND EXISTS
(SELECT dii.idDocumento FROM essv1.dbo.oprIntegrantes ii, sceimg.dbo.documentos dii
WHERE ii.Identificador = dr.Identificador
AND ii.numEntFed = dr.NumEntFed
AND ii.anioInscripcion = dr.AnioInscripcion
AND ii.progFamilia = dr.ProgFamilia
AND dii.numEntFed = ii.NumEntFed
AND dii.anioInscripcion = ii.AnioInscripcion
AND dii.progFamilia = ii.ProgFamilia
AND dii.idPropietario = ii.Identificador
AND dii.idDocumento IN (9)
)THEN NULL
WHEN dr.idDocumento IN (9) AND NOT EXISTS
(SELECT dii.idDocumento FROM essv1.dbo.oprIntegrantes ii, sceimg.dbo.documentos dii
WHERE ii.Identificador = dr.Identificador
AND ii.numEntFed = dr.NumEntFed
AND ii.anioInscripcion = dr.AnioInscripcion
AND ii.progFamilia = dr.ProgFamilia
AND dii.numEntFed = ii.NumEntFed
AND dii.anioInscripcion = ii.AnioInscripcion
AND dii.progFamilia = ii.ProgFamilia
AND dii.idPropietario = ii.Identificador
AND dii.idDocumento IN (9)
) AND EXISTS
(SELECT dii.idDocumento FROM essv1.dbo.oprIntegrantes ii, sceimg.dbo.documentos dii
WHERE ii.Identificador = dr.Identificador
AND ii.numEntFed = dr.NumEntFed
AND ii.anioInscripcion = dr.AnioInscripcion
AND ii.progFamilia = dr.ProgFamilia
AND dii.numEntFed = ii.NumEntFed
AND dii.anioInscripcion = ii.AnioInscripcion
AND dii.progFamilia = ii.ProgFamilia
AND dii.idPropietario = ii.Identificador
AND dii.idDocumento IN (8)
)THEN NULL
ELSE dr.idDocumento
END
ORDER BY dr.Identificador, dr.idDocumento