[SOLUCIONADO] Problemas consultas, conteos, porcentajes

[andresalvarez]

Hola Comunidad, gracias de antemano por su interés en la pregunta.

Tengo que realizar un proceso y no tengo idea de como hacerlo. Tengo una base de datos que tiene la identificación del consultor, la identificación del comprador, radicado de consulta y fecha de la consulta

La idea de la empresa es dar solución a preguntas de los compradores de seguros.

Si el comprador llama su primer vez, el consultor A, le brinda una solución, hasta ahí bien, luego el mismo comprador llama por segunda vez en menos de 1 mes, ya el Consultor A tiene esa consulta mala (Debió solucionar las inquietudes en la primera consultoria). entonces suponiendo que el consultor A realiza 100 Consultorias en un mes, y de esas 100, 30 vuelven a consultar, tengo una efectividad para el consultor del 70%. La empresa tiene 600 Consultores como genero estos dos reportes.

1- El listado de efectividad de todos los consultores.
2- El listado de cada consultoria que se repitió en la identificación del comprador.

De antemano mil gracias.

[GeriReshef]

Suponemos que estos son los datos:

Código SQL:

CREATE TABLE #T(Consultor CHAR(1),
                Comprador CHAR(3),
                Fecha DateTime);
CREATE Clustered INDEX Idx_#T ON #T(Consultor,Comprador,Fecha);
GO
 
INSERT
INTO    #T
VALUES  ('A','A1','20140101'), --buena
        ('A','A1','20140215'),
        ('A','A2','20140101'), --mala
        ('A','A2','20140115'),
        ('A','A3','20140101'), --buena
        ('B','B1','20140101'), --mala
        ('B','B1','20140110'),
        ('B','B2','20140101'), --mala
        ('B','B2','20140101'),
        ('C','C1','20140101'), --buena
        ('C','C1','20140215'),
        ('C','C1','20140401');
 

El resumen e este caso:

Código SQL:

WITH T1 AS
(SELECT ROW_NUMBER() OVER (Partition BY Consultor,Comprador ORDER BY Fecha) N,
        *
FROM    #T),
T2 AS
(SELECT Consultor,
        Comprador,
        CASE WHEN COUNT(*)= 2 THEN DateDiff(DAY,MIN(Fecha),MAX(Fecha)) ELSE NULL END Plazo
FROM    T1
WHERE   N IN (1,2)
GROUP BY Consultor,
        Comprador)
SELECT  Consultor,
        COUNT(*) Total,
        SUM(CASE WHEN Plazo<31 THEN 0 ELSE 1 END) Buenas,
        SUM(CASE WHEN Plazo<31 THEN 1 ELSE 0 END) Malas
FROM    T2
GROUP BY Consultor;
 

[Libras]

Wow, sin mas datos respondes eso :O todo un crack Mr GeriReshef

[andresalvarez]

Lo voy a probar y te cuento como va. De antemano muchas gracias por tu atención

[GeriReshef]

Iniciado por Libras

Wow, sin mas datos respondes eso :O todo un crack Mr GeriReshef

Ya que tenemos "No SQL",
es muy recomendable el "No datos"..

[andresalvarez]

Hola GeriReshef, gracias por tu respuesta.

Lo he ensayado y no funciona pero se que la respuesta está cerca te explico:


la tabla puede contener los siguientes datos de ejemplo:

Código SQL:

CREATE TABLE #T(Consultor CHAR(1),
                Comprador CHAR(3),
                Fecha DateTime);
CREATE Clustered INDEX Idx_#T ON #T(Consultor,Comprador,Fecha);
GO
 
INSERT
INTO    #T
VALUES  ('A','A1','20140101'), --buena
        ('A','A1','20140215'),
        ('A','A2','20140101'), --mala
        ('A','A2','20140115'),
        ('A','A3','20140101'), --buena
        ('B','B1','20140101'), --mala
        ('B','B1','20140110'),
        ('B','B2','20140101'), --mala
        ('B','A2','20140103'), -- Este muestra que el usuario A2 preguntó a un asesor distinto antes le habia consultado a A y esta vez le consulta a B, por ende está mala
        ('C','C1','20140101'), --buena
        ('C','A2','20140125'), --Este muestra nuevamente al usuario consultando por Tercera Vez a un consultor distinto. Es buena para el ultimo consultor, pero es mala para los otros dos consultores dentro del mes (30 días) ya que el cliente vuelve a llamar
        ('C','C1','20140401');
 

Un ejemplo claro como se realiza al dia de hoy en excel es:

Se ordena la BD por Cliente luego por Fecha. Se valida si el cliente es igual al del registro superior, si es correcto, se valida la fecha del registro actual con el registro anterior y si el resultado es menor a 30 dias, entonces es mala de lo contrario es buena. En excel se hace esta validación en una columna nueva. luego en una tabla dinamica se muestra el resultado del consultor, cuantas buenas y cuantas malas tiene. No se si me hago entender, de todos modos agradezco su ayuda.

[GeriReshef]

Andrés- no estoy seguro que entiendo tu ejemplo:
A1- buena (volvió después de más de un mes)
A2- mala (volvió después de menos de un mes)
A3- buena (no volvió)
B1- mala (volvió después de menos de un mes)
B2- buena (no volvió)
C1- buena (volvió después de más de un mes)

¿Que pasa si vuelve en la segunda vez a otro consultor?
¿Que pasa si vuelve en la tercera/cuarta/.. vez a otro consultor (a pesar que en la segunda vilvió al original después de más de un mes)?

[andresalvarez]

Hola GeriReshef, Gracias por tu interés.

La empresa es de soluciones integrales, la idea es dejar al cliente satisfecho y que el NO vuelva a consultar, la idea es que en la primer consultaría salga totalmente satisfecho.

Entonces la primer consulta sin importar el Empleado (Consultor) esta bien. Pero si vuelve a consultar, sin importar el empleado en menos de 30 días, es penalizable el primer consultor, porque no brindo una buena consultoría.

Entonces si consulta por tercera vez en menos de un mes, se deben penalizar 2 Consultores. El primer por la segunda consulta y el segundo por la tercer consulta.

La idea es garantizar que el usuario no consulte en el próximo mes, así se mejora el tiempo de labor. Entonces se debe brindar respuestas concretas, claras y asertivas de los seguros.

Luego en cualquier momento yo realizo la consulta y me aparecerá lo siguiente:

Consultor1 Atiende 100 Consultas de las cuales 30 usuarios vuelven a consultar en menos de un mes.

Titulos: ('Consultor','Consultas','Malas','Buenas')
Registros: ('Consultor1',100,30,70)

Creo que está mejor explicado esta vez. Si tienes cualquier otra duda, te agradecería infinitamente.

[andresalvarez]

Si tienes conocimiento de excel, me indicas te indico las formulas que se realizan al día de hoy.

Gracias.

[GeriReshef]

Espero que te entendí: tenemos que coincidir cada consulta de un comprador con la siguiente:
si no existe la siguiente- es "buena"
si existe una pero paso más de un mes- es "buena"
si existe una pero paso menos de un mes- es "mala".

Para coincidir cada consulta con la siguiente- utilizo este código:

Código SQL:

WITH Ta AS
(SELECT ROW_NUMBER() OVER(Partition BY Comprador ORDER BY Fecha) N,
        *
FROM    #T)
SELECT  *
FROM    Ta T1
LEFT JOIN Ta T2
        ON T1.Comprador=T2.Comprador
        AND T1.N=T2.N-1;
 

Para obtener las calificaciones de "buena" y "mala"-

Código SQL:

WITH Ta AS
(SELECT ROW_NUMBER() OVER(Partition BY Comprador ORDER BY Fecha) N,
        *
FROM    #T)
SELECT  T1.Consultor,
        T1.Comprador,
        T1.Fecha Fecha1,
        T2.Fecha Fecha2,
        CASE WHEN T2.Fecha IS NULL THEN 'Buena'
            WHEN DateDiff(DAY,T1.Fecha,T2.Fecha)>=31 THEN 'Buena'
            ELSE 'Mala'
            END Calificacion
FROM    Ta T1
LEFT JOIN Ta T2
        ON T1.Comprador=T2.Comprador
        AND T1.N=T2.N-1;
 

Para contar cuantas "buenas" y "malas" tiene cada consultor:

Código SQL:

WITH Ta AS
(SELECT ROW_NUMBER() OVER(Partition BY Comprador ORDER BY Fecha) N,
        *
FROM    #T),
Tb AS
(SELECT T1.Consultor,
        T1.Comprador,
        T1.Fecha Fecha1,
        T2.Fecha Fecha2,
        CASE WHEN T2.Fecha IS NULL THEN 'Buena'
            WHEN DateDiff(DAY,T1.Fecha,T2.Fecha)>=31 THEN 'Buena'
            ELSE 'Mala'
            END Calificacion
FROM    Ta T1
LEFT JOIN Ta T2
        ON T1.Comprador=T2.Comprador
        AND T1.N=T2.N-1)
SELECT  Consultor,
        SUM(CASE WHEN Calificacion='Buena' THEN 1 ELSE 0 END) Buenas,
        SUM(CASE WHEN Calificacion='Mala' THEN 1 ELSE 0 END) Malas,
        COUNT(*) Total
FROM    Tb
GROUP BY Consultor;
 

[andresalvarez]

Hola GeriReshef,

En el momento de ejecutarlo encontraba algunas diferencias, solamente realice un cambio

Código SQL:

LEFT JOIN Ta T2
        ON T1.Comprador=T2.Comprador
        AND T1.N=T2.N-1; --Se cambia por T2.N=T1.N-1 y funciona perfectamente
 

Te agradezco infinitamente, me gustaría que me explicaras con tus palabras como usar el LEFT JOIN, de resto el código se entiende perfectamente.

Nuevamente infinitas gracias, se marca el post como SOLUCIONADO.

Iniciado por GeriReshef

Espero que te entendí: tenemos que coincidir cada consulta de un comprador con la siguiente:
si no existe la siguiente- es "buena"
si existe una pero paso más de un mes- es "buena"
si existe una pero paso menos de un mes- es "mala".

Para coincidir cada consulta con la siguiente- utilizo este código:

Código SQL:

WITH Ta AS
(SELECT ROW_NUMBER() OVER(Partition BY Comprador ORDER BY Fecha) N,
        *
FROM    #T)
SELECT  *
FROM    Ta T1
LEFT JOIN Ta T2
        ON T1.Comprador=T2.Comprador
        AND T1.N=T2.N-1;
 

Para obtener las calificaciones de "buena" y "mala"-

Código SQL:

WITH Ta AS
(SELECT ROW_NUMBER() OVER(Partition BY Comprador ORDER BY Fecha) N,
        *
FROM    #T)
SELECT  T1.Consultor,
        T1.Comprador,
        T1.Fecha Fecha1,
        T2.Fecha Fecha2,
        CASE WHEN T2.Fecha IS NULL THEN 'Buena'
            WHEN DateDiff(DAY,T1.Fecha,T2.Fecha)>=31 THEN 'Buena'
            ELSE 'Mala'
            END Calificacion
FROM    Ta T1
LEFT JOIN Ta T2
        ON T1.Comprador=T2.Comprador
        AND T1.N=T2.N-1;
 

Para contar cuantas "buenas" y "malas" tiene cada consultor:

Código SQL:

WITH Ta AS
(SELECT ROW_NUMBER() OVER(Partition BY Comprador ORDER BY Fecha) N,
        *
FROM    #T),
Tb AS
(SELECT T1.Consultor,
        T1.Comprador,
        T1.Fecha Fecha1,
        T2.Fecha Fecha2,
        CASE WHEN T2.Fecha IS NULL THEN 'Buena'
            WHEN DateDiff(DAY,T1.Fecha,T2.Fecha)>=31 THEN 'Buena'
            ELSE 'Mala'
            END Calificacion
FROM    Ta T1
LEFT JOIN Ta T2
        ON T1.Comprador=T2.Comprador
        AND T1.N=T2.N-1)
SELECT  Consultor,
        SUM(CASE WHEN Calificacion='Buena' THEN 1 ELSE 0 END) Buenas,
        SUM(CASE WHEN Calificacion='Mala' THEN 1 ELSE 0 END) Malas,
        COUNT(*) Total
FROM    Tb
GROUP BY Consultor;
 

[GeriReshef]

Me parece que tu solución no tiene mucho sentido, sin embargo- te explicaré la mia:



(lamentablemente el sistema no muestra esta foto:
https://drive.google.com/file/d/0B3o...it?usp=sharing)

De las 12 líneas de la table
6 no tienen coincidencia (buenas)
2 tienen coincidecia de más de un mes (buenas)
4 tienen coincidencia de menos de un mes (malas)

El Left Join nos posibilita mostrar también las 6 filas de la izquierda que no tienen coincidencia en la derecha.