[SOLUCIONADO] rango por ventas y por zona

[gerardo_goh]

hola que tal estimad@s, tengo el siguiente problema y espero que me puedan ayudar de igualmente orientar, les explico tengo una tabla con la siguiente estructura y datos

Código MySQL:

create table tblrango (id_trab           varchar(5)  not null primary key,
                       nombre            varchar(50) not null,
                       zona              varchar(4)  not null,
                       tipo_incidencia   varchar(50) not null,
                       ventas            integer     not null,
                       fec_inicial       date        null,
                       fec_final         date        null,
                       dia_hoy           date        null,
                       origen            varchar(50) not null);
 
insert into tblrango values ('00156','ricardo','0015','trabaja',30,null,null,'2013-02-15','tblrango'),
                            ('04566','gerardo','0015','incapacidad',20,'2013-02-15','2013-02-20','2013-02-15','tblrango'),
                            ('00013','armando','0015','trabaja',25,null,null,'2013-02-15','tblrango'),
                            ('02546','jose','0120','vacaciones',48,'2013-02-10','2013-02-10','2013-02-15','tblrango'),
                            ('01756','francisco','0120','trabaja',30,null,null,'2013-02-15','tblrango'),
                            ('09000','pablo','3000','trabaja',30,null,null,'2013-02-15','tblrango'),
                            ('12300','jonathan','3000','incapacidad',30,'2013-02-05','2013-03-05','2013-02-15','tblrango'),
                            ('12222','ulises','3000','vacaciones',31,'2013-02-12','2013-02-15','2013-02-15','tblrango'),
                            ('05630','julian','3000','trabaja',40,null,null,'2013-02-15','tblrango'),
                            ('15460','pedro','3000','trabaja',41,null,null,'2013-02-15','tblrango');
 
 
+---------+-----------+------+-----------------+--------+-------------+---------
---+------------+----------+
| id_trab | nombre    | zona | tipo_incidencia | ventas | fec_inicial | fec_fina
l  | dia_hoy    | origen   |
+---------+-----------+------+-----------------+--------+-------------+---------
---+------------+----------+
| 00013   | armando   | 0015 | trabaja         |     25 | NULL        | NULL
   | 2013-02-15 | tblrango |
| 00156   | ricardo   | 0015 | trabaja         |     30 | NULL        | NULL
   | 2013-02-15 | tblrango |
| 01756   | francisco | 0120 | trabaja         |     30 | NULL        | NULL
   | 2013-02-15 | tblrango |
| 02546   | jose      | 0120 | vacaciones      |     48 | 2013-02-10  | 2013-02-
10 | 2013-02-15 | tblrango |
| 04566   | gerardo   | 0015 | incapacidad     |     20 | 2013-02-15  | 2013-02-
20 | 2013-02-15 | tblrango |
| 05630   | julian    | 3000 | trabaja         |     40 | NULL        | NULL
   | 2013-02-15 | tblrango |
| 09000   | pablo     | 3000 | trabaja         |     30 | NULL        | NULL
   | 2013-02-15 | tblrango |
| 12222   | ulises    | 3000 | vacaciones      |     31 | 2013-02-12  | 2013-02-
15 | 2013-02-15 | tblrango |
| 12300   | jonathan  | 3000 | incapacidad     |     30 | 2013-02-05  | 2013-03-
05 | 2013-02-15 | tblrango |
| 15460   | pedro     | 3000 | trabaja         |     41 | NULL        | NULL
   | 2013-02-15 | tblrango |
+---------+-----------+------+-----------------+--------+-------------+---------
---+------------+----------+
10 rows in set (0.00 sec)
 

necesito obtener el siguiente resultado:

http://www.freeimagehosting.net/9h3yv

Anexo el link por que no se como subir imagenes

la idea es que me diga el rango por grupo por la cantidad de ventas generadas por el trabajador ademas de las columnas cant_dias_no_lab es el resultado de total de dias de la incidencia, y la columna tot_dias_hoy es la cantidad de dias que lleva de incidencia hasta la fecha de la columna dia_hoy, espero que me puedan ayudar, muchas gracias

[leonardo_josue]

Hola gerardo_goh:

Te recuerdo que aquí te podemos ayudar a resolver tus dudas, pero que de ninguna manera te podemos hacer el trabajo... En realidad en tu post no expresas qué problemas tienes o qué dudas tienes, por lo tanto es algo complicado dar una respuesta puntual. ¿Qué intentaste hacer? porque supongo que intentaste hacer algo verdad?, coloca las consultas que intentaste hacer, no importa que sean erróneas, a partir de eso podemos ayudarte a afinarla o indicarte alguna otra alternativa. Por lo pronto hagamos algunas acotaciones:

1. Para el rango: en realidad no nos explicas cómo es que asignas cada valor (1-4). dices que dependen de la cantidad de ventas generadas, pero cómo son estos rangos??? porque por ejemplo

Código:

Ricardo tiene ventas = 30 y un rango = 1
Pablo   tiene ventas = 30 y un rango = 4 ??????????

Para hacer esta clasificación puedes hacerlo mediante un CASE-WHEN o mediante un IF.

http://dev.mysql.com/doc/refman/5.0/...functions.html

2. Para la cantidad de días. Puedes hacerlo con la función DATEDIFF:

DATEDIFF(expr,expr2)

DATEDIFF() retorna el número de días entre la fecha inicial expr y la fecha final expr2. expr y expr2 son expresiones de fecha o de fecha y hora. Sólo las partes de fecha de los valores se usan en los cálculos.

http://dev.mysql.com/doc/refman/5.0/...functions.html

Haz la prueba, intenta sacar la consulta por tu propia cuenta y si continuas con dudas publica lo que hiciste y con gusto te ayudamos.

Saludos
Leo.

[gerardo_goh]

Hola que tal, creo que expuse el tema mal, lo que necesito es obtener el ranking de los mejores vendedores, pongo un ejemplo de excel:

http://jldexcelsp.blogspot.mx/2011/0...or-grupos.html

Esto es lo que llevo creado.

Código MySQL:

-- ============ PASO 1=============
-- Crear una tabla temporal
create table tblvta_alta as
 
select concat(a.id_trab,'-',a.nombre,'-',a.zona,'-',a.ventas)as key_2,a.id_trab,a.nombre,a.zona,a.tipo_incidencia,a.ventas,
      ifnull(a.fec_inicial,'') fec_inicio,
      ifnull(a.fec_final,'')fec_fin,
      b.lugar,
      if(a.fec_inicial,datediff(a.fec_final,a.fec_inicial)+1,' ')cant_dia_no_lab,
      if(a.fec_final>a.dia_hoy,datediff(a.dia_hoy,a.fec_inicial)+1,' ')tot_dias_hoy,
      a.dia_hoy
from tblrango a
 
inner join
 
(select b.zona,max(b.ventas) maxventas,(1) lugar
from tblrango b
group by b.zona
order by b.zona asc) b
 
on  a.zona = b.zona
and a.ventas=b.maxventas;
 
select * from tblvta_alta;
 
 
-- ============ PASO 2 ==================
-- Para determinar los días no laborados hasta el 2013-02-15 y el total de dias de incidencia diferente a trabaja
create table tblfechas as
select concat(a.id_trab,'-',a.nombre,'-',a.zona,'-',a.ventas)as key_1,a.zona,a.id_trab,a.nombre,a.tipo_incidencia,a.ventas,
      ifnull(fec_inicial,'')as fecha_ini,
      ifnull(fec_final,'')as fecha_fin,
      if(a.fec_inicial,datediff(a.fec_final,a.fec_inicial)+1,' ')cant_dia_no_lab,
      if(a.fec_final>a.dia_hoy,datediff(a.dia_hoy,a.fec_inicial)+1,' ')tot_dias_hoy
 
 
from tblrango a;
 
select * from tblfechas;
 
 
-- ============ Paso 3 ==================
-- Unir tablas del paso 1 con la del paso 2
 
select a.id_trab,a.nombre,a.zona,a.tipo_incidencia,a.ventas,
ifnull(b.lugar,'')lugar,a.fecha_ini,a.fecha_fin,a.cant_dia_no_lab,a.tot_dias_hoy,
if(fecha_ini='','trabaja','descanso')ruta from tblfechas a
left outer join tblvta_alta b
on a.key_1=b.key_2
order by a.zona asc,a.ventas desc;
 
-- el dia_hoy es: 2013-02-15 sobre esa fecha fueron los calculos de las columnas cant_dia_no_lab  y tot_dias_hoy
 
+---------+-----------+------+-----------------+--------+-------+------------+------------+-----------------+--------------+----------+
| id_trab | nombre    | zona | tipo_incidencia | ventas | lugar | fecha_ini  | fecha_fin  | cant_dia_no_lab | tot_dias_hoy | ruta     |
+---------+-----------+------+-----------------+--------+-------+------------+------------+-----------------+--------------+----------+
| 00156   | ricardo   | 0015 | trabaja         |     30 | 1     |            |            |                 |              | trabaja  |
| 00013   | armando   | 0015 | trabaja         |     25 |       |            |            |                 |              | trabaja  |
| 04566   | gerardo   | 0015 | incapacidad     |     20 |       | 2013-02-15 | 2013-02-20 | 6               | 1            | descanso |
| 02546   | jose      | 0120 | vacaciones      |     48 | 1     | 2013-02-10 | 2013-02-10 | 1               |              | descanso |
| 01756   | francisco | 0120 | trabaja         |     30 |       |            |            |                 |              | trabaja  |
| 15460   | pedro     | 3000 | trabaja         |     41 | 1     |            |            |                 |              | trabaja  |
| 05630   | julian    | 3000 | trabaja         |     40 |       |            |            |                 |              | trabaja  |
| 12222   | ulises    | 3000 | vacaciones      |     31 |       | 2013-02-12 | 2013-02-15 | 4               |              | descanso |
| 09000   | pablo     | 3000 | trabaja         |     30 |       |            |            |                 |              | trabaja  |
| 12300   | jonathan  | 3000 | incapacidad     |     30 |       | 2013-02-05 | 2013-03-05 | 29              | 11           | descanso |
+---------+-----------+------+-----------------+--------+-------+------------+------------+-----------------+--------------+----------+
10 rows in set (0.00 sec)
 

Hasta el momento esto es lo que tengo me hace falta obtener los demas ranking de los vendedores por zona de acuerdo a las ventas generadas por cada zona.

Espero que me puedan ayudar.

[leonardo_josue]

Hola de nuevo gerardo_goh:

Lamentablemente MySQL no cuenta con funciones RANK o ROW_COUNT que poseen otros motores como ORACLE o SQL Server, y que son las indicadas para hacer lo que necesitas, sin embargo hay varias técnicas que puedes utilizar para simular estas funciones. Checa esta liga:

RANK:
http://www.artfulsoftware.com/infotr...tip.php?id=460

ROW_NUMBER:
http://www.artfulsoftware.com/infotr...ip.php?id=1098


Como podrás observar la consulta no es tan trivial, pero en realidad no es tan complicada llevarla a la práctica. Me voy a enfocar sólo en lo que te falta que es el RANK de ventas... sería más o menos así, basándonos en los siguientes datos:

Código MySQL:

mysql> SELECT id_trab, nombre, zona, ventas FROM tblrango;
+---------+-----------+------+--------+
| id_trab | nombre    | zona | ventas |
+---------+-----------+------+--------+
| 00013   | armando   | 0015 |     25 |
| 00156   | ricardo   | 0015 |     30 |
| 04566   | gerardo   | 0015 |     20 |
| 01756   | francisco | 0120 |     30 |
| 02546   | jose      | 0120 |     48 |
| 05630   | julian    | 3000 |     40 |
| 09000   | pablo     | 3000 |     30 |
| 12222   | ulises    | 3000 |     31 |
| 12300   | jonathan  | 3000 |     30 |
| 15460   | pedro     | 3000 |     41 |
+---------+-----------+------+--------+
10 rows in set (0.00 sec)
 

La idea de la función RANK es hacer una subconsulta para cada registro donde cuentes cuantos registros existen con ventas mayores o iguales a la que se está analizando. Sería más o menos así:

Código MySQL:

mysql> SELECT v1.id_trab, v1.nombre, v1.zona, 
    -> v1.ventas, COUNT(v2.ventas) AS Rank
    -> FROM tblrango v1
    -> INNER JOIN tblrango v2 ON
    ->   v1.zona = v2.zona AND
    ->   v1.ventas < v2.ventas OR
    ->   (v1.ventas = v2.ventas AND v1.zona = v2.zona AND
    ->    v1.id_trab = v2.id_trab)
    -> GROUP BY v1.id_trab, v1.zona, v1.ventas
    -> ORDER BY v1.zona, v1.ventas DESC, v1.id_trab;
+---------+-----------+------+--------+------+
| id_trab | nombre    | zona | ventas | Rank |
+---------+-----------+------+--------+------+
| 00156   | ricardo   | 0015 |     30 |    1 |
| 00013   | armando   | 0015 |     25 |    2 |
| 04566   | gerardo   | 0015 |     20 |    3 |
| 02546   | jose      | 0120 |     48 |    1 |
| 01756   | francisco | 0120 |     30 |    2 |
| 15460   | pedro     | 3000 |     41 |    1 |
| 05630   | julian    | 3000 |     40 |    2 |
| 12222   | ulises    | 3000 |     31 |    3 |
| 09000   | pablo     | 3000 |     30 |    4 |
| 12300   | jonathan  | 3000 |     30 |    4 |
+---------+-----------+------+--------+------+
10 rows in set (0.00 sec)
 

También podrías hacerlo con la función ROW_NUMBER, la idea aquí es en primer lugar ordenar los registros por zona y por ventas, utilizar una variable temporal para almacenar la zona y un contador para la posición. Mientras se analice la misma zona, se va incrementando el contador para poner la posición, cuando se detecta un cambio de zona entonces se inicializa el contador a 1... sería más o menos así:

Primero inicializamos las variables temprales que vamos a utilizar:

Código MySQL:

mysql> SET @zonaPrev=0, @ventasPrev=0, @ordPrev=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT id_trab, nombre, zona, ventas, row_number
    -> FROM (
    ->   SELECT
    ->     ventas, id_trab, nombre,
    ->     @ordPrev := IF(@zonaPrev = zona,
    ->                    IF(ventas != @ventasPrev, @ordPrev+1, @ordPrev),
    ->                    1) AS row_number,
    ->     @zonaPrev := zona AS zona,
    ->     @ventasPrev := ventas AS ventasPrev
    ->   FROM tblrango
    ->   ORDER BY zona, ventas DESC, id_trab, nombre
    -> ) tmp;
+---------+-----------+------+--------+------------+
| id_trab | nombre    | zona | ventas | row_number |
+---------+-----------+------+--------+------------+
| 00156   | ricardo   | 0015 |     30 |          1 |
| 00013   | armando   | 0015 |     25 |          2 |
| 04566   | gerardo   | 0015 |     20 |          3 |
| 02546   | jose      | 0120 |     48 |          1 |
| 01756   | francisco | 0120 |     30 |          2 |
| 15460   | pedro     | 3000 |     41 |          1 |
| 05630   | julian    | 3000 |     40 |          2 |
| 12222   | ulises    | 3000 |     31 |          3 |
| 09000   | pablo     | 3000 |     30 |          4 |
| 12300   | jonathan  | 3000 |     30 |          4 |
+---------+-----------+------+--------+------------+
10 rows in set (0.00 sec)
 

Si comparas este ejercicio con el que te pongo en la liga, observarás que yo utilizo una segunda variable ventasTemp, esto es para evitar que cuando se trate de ventas iguales te ponga un orden distinto, como en los últimos dos registros, si lo hago como aparece en el ejemplo sería así:

Código MySQL:

mysql> SET @zonaPrev=0, @ordPrev=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT id_trab, nombre, zona, ventas, row_number
    -> FROM (
    ->   SELECT
    ->     ventas, id_trab, nombre,
    ->     @ordPrev := IF(@zonaPrev = zona, @ordPrev+1, 1) AS row_number,
    ->     @zonaPrev := zona AS zona
    ->   FROM tblrango
    ->   ORDER BY zona, ventas DESC, id_trab, nombre
    -> ) tmp;
+---------+-----------+------+--------+------------+
| id_trab | nombre    | zona | ventas | row_number |
+---------+-----------+------+--------+------------+
| 00156   | ricardo   | 0015 |     30 |          1 |
| 00013   | armando   | 0015 |     25 |          2 |
| 04566   | gerardo   | 0015 |     20 |          3 |
| 02546   | jose      | 0120 |     48 |          1 |
| 01756   | francisco | 0120 |     30 |          2 |
| 15460   | pedro     | 3000 |     41 |          1 |
| 05630   | julian    | 3000 |     40 |          2 |
| 12222   | ulises    | 3000 |     31 |          3 |
| 09000   | pablo     | 3000 |     30 |          4 |
| 12300   | jonathan  | 3000 |     30 |          5 |
+---------+-----------+------+--------+------------+
10 rows in set (0.00 sec)
 

Observa que en los últimos dos registros les asigna rangos diferentes (4, 5).

Trata de analizar las consultas pero sobre todo trata de entenderlas, practica algunas otras consultas, estoy seguro que este tipo de consultas te servirán en un futuro.

Saludos.

[gerardo_goh]

Muchas gracias, es lo que necesitaba, pongo el tema como solucionado, una pregunta como pongo en mis preguntas imagenes como otros temas: ejemplo:

http://www.forosdelweb.com/f86/mostr...s-alta-949576/

o

http://www.forosdelweb.com/f86/agrup...fechas-947318/

de nuevo muchas gracias por todo, me haz ayudado mucho.

[sjj]

Las imágenes que quieras postear deben estar en alguna URL. Haces click en la opción "Insertar Imagen"--> y ahí introduces la URL de la imagen que deseas incluir.