Conteo de facturas por año (con PIVOT)
Buen día a la comunidad de SAP Business One, este es el primer post que escribo.
El motivo es para compartirles un pequeño query para poder contar las facturas
generadas por año y por cada cliente. Todo esto surgió a partir de la pregunta
que nos hizo Alessandro López y me pareció muy interesante. Por ello quice
compartirles mi propuesta a esa pregunta.
-- DECLARACIÓN DE VARIABLES
DECLARE @Cad VARCHAR(MAX), @SQL NVARCHAR(MAX)
-- INICIALIZACIÓN DE VARIABLES
SET @Cad = ','
-- CONCATENAR AÑOS DE FACTURAS
SELECT @Cad = @Cad + ',[' + Años.Año + ']'
FROM (SELECT DISTINCT CONVERT(VARCHAR,YEAR(DocDate)) [Año] FROM OINV) [Años]
-- QUITAR COMAS AL PRINCIPIO
SELECT @Cad = REPLACE(@Cad,',,','')
-- CONSULTA GENERAL
SET @SQL= N'
SELECT * FROM
(
SELECT CardCode, CardName, YEAR(DocDate) [Año], DocEntry
FROM OINV
) [T0]
PIVOT (
COUNT(T0.DocEntry) FOR T0.[Año] IN (' + @Cad + ')
)AS [Count]
'
-- EJECUTAR SQL DINÁMICO
EXECUTE SP_EXECUTESQL @SQLComo se pueden dar cuenta, utilizo SQL dinámico, esto para que nuestro pivote no sea
estático y tener que estar modificando la consulta cada que pasen los años. Desde luego,
la consulta se puede optimizar y también utilizar en algún otro documento, solo es cuestión
de cambiar las tablas.
Aquí el resultado:
Bueno, eso es todo y espero les sea de gran utilidad.
Saludos cordiales.
Carlos Ventura.
Like!
Yeah !!
Aquí el mismo query con una ligera optimización (ordenación de código de cliente, y también de los años ya que en algunos casos podría mostrarse desordenados los años [me pasó a mi]):
-- DECLARACIÓN DE VARIABLES
DECLARE @Cad VARCHAR(MAX), @SQL NVARCHAR(MAX)
-- INICIALIZACIÓN DE VARIABLES
SET @Cad = ''
-- CONCATENAR AÑOS DE FACTURAS
SELECT @Cad = '[' + REPLACE((SELECT Annio AS [data()] FROM
(SELECT distinct year(docdate) Annio FROM OINV T0) T order by 1 FOR XML PATH ('')), ' ', '],[') +']'
-- CONSULTA GENERAL
SET @SQL= N'
SELECT * FROM
(SELECT T2.CardCode, T2.CardName, YEAR(T1.DocDate) [Año], T1.DocEntry
FROM OINV T1 inner join OCRD T2 on T1.cardcode = T2.cardcode) [T0]
PIVOT (
COUNT(T0.DocEntry) FOR T0.[Año] IN (' + @Cad + ')
)AS [Count] order by 1
'
-- EJECUTAR SQL DINÁMICO
EXECUTE SP_EXECUTESQL @SQL
Saludos,
JC.