SQL inventario

[Bpascal]

Hola Amigo,

disculpa la tardanza y disculpa que no fui explicitamente claro con el INNER JOIN, inlcluso no te dije toda la historia, la cual es necesario que la domines de lo contrario no podras hacer querys eficientes.

Por favor lee detalladamente las explicacion a continuacion del JOIN y al final del post te escribo el querry que ocupas.

Primero debes saber que hay dos sabores de JOIN:

INNER JOIN
LEFT OUTER JOIN

si tenemos tabla_A:
Id, englishword
--------------
1 bird
2 house
3 dog
5 cat
6 black

si tenemos tabla_B:
Id, spanishword
--------------
1 pajaro
2 casa
3 perro
5 gato

si hacemos el siguiente select:

Código SQL [-]
SELECT 
 a.id,a.englishword,b.spanishword 
FROM
TABLA_A a
INNER JOIN TABLA_B b ON a.id = b.id

el resultado seria:

1 bird pajaro
2 house casa
3 dog perro
5 cat gato

Como vez, el ultimo registro de la tabla_A con id = 6 no aparece en la lista, esto se deve a que INNER JOIN se usa para seleccionar solo los registros que coincidan en el campo relacionado. Como el ID=6 no esta en la tabla_B entonces ese registro no sale. El inner join es mas utilizalo para enlazar tablas de transacciones con su maestro, o tablas detalle con encabezado.

Ahora, si queremos sacar TODOS los registros de la tabla_A con su respectivo dato de la tabla_B pero tambien incluyendo los que no pudieran tener equivalencia en tabla_B entonces harias:

Código SQL [-]
SELECT 
 a.id,a.englishword,b.spanishword 
FROM
TABLA_A a
LEFT OUTER JOIN TABLA_B b ON a.id = b.id

el resultado seria:

1 bird pajaro
2 house casa
3 dog perro
5 cat gato
6.black NULL

Los registros que no tengan su correspondiente equivalencia en tabla_B aparece con valor NULL.

Hasta aqui ya te estaras imaginando que lo que necesitas es usar LEFT OUTER JOIN, porque basicamente tu quieres repasar todos los registros de la tabla presupuesto la par con resumenes si (si los hubiera) de la tabla compras.

Sin envargo en estos ejemplo la relacion es de 1 a 1, o almenos de muchos a 1 es decir, por un registro en la tabla A se espera un tan solo registro en la tabla B, pero el asunto se vuleve diferente cuando la relacion es uno a muchos, por ejemplo:

si la tabla B tuviera:
Id, spanishword
--------------
1 pajaro
1 ave
1 plumifero
2 casa
3 perro
5 gato

como vez, el id=1 esta 3 veces

si se aplica el utimo querry el resultado seria:

1 bird pajaro
1 bird ave
1 bird plumifero
2 house casa
3 dog perro
5 cat gato
6.black NULL

Entonces tu ves que el dato "bird" de la tabla_A se REPITE 3 veces, una vez por cada registro con el ID=1 encontrado en la tabla B.

Por consiguinete, si tu haces el siguinete querry:

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 
LEFT OUTER 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

Los sum() y los count() de la tabla COMPRAS saldran bien, pero los de la tabla presupuesto saldran mal ya que ahora hay datos duplicados por cada ocurrencia encontrada en compras, de la misma manera como se duplica el dato "Bird" en el ejemplo que te puse tambien aqui el dato p.monto saldra duplicado.

Entonces como se resuleve este tipo de problemas? bueno se resuelve resumiendo la tabla compras UNA TAN SOLA VEZ y enlazando el resultado a la tabla presupuesto usando LEFT OUTER JOIN,

Es decir envez de escribir el nombre de la tabla despues del left outer join se puede perfectamente escribir un SELECT que resume ya la tabla antes de hacer el enlazado, ejemplo:

from presupuesto p LEFT OUTER JOIN

(SELECT
area,ano,mes,empresa,oficina,sum(Monto_compr) as T_monto_compr, count(mont_compr) as C_mon_compr
FROM
Compras
group by area,ano,mes,empresa,oficina)

C ON p.area = c.area and p.ano = c.ano and p.mes = c.mes AND P.EMPRESA = C.EMPRESA

Esto nos dara una especie de tabla temporal ya resumida por lo que queramos y poder ser enlazada con otra tabla maestra por ejemplo usando inner o outer join segun sea la necesidad.

Ya terminamos?, no todavia, , c.Tot_compr y c.C_mon_compr ya tienen el total de la compra y la cuenta de la compra lista para enlazarla con una tabla mestra, pero tu todavia necesitas resumir la tabla presupuesto para sumar y contar el valor monto.

La manera de sumarizar la tabla presupuesto e incluir los totales de la tabla compra sin duplicar se puede hacer usando el comando DISTINCT.

creo que este es el querry final:

SELECT
p.area , sum(P.monto) , sum(distintc c.tot_compr), sum(distintc c.C_mon_compr)
FROM
presupuesto p
LEFT OUTER JOIN

(SELECT
area,ano,mes,empresa,oficina,sum(Monto_compr) as T_monto_compr, count(mont_compr) as C_mon_compr
FROM
Compras
group by area,ano,mes,empresa,oficina)

C ON p.area = c.area and p.ano = c.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

Favor notar que en el select de la tabla compras la estoy resumiendo sin tomar en considerancion el campo estado.


Espero que te funcione o por lo menos te haya dado la suficiente informacion para adaptar tus querrys.

[[AzidRain]]

Haz dado en el clavo BPascal, lo que el amigo necesitaba es una explicación así de clara de lo joins...mis respetos señor.

Estaba analizando el caso y no se si me equivoque pero al parecer nuestro amigo atacó el problema con lo que él ya sabía sin investigar un poquito más a ver que otras cosas se pueden hacer. A todos nos pasó en su momento y más de uno regresamos a nuestros propios códigos a "optimizarlos" .

[mjjj]

Muchas gracias por tu tan clara explicación, pero ocurre algo, al tratar de hacer la consulta en el IBExpert, me arroja un error en la primera linea, No reconoce el campo "C.T_monto_compr", que es el campo de la sumatoria de la tabla compras "sum(Monto_compr) as T_monto_compr".

Al parecer no reconoce este campo ya que es la respuesta de la segunda consulta.

porque ocurrira esto?
utilizo Firebird 2.0, sera que en esta version no se puede realizar este tipo de consultas?

y lo ultimo, donde agrego la considion del campo estado de la tabla compras?
ya que son 2 condiciones distintas, 'F' para la suma y 'P' para el count.

Les dejo mis dudas, y de ante mano muchas gracias por todo, me han ayuda muchisimo.

Saludos

[[fjcg02]]

Eh! Bpascal, estupendísima explicación. Además eres el único que has dado en el clavo.
Yo creo que hemos aprendido todos algo con tu aportación.

Saludos

[Bpascal]

Muchas gracias por tu tan clara explicación, pero ocurre algo, al tratar de hacer la consulta en el IBExpert, me arroja un error en la primera linea, No reconoce el campo "C.T_monto_compr", que es el campo de la sumatoria de la tabla compras "sum(Monto_compr) as T_monto_compr".

Revisa bien si es un error de deletreo de nombres, espero que eso sea, el codigo lo probé en MsSql 2005, asegurate que el SELECT donde se resume la tabla de compras esta entre parentesis y que el alias "C" esta DESPUES de cerrar parentesis.

y lo ultimo, donde agrego la considion del campo estado de la tabla compras?
ya que son 2 condiciones distintas, 'F' para la suma y 'P' para el count.

En el SELECT donde se resume la tabla compras, toma nota que es muy similar a tu codigo original la diferencia es que el select anidado que utilizabas se movió del select principal y aqui no lleva condiciones.

[mjjj]

Bpascal, lo he verificado varias veces, y no es un problema de deletreo, incluso probe con otro ejemplo utilizando la misma idea y tampoco funciona.

Al parecer Firebird 2.0, no soporta este tipo de consultas.

Podrá ser esto??

[[fjcg02]]

Pero esto sí debe de 'tragar'

Código SQL [-]
select p.area , P.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 p.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, P.MONTO

Date cuenta de que he quitado la función de agregado al campo monto y lo he añadido al group by.

Ya nos contarás.

Saludos

[mjjj]

Amigos, no me funciona para nada. La idea que mas me gusta fue la de Bpascal, pero tampoco resulto, revise que estuviera todo bien escrito, y asi es.

Será que utilizo Firebird 2.0, que no permite este tipo de consultas.

Si es asi que me recomiendan hacer?

Gracias