Comparar años para articulos vendidos a un cliente

[octaviz]

Tengo una consulta para sacar un listado por meses de los artículos vendidos a un cliente con su importe, pero ahora necesito modificarla y poder comparar 2 años, no se si esto es posible. Yo no he sido capaz de hacerlo.

Esta es la consulta.

Declare @fromdate as datetime
Declare @Todate as datetime
Declare @Code as nvarchar(25)
set @fromdate = ( select min(Ta.[docDate]) from inv1 ta where Ta.[docDate] >= [%0])
set @Todate = ( select max(Tb.[docDate]) from inv1 tb where Tb.[docDate] <= [%1])
set @code = (select max(Tc.[cardName]) from OINV tC where Tc.[cardName] = [%2])


SELECT [Name] as Itemcode,[C] as Customername, [Jan]= sum([1]), [Feb]= sum([2]), [Mar]= sum([3]), [Apr]= sum([4]),  [May]= sum([5]), [June]= sum([6]), [July]= sum([7]), [Aug]= sum([8]), [Sept]= sum([9]), [Oct]= sum([10]), [Nov]= sum([11]), [Dec]= sum([12]), total = sum (isnull([1],0)+ isnull([2],0) + isnull([3],0) + isnull([4],0) +  isnull([5],0) + isnull([6],0) + isnull([7],0) + isnull([8],0) + isnull([9],0)+ isnull([10],0) + isnull([11],0) + isnull([12],0))

from


(SELECT T0.itemcode as Name, t2.cardname as C, t1.quantity
                 * t1.price [stock_value], month(T1.[docDate]) as month FROM OITM T0  INNER JOIN INV1 T1 ON T0.itemcode = T1.itemcode INNER JOIN OINV T2 ON T1.docentry = T2.docentry WHERE T1.[docDate] between @fromdate and @todate and  T2.[cardName]  = @code  GROUP BY T0.itemcode,T1.[docDate],t2.cardname,t1.quantity, t1.price ) S

Pivot
(sum([stock_value]) For  Month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
group by [Name],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[C]

For Browse

Muchas gracias.

[Willy_Caldero]

claro que es posible, con SQL se hace magia jajaj.
Para comparacion, generalmente usas un UNION con la consulta del proximo año, y luego haces un group by por articulo, y sacas los sumarizados. En ambas consultas debes preveer el total de los campos a mostrar, algo asi:

Select item, Sum(JanPrevYear), Sum(JanNextYear)
From
(select item, [Jan]= sum([1]), 0 as 'JanNextYear' .....
union
select item, 0 as 'JanprevYear',  [Jan]= sum([1]) .....
)
group by Item

Saludos,

[octaviz]

Muchas gracias Willy, intentaré hacer algo con Union a ver…

[rubenortiz]

Saludos, hermano saca toda esa informacion desde la abla OINM, unes el cardcode del cliente con la tabla ocrd, ahi ya tienes el neto, ya que en la tabla oinm caen las ventas, y notas de creditos, bueno debes filtar que solo te de los transtype 13 que son las ventas {OINM.OutQty}{OINM.calcPrice}, y el 14 que son las devoluciones {OINM.inQty}{OINM.calcPrice}*-1 este multiplicas las cantidadas por el calcprice o precio por menos 1 para que te lo netee al final de los productos y si tienes Crystal Report, es pan comido, seleccionas el cliente y la fecha desde hasta, la llevas A EXCEL Y LISTO, con ua tabla dnamica ves lo que quieras, saludos. si tienes el crystal avisame te puedo enviar la sentencias de sql de crystal y lo pegas a el tuyo , le haces los cambios correspondientes a tu bse de datos es decit de donde va a sacar la informacion, sluodos

[Un_Tal_Erik_J]

Adapta esto a tu requerimiento

select DISTINCT a.ItemCode, a.ItemName, a.OnHand, b.ItmsGrpNam , a.InvntryUom, a.BuyUnitMsr
, a.createDate as [Fecha de producción]

--------2019--------

—MAX----

, ( Select Max(Currency) From OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) And Createdate = (select MAX(createDate) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) AND createDate between ‘20190101’ and ‘20191231’) )as [MAX Moneda 2019]

, ( Select Max(CardCode) From OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) And Createdate = (select MAX(createDate) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) AND createDate between ‘20190101’ and ‘20191231’) )as [MAX SN 2019]

, (select MAX(createDate) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) AND createDate between ‘20190101’ and ‘20191231’) as [MAX Fecha 2019]

, (select MAX(Price) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) and createDate = ( select MAX(createDate) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) AND createDate between ‘20190101’ and ‘20191231’) ) as [MAX Price 2019]

----MIN----

, ( Select Min(CardCode) From OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) And Createdate = (select MAX(createDate) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) AND createDate between ‘20190101’ and ‘20191231’) )as [MIN SN 2019]

, (select MIN(createDate) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) AND createDate between ‘20190101’ and ‘20191231’ ) as [MIN Fecha 2019]

, (select MIN(Price) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) and createDate = ( select MAX(createDate) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) AND createDate between ‘20190101’ and ‘20191231’) ) as [MIN Price 2019]

-----2018----

, ( Select Max(Currency) From OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) And Createdate = (select MAX(createDate) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) AND createDate between ‘20180101’ and ‘20181231’) )as [MAX Moneda 2018]

, ( Select Max(CardCode) From OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) And Createdate = (select MAX(createDate) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) AND createDate between ‘20180101’ and ‘20181231’) )as [MAX SN 2018]

, (select MAX(createDate) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) AND createDate between ‘20180101’ and ‘20181231’) as [MAX fecha 2018]

, (select MAX(Price) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) and createDate = ( select MAX(createDate) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) AND createDate between ‘20180101’ and ‘20181231’) ) as [MAX Price 2018]

----MIN----

, ( Select Min(CardCode) From OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) And Createdate = (select MAX(createDate) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) AND createDate between ‘20180101’ and ‘20181231’) )as [MAX SN 2018]

, (select MIN(createDate) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) AND createDate between ‘20180101’ and ‘20181231’ ) as [MIN Fecha 2018]

, (select MIN(Price) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) and createDate = ( select MAX(createDate) from OINM where ItemCode = a.ItemCode and TransType in (20,69,10000071) AND createDate between ‘20180101’ and ‘20181231’) ) as [MIN Price 2018]

, CASE
WHEN a.validFor = ‘Y’ and a.frozenFor = ‘N’ THEN ‘Activo’
WHEN a.validFor = ‘N’ and a.frozenFor = ‘Y’ THEN ‘Inactivo’
EnD as Activo
from OITM a
inner join OITB b on a.ItmsGrpCod = b.ItmsGrpCod
inner join OITW c on a.ItemCode = c.ItemCode