SQL inventario

[mjjj]

Por mas que trato es no funciona para nada, me entrega datos que no tienen nada que ver con lo que necesito.

Explico un poco mas la situacion.

Es una aplicacion de compras y presupuesto, la cual consta de 2 tablas, compras y presupuesto.

La tabla presupuesto contiene los campos empresa, area, subarea, ano, mes y monto. El presupuesto esta ingresado como sigue:

empresaareasubareaanomesmontoemp1apoyomantencion20091500emp1apoyomantencion20092200emp1apoyosueldo20091100emp1apoyootro2009250emp1admgasto2009180emp2apoyomantencion2009170emp2apoyomas2009155

asi para todos las area, subarea hasta el mes de diciembre 2009

Entoces, lo que necesito es que me arroje un listado de todos las distintas area de la empresa "emp1", en donde ademas muestre la suma de todos los montos para cierto mes y año en particular (primeras 2 columnas intervienen la tablas presupuesto)

la Tabla compras es asi:
empresaareasubareaanomesmonto_comprestadoemp1apoyomantencion2009120Femp1apoyomantencion2009110Femp1apoyosueldo200912Pemp1apoyootro200916Femp1admgasto2009112Pemp2apoyomantencion2009121Pemp2apoyomas200911F

Puede ocurrir que no se genero ninguna compra para algun empresa, area, año y mes en particular.

Las columnas 3 y 4 de mi consulta hacen referencia a la tablas compras. En la 3 columna, debe sumar todas la compras, asociadas al empresa, area, año y mes escojida y esten finalizadas (estado F), y la 4 columna la cantidad de compras asociadas a la empresa, area, año y mes que esten excedidas (estado = P)

o que deberia arrojar seria para empresa = emp1, año=2009, mes=1

areamontocompraexcedidaapoyo600361adm8001

descarte el campo oficina, simplemente es otro parametro mas, que debe coincidir para ambas tablas.

Ayudenme con esto, ya no se que hacer.

Gracias

[mjjj]

adjunto excel con ejmplo de tablas para una mejor comprension

Saludos

[Bpascal]

Hola,

Ok, me tomé un tiempito para leer detenidamente el querry original de los insumos y creo que tengo claro que es lo que hace.

Principalmente tienes 2 tablas, INSUMOS es la tabla maestra donde estan los codigos, descripciones etc de los productos, y la tabla INVENTARIOS donde estan los movimientos de los productos tanto de entrada como de salida, la cual se diferencia por el campo tipo; "I" significa ingreso y "E" significa egreso.

La meta es basicamente listar todos los codigos de INSUMOS con su saldo en unidades y valores resumidos desde la tabla INVENTARIOS, el resumen se puede condicionar por ejemplo de que solo sea de determinada empresa, bodega o incluso solo incluyendo hasta determinada fecha si se quiere, etc.


En tu querry tu estas haciendo a la tabla de movimientos (que por naturaleza es grande) lo siguente:

- 2 veces SELECT para obtener el data saldo en unidades
- 2 veces SELECT para obtener el dato saldo en valores
- 4 veces select par obtener el dato de costo unitario.

En total 8 selects por la cada codigo de la tabla insumos, la cual tu dices tiene aprox 1000 registros, esto genera que se le apliquen aproximadamente 8,000 selects a una tabla llamada inventario que seguramente tiene 20 mil o 30 mil y creciendo por que son las transaciones.

Vemos como podemos bajar un poco la cantidad de SELECTS aplicados.

-----------------------------

Primero dejame explicarte el uso de INNER JOIN para relacionar 2 tablas, realmente es bien simple:

La tabla INVENTARIOS tiene el campo "bodega" que es el numero Id de una bodega
tambien tiene el campo "empresa" que es el numero Id de una empresa

Tienes una tabla llamada BODEGA la cual tiene su campo "ID", su campo "empresa", y su campo "nombre"

Si dedeas generar un listado de la tabla inventarios incluyendo una columna que diga el nombre de la bodega tu lo haces de la siguiente manera:

SELECT
I.id, I.cantidad, I.tipo, I.empresa I.bodega, B.nombre
FROM
INVENTARIO I, BODEGA B
WHERE
I.empresa=B.empresa AND I.bodega = B.id

Ahora re-escrito el querry usando INNER JOIN seria:

SELECT
I.id, I.cantidad, I.tipo, I.empresa I.bodega, B.nombre
FROM
INVENTARIO I
INNER JOIN BODEGA B ON I.empresa=B.empresa AND I.bodega = B.id

El resultado es exactamente lo mismo, unicamente se ha quitado la seecion WHERE y la condicion se movio a la seccion INNER JOIN.

Ahora tu te preguntaras "y que?, si es lo mismo entonces cual es la ventaja de hacerlo con WHERE ó hacerlo con INNER JOIN ?"

Usar inner join te permite liberar el WHERE de las condiciones utilizadas para enlazar tablas, muchas veces es necesario utilizar 4 o 5 tablas al mismo tiempo y obtener informacion de ellas, escribir un WHERE donde estan todas esas relaciones mas las condiciones que son para selecionar los registros deseados harian un where ya muy complicado. Utiliza la clausula WHERE solo para escribir las condiciones que se requieren para seleccionar los registros deseados en la tabla principal; y utiliza la clausula INNER JOIN para especificar la relacion que hay entre las tablas involucradas, esto ayuda al SQL a saber la mejor manera de utilizar los indices de las tablas para hacer la relacion.
------------------------------

Ok, ahora a continuacion te paso a como haria yo el querry que genere el listado en discucion;

Yo primero resumiria la tabla de inventarios utilizando UN tan solo SELECT creando una tabla temporal, esto reducira drasticamente la cantidad de select con tantas condiciones. De hecho la maneras mas optimia seria 2 tablas temporales, una por las entradas y otra por las salidas pero no quiero hacerte mas largo el post:

SELECT
id,tipo,SUM(cantidad) AS cantidad, sum(cantidad * precio * tc) as valor
FROM
Inventario I
INNER JOIN BODEGA B ON (I.bodega = B.id) and (I.empresa = B.empresa)
GROUP BY
I.id,I.tipo
WHERE
I.empresa=:numempresa AND B.nombre=:nombrebedega
INTO
#TEMP_Table

Ok, un tan solo SELECT que me genera una tabla donde ya estan resumidas las cantidades y los valores por codigo de producto y por movimiento, es decir hay tan solo un registro de cada codigo por todas las "E" y uno por todas la "I". en el where puedes agregar mas condiciones como de rango de fechas por ejemplo.

Ahora hagamos otro querry para generar el listado final enlazando la tabla INSUMOS y la tabla #temp_table:

SELECT
m.cod_insum,M.nom_insum, m.unidad, M.CRITICO, m.rotacion,
(SELECT t.cantidad from #Temp_table T WHERE t.id = m.cod_insumo AND t.tipo='I') -
((SELECT t.cantidad from #Temp_table T WHERE t.id = m.cod_insumo AND t.tipo='E') as saldo,
(SELECT t.valor from #Temp_table T WHERE t.id = m.cod_insumo AND t.tipo='I') -
((SELECT t.valor from #Temp_table T WHERE t.id = m.cod_insumo AND t.tipo='E')
as valor_saldo

Ok, aqui se ve que hay 4 SELECTS, sin envargo estos son aplicados a una tabla drasticamente reducida y las condiciones utilizadas son tribiales. Si se utilizan 2 tablas temporales una para entradas y otros para salidas entonces se reduciria a 2 SELECTS.

---------------------------------------------

Te recomiendo que le des una ojeada a la informacion sobre tablas temporales, estas se borran solas cuando se cierra la sesion, pero deveras borralas manualmente mientras la seccion este activa antes de hacer una siguiente consulta porque el SELECT INTO fallará cuando encuentre que ya hay una tabla temporal con ese nombre.


saludos.

[mjjj]

Mcuhas gracias por tu excelente y completa explicación, me quedo todo mucho mas claro, pero siempre surgen nuevas dudas.

El tema de la tablas temporales, es lo mismo que las talbas en memoria?
Estuve leyendo sobre alguno componentes que puedes servir para esto, será necesario instalar algun componente especial, o simplemente se puede generar con algun comando SQL.

Por otro lado, y gracias a la explicación del inner join, es que he tratado de implmentar esta idea a otra consultas dentro de mi aplicación y la verdad es que no me funciona nada de bien. Expongo mi código.

Código Delphi [-]
 IBQUERY6.Close;
 IBQUERY6.SQL.CLEAR;
 IBQUERY6.SQL.ADD('select area, sum(monto),');
 IBQUERY6.SQL.ADD('(SELECT SUM(MONTO_COMPR) FROM COMPRAS WHERE AREA = P.AREA AND ano=:ano AND mes=:mes AND EMPRESA =:EMPRESA');
 IBQUERY6.SQL.ADD('AND OFICINA ='+QUOTEDSTR(SCOMBOBOX3.TEXT));
 IBQUERY6.SQL.ADD('AND ESTADO ='+QUOTEDSTR('F')+'),');
 IBQUERY6.SQL.ADD('(SELECT COUNT(ESTADO) FROM COMPRAS WHERE AREA  = P.AREA AND ano=:ano AND mes=:mes AND EMPRESA =:EMPRESA');
 IBQUERY6.SQL.ADD('AND OFICINA ='+QUOTEDSTR(SCOMBOBOX3.TEXT));
 IBQUERY6.SQL.ADD('AND ESTADO ='+QUOTEDSTR('P')+')');
 IBQUERY6.SQL.ADD('from PRESUPUESTO P where EMPRESA =:EMPRESA');
 IBQUERY6.SQL.ADD('AND ano=:ano AND mes=:mes');
 IBQUERY6.SQL.ADD('AND OFICINA ='+QUOTEDSTR(INTTOSTR(OFI)));
 IBQUERY6.SQL.ADD('group BY AREA');
 IBQUERY6.parambyname('empresa').AsString:=empresa;
 IBQUERY6.parambyname('ano').asinteger:=calendar1.year;
 IBQUERY6.parambyname('mes').asinteger:=calendar1.month;
 ibquery6.Open;

lo que funciona bien, pero es relativamente lento, el mismo problema anterior, Select anidados, pero no se como hacerlo de otra manera.... ayuda porfa!!

intente utilizando esto:

Código SQL [-]
select
p.area , sum(P.monto) , sum(case when estado = 'F' then monto_compr else 0 end),
count(case when estado = 'P' then monto_compr else 0 end)
from presupuesto p inner join compras c on c.area = p.area and c.ano = p.ano
and p.mes = c.mes AND P.EMPRESA = C.EMPRESA
where p.mes = '2009'
and p.ano =   '1'
and P.empresa = 'emp'
AND P.oficina = '1'
AND C.OFICINA = 'SANTIAGO'
GROUP BY AREA

Me arroja valores que no tienen nada que ver, obvimante no funciona, el porqe es el que no se.

Lo que intento hacer es lo siguiente:

Un distado de todas la distintas area (tabla presupuesto), además de la sumatoria de lo compra (tabla compra estado = 'F') y la cantidad de compras excedidas (tabla compras estado ='P')

Ojala me puedan echar una manito con esta consulta.

Gracias

[[fjcg02]]

Bueno,
aqui tienes tu código.
Te faltaba meter la empresa en las condiciones del inner join
No se de dónde sale el parámetro oficina, que he quitado. El resto, es lo mismo que tenías, sólo tienes que comparar las querys.
El valor del parámetro empresa debe coincidir con algún valor de la tabla (emp1).
Me he basado en el juego de ensayo de la hoja de cálculo que has incluido.
Te aconsejo que juegues en una bañera primero, luego en una piscina y luego definitivamente te tires al mar. por qué? cuando hagas este tipo de consultas, hazlas sacando todos los valores, y de ahí vas filtrando poco a poco, es decir, intenta sacar los valores de las empresas primero, luego las empresas y las áreas, luego filtrando por empresa, area, .... hasta que salga lo que quieras.

Código SQL [-]
select p.empresa,p.area , 
                sum(P.monto) AS MONTO, 
                sum(case when estado = 'F' then monto_compr else 0 end) AS COMPRA,
               count(case when estado = 'P' then 1 else 0 end) AS EXCEDIDA
from presupuesto p inner join compras c on c.empresa=c.empresa and c.area = p.area and c.ano = p.ano
and p.mes = c.mes AND P.EMPRESA = C.EMPRESA AND P.AREA=C.AREA
where p.mes = '1'
and p.ano =   '2009'
and P.empresa = 'emp1'
GROUP BY P.empresa, P.AREA

Saludos, cuentanos cómo te queda.

[mjjj]

Esto no funciona ni por mas empeño que le ponga...

Código SQL [-]
select p.area ,
                sum(P.monto) AS MONTO, 
                sum(case when estado = 'F' then monto_compr else 0 end) AS COMPRA,
               count(case when estado = 'P' then 1 else 0 end) AS EXCEDIDA
from presupuesto p inner join compras c on c.empresa=c.empresa and c.area = p.area and c.ano = p.ano
and p.mes = c.mes AND P.EMPRESA = C.EMPRESA AND P.AREA=C.AREA and p.oficina = c.oficina
where p.mes = '1'
and p.ano =   '2009'
and P.empresa = 'emp1'
and p.oficina = '1'
GROUP BY P.AREA

Lo unico que concuerda son las distintas area que tengo, pero los montos y la cantidad estan disparados.

A mi entender es que al preguntar a 2 tablas, esta se multiplican, lo que conlleva a un enorme numero de registros. Entonces al hacer una consulta sobre estas tablas, las sumas y cantidad son mayores.

Ahora bien, eso creo que es lo que ocurre, pero no se como solucionarlo para obtener lo que ando buscando.

Corrijanme si estoi equivocando.

Gracias.

[[fjcg02]]

Qué motor de BBDD usas ?