Query muy complicado

26/10/2003 - 21:26 por marcelo | Informe spam
Hola,
(Abajo del mensaje incluyo las sentencias DDL de creación
de las tablas con algunos datos)
Tengo que insertar en una tabla de ESTADISTICAS a partir
de la tabla de FACTURAS y DESCRIPCIONES los datos de
FacturaID, TipoMovimientoID e Importe.
Los campos que contienen las tablas son:
*FACTURAS: FacturaID, Fecha, Importe,DESCRIP_MOVIM y
ClienteID
*DESCRIPCIONES: TipoMovimientoID y DESCRIP_MOVIM
*MOVIMFACTURAS: FacturaID, NroRenglon y Subtotal.

Descripción del problema:
Me vienen FACTURAS con el campo DESCRIP_MOVIM con una
descripción del Movimiento (con estos 2 valores: XXX o
YYY) pero como lo que debo insertar es
el "TipoMovimientoID" debo hacer Join con la tabla
DESCRIPCIONES con el campo DESCRIP_MOVIM para de esta
manera obtener el "TipoMovimientoID" que le corresponde a
dicha descripción e insertarlo en la tabla ESTADISTICAS

El GRAN problema es que en la tabla DESCRIPCIONES tengo 4
registros con estos valores:

TipoMovimientoID DESCRIP_MOVIM
1 XXX
2 YYY
3 XXX
4 XXX

Con lo cual XXX está en 3 registros y cuando hago el join
entre las tablas FACTURAS y DESCRIPCIONES por el campo
DESCRIP_MOVIM si la descripción de Facturas
(DESCRIP_MOVIM) es "XXX" entonces me devuelve 3 registros
cuando yo quiero que me devuelva 1 sólo con
el "TipoMovimientoID" que le corresponde.
Para lo cual según la descripción en FACTURAS es XXX me
tiene que devolver
* TipoMovimientoID = 1 sí la FACTURA tiene al menos 1
movimiento en la tabla MOVIMFACTURAS:
* TipoMovimientoID = 3 Si el CLIENTEID de la tabla
FACTURAS es > 10.000
* TipoMovimientoID = 4 Si NO es nada de lo anterior


* TipoMovimientoID = 2 SALDRÍA DIRECTO CON EL Join entre
tablas FACTURAS y DESCRIPCIONES por el campo
DESCRIP_MOVIM ya que hay un sólo registro con la
descripción "YYY"

Nota: estos 4 son excluyentes, es decir jamás se podría
dar la condición del TipoMovimientoID 1 y 3, por ejemplo.

Tampoco puedo cambiar nada de la estructura de como esta
hecho esto. Sólo me vienen esas tablas y tengo que hacer
esa inserción.

Se puede hacer con una única instrucción INSERT el query
de inserción con el TipoMovimientoID????
Cómo lo harían????


Sentencias DDL
CREATE TABLE [dbo].[Descripciones] (
[TipoMovimientoID] [int] NOT NULL ,
[Descrip_Movim] [nvarchar] (15) COLLATE
Modern_Spanish_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Estadisticas] (
[FacturaID] [int] NOT NULL ,
[TipoMovimientoID] [int] NOT NULL ,
[Importe] [float] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Facturas] (
[FacturaID] [int] NOT NULL ,
[Fecha] [datetime] NOT NULL ,
[Importe] [float] NOT NULL ,
[Descrip_Movim] [nvarchar] (15) COLLATE
Modern_Spanish_CI_AS NOT NULL ,
[ClienteID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MovimFacturas] (
[FacturaID] [int] NOT NULL ,
[NroRenglon] [int] NOT NULL ,
[Subtotal] [float] NOT NULL
) ON [PRIMARY]
GO

Insert Into DESCRIPCIONES
values(1, 'XXX')

Insert Into DESCRIPCIONES
values(2, 'YYY')

Insert Into DESCRIPCIONES
values(3, 'XXX')

Insert Into DESCRIPCIONES
values(4, 'XXX')

Insert Into FACTURAS
values(12, '01/12/2003', 25, 'XXX', 1)

Insert Into FACTURAS
values(21, '05/12/2003', 10, 'XXX', 2)

Insert Into FACTURAS
values(42, '07/12/2003', 31, 'YYY', 9)

Insert Into FACTURAS
values(49, '07/12/2003', 18, 'XXX', 2)

Insert Into FACTURAS
values(58, '09/12/2003', 40, 'XXX', 15000)

Insert Into MOVIMFACTURAS
values(21, 1, 15)

Insert Into MOVIMFACTURAS
values(21, 2, 28)
 

Leer las respuestas

#1 Javier Loria
27/10/2003 - 16:34 | Informe spam
Hola Marcelo:
Muchas Gracias por el codigo, realmente ayuda.
Espero que tu codigo sea de migracion, limpieza o DataWarehouse porque
definitivamente la relacion entre Facturas y Descripciones esta horrible.
Asumo que todas las columnas xxxID son llaves Primarias.
Este codigo NO debe ser muy eficiente pero espero haga lo que quieres:
/* Codigo Feo y Lento, pero hace lo pedido */
INSERT Estadisticas (FacturaID, TipoMovimientoID, Importe)
SELECT FacturaID,
Descripciones.TipoMovimientoID,
Importe
FROM
(SELECT Facturas.FacturaID,
Facturas.Descrip_Movim,
Facturas.Importe,
Facturas.ClienteID,
SUM( CASE
WHEN MovimFacturas.FacturaID IS NULL
THEN 0
ELSE 1
END) AS NumMovimientos
FROM Facturas
LEFT JOIN MovimFacturas
ON Facturas.FacturaID=MovimFacturas.FacturaID
GROUP BY Facturas.FacturaID,
Facturas.Descrip_Movim,
Facturas.Importe,
Facturas.ClienteID)
AS FacturasConMovimientos
JOIN Descripciones
ON (FacturasConMovimientos.Descrip_Movim Descripciones.Descrip_Movim) AND
((FacturasConMovimientos.Descrip_Movim<>'XXX') OR
(CASE
WHEN
FacturasConMovimientos.Descrip_Movim='XXX'
AND FacturasConMovimientos.NumMovimientos>=1
THEN 1
WHEN
FacturasConMovimientos.Descrip_Movim='XXX'
AND FacturasConMovimientos.ClienteID>000
THEN 3
WHEN
FacturasConMovimientos.Descrip_Movim='XXX'
THEN 4
END)=Descripciones.TipoMovimientoID)

/* Fin de Codigo Feo y Lento */
Una explicacion rapida:
a) En el FROM veras que hay una tabla "derivada" para incluir la columna
de Numero de Movimientos, esta columna se calcula con una especie de COUNT
ya que la funcion COUNT da una mala cuenta por el LEFT JOIN.
b) El codigo seria mucho mas claro si esta tabla se convierte en una
vista, si piensas que esta columna te servira para otras cosas yo usaria la
vista, si este codigo es unico, probablemente lo dejaria como esta.
c) La condicion del JOIN "normaliza" la relacion entre Facturas y
Descripcion, en cada uno de los case hay una condicion:
FacturasConMovimientos.Descrip_Movim='XXX', que NO es necesaria , por la
combinacion de <>'XXX' y el OR; pero igual la deje porque me parece puede
ayudar al optimizador y puede ayudar a hacer mas facil de mantener el
codigo, sobre todo si luego aparece otra condicion adicional.
d) (FacturasConMovimientos.Descrip_Movim<>'XXX') puede "optimizarse"
como FacturasConMovimientos.Descrip_Movim NOT IN ('XXX'), pero dudo mucho
que le sirva al optimizador nada con el OR que viene posteriormente.
e) Es MUY importante el Orden en que aparezcan los WHEN en el CASE, ya
que el primero que se cumpla es el valedero.
f) No te recomiendo el uso de los FLOAT te van a producir errores de
redondeo horribles, usa mejor NUMERIC o DECIMAL.

Espero te sirva y haga lo deseado,



Javier Loria
Costa Rica
Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
que pueda ser copiado y pegado al Query Analizer.
La version de SQL y Service Pack tambien ayuda..


marcelo escribio:
Hola,
(Abajo del mensaje incluyo las sentencias DDL de creación
de las tablas con algunos datos)
Tengo que insertar en una tabla de ESTADISTICAS a partir
de la tabla de FACTURAS y DESCRIPCIONES los datos de
FacturaID, TipoMovimientoID e Importe.
Los campos que contienen las tablas son:
*FACTURAS: FacturaID, Fecha, Importe,DESCRIP_MOVIM y
ClienteID
*DESCRIPCIONES: TipoMovimientoID y DESCRIP_MOVIM
*MOVIMFACTURAS: FacturaID, NroRenglon y Subtotal.

Descripción del problema:
Me vienen FACTURAS con el campo DESCRIP_MOVIM con una
descripción del Movimiento (con estos 2 valores: XXX o
YYY) pero como lo que debo insertar es
el "TipoMovimientoID" debo hacer Join con la tabla
DESCRIPCIONES con el campo DESCRIP_MOVIM para de esta
manera obtener el "TipoMovimientoID" que le corresponde a
dicha descripción e insertarlo en la tabla ESTADISTICAS

El GRAN problema es que en la tabla DESCRIPCIONES tengo 4
registros con estos valores:

TipoMovimientoID DESCRIP_MOVIM
1 XXX
2 YYY
3 XXX
4 XXX

Con lo cual XXX está en 3 registros y cuando hago el join
entre las tablas FACTURAS y DESCRIPCIONES por el campo
DESCRIP_MOVIM si la descripción de Facturas
(DESCRIP_MOVIM) es "XXX" entonces me devuelve 3 registros
cuando yo quiero que me devuelva 1 sólo con
el "TipoMovimientoID" que le corresponde.
Para lo cual según la descripción en FACTURAS es XXX me
tiene que devolver
* TipoMovimientoID = 1 sí la FACTURA tiene al menos 1
movimiento en la tabla MOVIMFACTURAS:
* TipoMovimientoID = 3 Si el CLIENTEID de la tabla
FACTURAS es > 10.000
* TipoMovimientoID = 4 Si NO es nada de lo anterior


* TipoMovimientoID = 2 SALDRÍA DIRECTO CON EL Join entre
tablas FACTURAS y DESCRIPCIONES por el campo
DESCRIP_MOVIM ya que hay un sólo registro con la
descripción "YYY"

Nota: estos 4 son excluyentes, es decir jamás se podría
dar la condición del TipoMovimientoID 1 y 3, por ejemplo.

Tampoco puedo cambiar nada de la estructura de como esta
hecho esto. Sólo me vienen esas tablas y tengo que hacer
esa inserción.

Se puede hacer con una única instrucción INSERT el query
de inserción con el TipoMovimientoID????
Cómo lo harían????


Sentencias DDL
CREATE TABLE [dbo].[Descripciones] (
[TipoMovimientoID] [int] NOT NULL ,
[Descrip_Movim] [nvarchar] (15) COLLATE
Modern_Spanish_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Estadisticas] (
[FacturaID] [int] NOT NULL ,
[TipoMovimientoID] [int] NOT NULL ,
[Importe] [float] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Facturas] (
[FacturaID] [int] NOT NULL ,
[Fecha] [datetime] NOT NULL ,
[Importe] [float] NOT NULL ,
[Descrip_Movim] [nvarchar] (15) COLLATE
Modern_Spanish_CI_AS NOT NULL ,
[ClienteID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MovimFacturas] (
[FacturaID] [int] NOT NULL ,
[NroRenglon] [int] NOT NULL ,
[Subtotal] [float] NOT NULL
) ON [PRIMARY]
GO

Insert Into DESCRIPCIONES
values(1, 'XXX')

Insert Into DESCRIPCIONES
values(2, 'YYY')

Insert Into DESCRIPCIONES
values(3, 'XXX')

Insert Into DESCRIPCIONES
values(4, 'XXX')

Insert Into FACTURAS
values(12, '01/12/2003', 25, 'XXX', 1)

Insert Into FACTURAS
values(21, '05/12/2003', 10, 'XXX', 2)

Insert Into FACTURAS
values(42, '07/12/2003', 31, 'YYY', 9)

Insert Into FACTURAS
values(49, '07/12/2003', 18, 'XXX', 2)

Insert Into FACTURAS
values(58, '09/12/2003', 40, 'XXX', 15000)

Insert Into MOVIMFACTURAS
values(21, 1, 15)

Insert Into MOVIMFACTURAS
values(21, 2, 28)

Preguntas similares