Consulta recursiva

[rvaldenegro]

Hola Estimados,

Hace tiempo no entraba por acá, pero ahora tengo una duda digna de ustedes. He estado trabajando en SQL 2008 con tablas recursivas para generar la estructura organizacional de la empresa. Algo así

tbl_estructura_organizativa
est_org_id
est_org_id_padre
niv_org_nivel
est_org_nombre

Donde est_org_id_padre referencia a est_org_id y niv_org_nivel indica el nivel de profundidad desde 1 hasta n. En este caso la estructura que tengo es Franquiciado, Razon Social, Local, en donde un franquiciado tiene n razones sociales y una razon social tiene n locales. Entonces:
- Franquiciado => niv_org_nivel = 1 y est_org_id_padre = NULL
- Razon social => niv_org_nivel = 2 y est_org_id_padre = id algun franquiciado
- Local => niv_org_nivel = 3 y est_org_id_padre = id alguna razon social

Ahora bien, tengo también una tabla de personas que se relaciona a est_org_id

tbl_persona
per_id
est_org_id
per_nombre

Cada persona se relaciona a un local, no a una razon social o franquiciado directamente sino a un local en especifico. Eso sí, hay que considerar que los otros casos igual podrían darse aunque por ahora no ocurre.

El tema es que necesito saber cuánta gente está asociado a cada franquiciado. En lo práctico esto implica sumar todas las personas que estén asociados a cada razon social y/o local que corresponda al franquiciado.

La verdad es que anteriormente no había trabajado con tablas recursivas, pero me lancé no más pensando "en el camino se arregla la carga" y si bien he logrado un par de soluciones la verdad no están muy lindas y quiero buscar una más óptima. Y humildemente pido su ayuda al respecto

Por ahora he estado jugando un poco con las CTE, pero me trae la estructura completa con todas las razones sociales y los locales de cada franquiciado, eso está OK, sirve en algunos casos, pero para éste en específico necesito el dato consolidado a nivel de franquiciado y no el detalle a nivel de local. ¿Se les ocurre alguna idea?. De antemano mil gracias . Les dejo el código SQL con el que he estado jugando

Código:

WITH Estructura (niv_org_nombre, est_org_id, est_org_nombre, niv_org_nivel) AS (
	SELECT n.niv_org_nombre, eo.est_org_id, eo.est_org_nombre, eo.niv_org_nivel
	FROM tbl_estructura_organizativa eo 
	INNER JOIN tbl_nivel_organizativo n ON n.niv_org_nivel=eo.niv_org_nivel
	WHERE eo.est_org_id_padre IS NULL
	AND eo.niv_org_nivel=1
	UNION ALL
	
	SELECT n.niv_org_nombre, eo.est_org_id, eo.est_org_nombre, eo.niv_org_nivel
	FROM tbl_estructura_organizativa eo 
	INNER JOIN tbl_nivel_organizativo n ON n.niv_org_nivel=eo.niv_org_nivel
	INNER JOIN Estructura AS est ON est.est_org_id=eo.est_org_id_padre
)

SELECT e.est_org_id, e.est_org_nombre, e.niv_org_nivel, tmpInscritos.inscritos
FROM Estructura e 
LEFT JOIN (
	SELECT COUNT(DISTINCT p.per_id) as inscritos, est_org_id
	FROM tbl_persona p
	GROUP BY est_org_id
) tmpInscritos ON tmpInscritos.est_org_id=e.est_org_id
WHERE e.niv_org_nivel=1
GROUP BY e.est_org_id, e.est_org_nombre, e.niv_org_nivel, inscritos
ORDER BY e.niv_org_nivel ASC, e.est_org_nombre ASC;

Gracias a todos por el interés

Un abrazo,

Rodrigo

[Libras]

recursividad con CTE, mmm prefiero la recursividad a nivel funcion :P podrias poner un ejemplo de tus datos?

[rvaldenegro]

Código SQL:

est_org_id  est_org_id_padre    niv_org_nivel   est_org_nombre
1            NULL               1               Franquiciado 1
2            1                  2               Razon Social 1
3            1                  2               Razon Social 2
4            NULL               1               Franquiciado 2
5            2                  2               LOCAL 1
6            2                  2               LOCAL 2
7            2                  3               LOCAL 3
 

Acá los locales 1 y 2 correspondiente a la razon social 1 que a su vez corresponde al franquiciado 1.

El local 3 corresponde a la razon social 2 del franquiciado 1 también

El franquiciado 2 no tiene razones sociales o locales

Las personas en este caso irían asociados a los ID 5, 6 y 7

Con respecto a si hacerlo por CTE o por funciones, la verdad no estoy cerrado a ninguna opción. Por ahora estoy trabajando con funciones y evaluando el tema de las CTE, pero me gustaría tener más opiniones

Muchas gracias de nuevo

[rvaldenegro]

Bueno, agrego un dato. Ahora por ejemplo tengo una función a la que paso como parámetro el id de la estructura y me devuelve todo lo que tenga esa estructura, el problema es cómo pasar el parámetro a la función en una misma consulta.

Lo que se puede hacer es crear un cursor que obtenga las estructuras y luego pasar id como parámetro

Código SQL:

SELECT *
FROM tbl_persona p
INNER JOIN udf_recorreEstructura(@id_estructura) fn ON fn.est_org_id=p.est_org_id
 

Y resulta, pero es una solución bien fea. Yo preferiría hacerlo todo en una misma consulta, pero es ahí donde estoy bastante enredado

Gracias de nuevo por la ayuda

[Libras]

Iniciado por rvaldenegro

Código SQL:

est_org_id  est_org_id_padre    niv_org_nivel   est_org_nombre
1            NULL               1               Franquiciado 1
2            1                  2               Razon Social 1
3            1                  2               Razon Social 2
4            NULL               1               Franquiciado 2
5            2                  2               LOCAL 1
6            2                  2               LOCAL 2
7            2                  3               LOCAL 3
 

Acá los locales 1 y 2 correspondiente a la razon social 1 que a su vez corresponde al franquiciado 1.

El local 3 corresponde a la razon social 2 del franquiciado 1 también

El franquiciado 2 no tiene razones sociales o locales

Las personas en este caso irían asociados a los ID 5, 6 y 7

Con respecto a si hacerlo por CTE o por funciones, la verdad no estoy cerrado a ninguna opción. Por ahora estoy trabajando con funciones y evaluando el tema de las CTE, pero me gustaría tener más opiniones

Muchas gracias de nuevo

Podrias explicar un poco mejor la estrucutra de tu tabla? yo por lo general una estructura de padres la manejo de este modo

id Id_padre nivel
1 NUll 0
2 1 1
3 1 1
4 3 2

saludos!

[iislas]

Libras: conectate a skype

[rvaldenegro]

Iniciado por Libras

Podrias explicar un poco mejor la estrucutra de tu tabla? yo por lo general una estructura de padres la manejo de este modo

id Id_padre nivel
1 NUll 0
2 1 1
3 1 1
4 3 2

saludos!

Es exactamente lo mismo pero con otros nombres.

Gracias,

R

[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

[Libras]

to much code, but if works is fine :P

[rvaldenegro]

bueno, si hubiera una versión resumida mejor, de momento no se me ocurre

[Libras]

esque podrias hacerlo en una sola funcion metiendo un ciclo dentro de la misma lo que tendrias que hacer es obtener los hijos del registro que mandas como parametro y analizar cada uno de estos elementos si alguno de ellos tiene hijos entonces llamas a la funcion recursiva :P es lo que estas haciendo pero con una sola funcion