Consulta recursiva

[rvaldenegro]

Holas,

Después de investigar un rato encontré una solución, requiere de dos pasos: 1 función recursiva para obtener las estructuras y una consulta CTE para obtener la cantidad de personas.

Queda así:

Código SQL:

ALTER FUNCTION [dbo].[udf_recorreEstructura](
    @pm_est_org_id INT
)
RETURNS @tmpEstOrg TABLE ( 
    est_org_id INTEGER
    , est_org_id_padre INTEGER NULL
    , niv_org_nivel VARCHAR(150)
    , niv_org_nombre VARCHAR(100)
    , reg_id INTEGER NULL
    , ciu_id INTEGER NULL
    , com_id INTEGER NULL
    , est_org_nombre VARCHAR(100)
    , est_org_descripcion VARCHAR(200) NULL 
    , est_org_fec_creacion datetime NULL
    , est_org_activa bit NULL
)   
AS
BEGIN
    --primero verificamos que venga un entero valido
    IF (isnumeric(@pm_est_org_id)=1 AND @pm_est_org_id>0)
    BEGIN
        DECLARE @id_estructura INTEGER;
        SET @id_estructura = CONVERT(INTEGER, @pm_est_org_id);
        
        IF (@id_estructura>0)
        BEGIN
            INSERT INTO @tmpEstOrg (
                est_org_id, est_org_id_padre, niv_org_nivel, niv_org_nombre, reg_id, ciu_id, com_id
                , est_org_nombre, est_org_descripcion, est_org_fec_creacion, est_org_activa
            ) SELECT eo.est_org_id, eo.est_org_id_padre, eo.niv_org_nivel, niv.niv_org_nombre
            , eo.reg_id, eo.ciu_id, eo.com_id
            , eo.est_org_nombre, eo.est_org_descripcion, eo.est_org_fec_creacion, eo.est_org_activa
            FROM tbl_estructura_organizativa eo
            LEFT JOIN tbl_nivel_organizativo niv ON niv.niv_org_nivel=eo.niv_org_nivel
            WHERE est_org_id_padre=@id_estructura;
            
            INSERT INTO @tmpEstOrg (
                est_org_id, est_org_id_padre, niv_org_nivel, niv_org_nombre, reg_id, ciu_id, com_id
                , est_org_nombre, est_org_descripcion, est_org_fec_creacion, est_org_activa
            ) SELECT f.est_org_id, f.est_org_id_padre, f.niv_org_nivel, f.niv_org_nombre
            , f.reg_id, f.ciu_id, f.com_id
            , f.est_org_nombre, f.est_org_descripcion, f.est_org_fec_creacion, f.est_org_activa
            FROM @tmpEstOrg t
            CROSS APPLY dbo.udf_recorreEstructura(t.est_org_id) f
        END
    END
    RETURN
END
 

Y luego la consulta CTE

Código SQL:

WITH 
cteEstructura (
    est_org_id, est_org_nombre
) AS (
    SELECT est_org_id, est_org_nombre
    FROM tbl_estructura_organizativa
    WHERE est_org_id_padre=0
    OR est_org_id_padre IS NULL
),
cteInscritos (
    est_org_id, inscritos
) AS (
    SELECT e.est_org_id, COUNT(p.rut_persona) AS total
    FROM cteEstructura e
    CROSS APPLY dbo.udf_recorreEstructura(e.est_org_id) f 
    INNER JOIN tbl_persona p ON p.est_org_id=f.est_org_id
    GROUP BY e.est_org_id
)
SELECT e.est_org_nombre, SUM(i.inscritos) AS inscritos
FROM cteEstructura e
INNER JOIN cteInscritos i ON i.est_org_id=e.est_org_id
GROUP BY e.est_org_nombre
ORDER BY inscritos DESC
 

Por ahora es la solución que he encontrado y funciona bien, aún creo que existen un par de detalles que pulir y estaré trabajando en ellos.

Agradezco la ayuda al respecto.

Saludos