lunes, 21 de abril de 2008

CONSULTA DE REFERENCIAS CRUZADAS DE MÚLTIPLES VALORES.


Los ejemplos están basados en la base de datos Neptuno.mdb.
En principio, una consulta de referencias cruzadas solo puede tener una expresión para calcular los encabezados de columna. Es decir, como ejemplo : no podemos obtener el número de pedidos por empleado y año, y al mismo tiempo, la fecha del último pedido por empleado y año.
Para solucionar este inconveniente, desde Microsoft proponen esta solución : http://support.microsoft.com/kb/304458/es
El inconveniente de este método es que necesita conocer previamente los nombres de las columnas generadas por las consultas de referencias cruzadas, pues desde la consulta donde se combinan las dos consultas previas, se hace referencia explícita a cada uno de los campos por su nombre. Esto, si las consultas devuelven un número fijo de campos no es problema, pero si el número de campos es variable ...
Una solución seria no llamar los campos directamente, sino utilizar el asterisco para seleccionarlos todos :
Supongamos que tenemos estas dos consultas :

RefCruzPedidosCantidad

TRANSFORM Count(Pedidos.IdPedido) AS CuentaDeIdPedido
SELECT Pedidos.IdEmpleado
FROM Pedidos
GROUP BY Pedidos.IdEmpleado
PIVOT "Pedidos_" & Year(FechaPedido);

RefCruzPedidosÚltimo

TRANSFORM Max(Pedidos.FechaPedido) AS MaxDeFechaPedido
SELECT Pedidos.IdEmpleado
FROM Pedidos
GROUP BY Pedidos.IdEmpleado
PIVOT "Último_" & Year(FechaPedido);

Para mostrar todos los resultados a la vez :

SELECT RefCruzPedidosCantidad.*, RefCruzPedidosÚltimo.*
FROM RefCruzPedidosCantidad INNER JOIN RefCruzPedidosÚltimo
ON RefCruzPedidosCantidad.IdEmpleado = RefCruzPedidosÚltimo.IdEmpleado;

Bien, efectivamente ahora se muestran todas las columnas sin necesidad de llamarlas de forma explícita. Sin embargo nos encontramos con el inconveniente de que el campo IdEmpleado aparece dos veces.
Para solucionar este problema, un método alternativo, que gracias a una consulta de unión, embebida dentro de la consulta de referencias cruzadas, permite obtener directamente los resultados :

TRANSFORM First(Temp.Valor)
SELECT Temp.IdEmpleado
FROM
(
Select IdEmpleado, Count(IdPedido) As Valor, Year(FechaPedido) As Año,
"Pedidos_" & Año As Campo
From Pedidos
Group by IdEmpleado, Year(FechaPedido)
Union Select IdEmpleado, Max(FechaPedido) As Valor, Year(FechaPedido) As Año,
"Último_" & Año As Campo
From Pedidos
Group by IdEmpleado, Year(FechaPedido)
) AS Temp
GROUP BY IdEmpleado
PIVOT Campo;

Finalmente, debido a que las columnas se generan en base al campo 'Campo', la consulta anterior muestra los campos en este orden :
IdEmpleado, Pedidos_1996, Pedidos_1997, Pedidos_1998, Último_1996, etc
Si queremos que aparezcan agrupadas por año, es necesario cambiar la expresión que genera los títulos de columna para que establezca otro orden :

TRANSFORM First(Temp.Valor)
SELECT Temp.IdEmpleado
FROM
(
Select IdEmpleado, Count(IdPedido) As Valor, Year(FechaPedido) As Año,
Año & "_Pedidos" As Campo
From Pedidos
Group by IdEmpleado, Year(FechaPedido)
Union Select IdEmpleado, Max(FechaPedido) As Valor, Year(FechaPedido) As Año,
Año & "_Último" As Campo
From Pedidos
Group by IdEmpleado, Year(FechaPedido)
) AS Temp
GROUP BY IdEmpleado
PIVOT Campo;

Ahora el orden de las columnas es :
IdEmpleado, 1996_Pedidos, 1996_Último, 1997_Pedidos, 1997_Último, etc

Ramon Poch. Terrassa a 05/01/2007.