Inventario - Análisis de Artículos - PIVOTE

Hola!, buen día 😀... les dejo esta consulta con dos finalidades:

1. Consultar en SAP la ultima entrada (fecha, total cantidad 1 año y cantidad ultima salida) y la ultima salida  (fecha, total cantidad 1 año y cantidad ultima salida) de los artículos.
2. Conozcas a través de un ejercicio como funciona el Pivote en SQL Server, en este caso utilizo a los almacenes que están en una misma columna para que se abra cada almacén en una solo columna una por cada almacén, de esta forma evito repeticiones en el Item de mi consulta.

Espero les ayude bastante, hasta la próxima!.

select ItemCode, ItemName, OnHand, ItmsGrpNam,[Fecha de producción],[Ultima Entrada], [Cantidad Ultima Salida], [Total Salidas]

, [Cantidad Ultima Entrada], [Total Entradas], [Ultima Salida], [ALM1], [ALM2], [ALM3],[ALM4] 

from (

select a.ItemCode, a.ItemName, a.OnHand, b.ItmsGrpNam

, a.createDate as [Fecha de producción]

, (select MAX(createDate) from OINM where ItemCode = a.ItemCode and  TransType in (20,69,10000071)) as [Ultima Entrada]

, (select SUM(InQty) 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)  ) ) as [Cantidad Ultima Entrada]

, (select SUM(InQty) from OINM where ItemCode = a.ItemCode and  TransType in (20,69,10000071) and createDate  between '20180101' and '20181231'  ) as [Total Entradas 2018]

, (select MAX(createDate) from OINM where ItemCode = a.ItemCode and  TransType in (60)) as [Ultima Salida]

, (select SUM(OutQty) from OINM where ItemCode = a.ItemCode and  TransType in (60) and createDate = ( select MAX(createDate) from OINM where ItemCode = a.ItemCode and  TransType in (60)  ) ) as [Cantidad Ultima Salida]

, (select SUM(OutQty) from OINM where ItemCode = a.ItemCode and  TransType in (60) and createDate between '20180101' and '20181231' ) as [Total Salidas 2018]

, c.OnHand [Stock en WHSE], a.BuyUnitMsr,a.InvntryUom, c.WhsCode

, 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 

left join OITW c on a.ItemCode = c.ItemCode

and c.WhsCode in ('ALM1', 'ALM2', 'ALM3','ALM4') 

) consulta

PIVOT

(

AVG (consulta.[Stock en WHSE])

FOR WhsCode in ([ALM1], [ALM2], [ALM3],[ALM4])

)AS pvt

Ventas - Ventas por cliente y articulo

Hola!, paso de forma muy rapída para compartirles esta consulta que me han solicitado mucho los agentes de ventas y que a ustedes les puede servir mucho de igual manera, saludos!.

select a.CardCode,

c.CardName,

a.DocDate,

DATEPART(month , a.DocDate) AS Periodo,

DATEPART(YEAR , a.DocDate) AS Año,

case a.DocSubType 

when 'DN' THEN 'Nota de débito'

when '--' THEN 'Factura'

END AS [Tipo de dcumento], 

a.DocNum,

a.DocEntry IrDocumento,

b.ItemCode,

case a.DocType When 'S' THEN b.dscription ELSE d.ItemName  END AS ItemName, 

b.Price,

case a.CANCELED

WHEN 'C' THEN case a.DocType When 'S' THEN -1 ELSE b.Quantity * -1  END

WHEN 'N' THEN case a.DocType When 'S' THEN 1 ELSE b.Quantity END

ELSE case a.DocType When 'S' THEN 1 ELSE b.Quantity END

END as Quantity,

case a.CANCELED WHEN 'C'

THEN case a.DocType When 'S'

 THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1) * - 1 ELSE (b.Price * 1) * e.rate  * -1 END

ELSE case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)  * -1 ELSE (b.Price * b.Quantity) * e.rate * -1 END

END

WHEN 'N' THEN

case a.DocType When 'S'

        THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1)  ELSE (b.Price * 1) * e.rate END

ELSE          case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)  ELSE (b.Price * b.Quantity) * e.rate END

END

ELSE

case a.DocType When 'S'

        THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1)  ELSE (b.Price * 1) * e.rate END

ELSE          case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)  ELSE (b.Price * b.Quantity) * e.rate END

END

END

AS [Total MXN],

case a.CANCELED WHEN 'C'

THEN case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1) * -1 ELSE (b.Price * 1) / e.rate * -1 END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity)  * -1 ELSE (b.Price * b.Quantity)  / e.rate* -1 END

END

WHEN 'N' THEN

case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1)  ELSE (b.Price * 1)  / e.rate END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity) ELSE (b.Price * b.Quantity) / e.rate END

END

ELSE

case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1)  ELSE (b.Price * 1)  / e.rate END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity) ELSE (b.Price * b.Quantity) / e.rate END

END

END

 AS [Total USD],

a.CANCELED,

a.Comments

from OINV a

inner join INV1 b on  a.docentry = b.docentry

inner join OCRD c on a.CardCode = c.CardCode

left join OITM d on b.ItemCode = d.ItemCode 

left join ORTT e on a.CreateDate = e.RateDate and e.Currency = 'USD'

where a.docdate between [%0]  and [%1]

UNION ALL

select a.CardCode,

c.CardName,

a.DocDate,

DATEPART(month , a.DocDate) AS Periodo,

DATEPART(YEAR , a.DocDate) AS Año,

case a.ObjType 

when 14 THEN 'Nota de Crédito'

END AS [Tipo de dcumento], 

a.DocNum,

a.DocEntry IrDocumento,

b.ItemCode,

case a.DocType When 'S' THEN b.dscription ELSE d.ItemName  END AS ItemName, 

b.Price,

case a.CANCELED

WHEN 'C' THEN case a.DocType When 'S' THEN 1 ELSE b.Quantity  END

WHEN 'N' THEN case a.DocType When 'S' THEN -1 ELSE b.Quantity * - 1 END

ELSE case a.DocType When 'S' THEN -1 ELSE b.Quantity * -1 END

END as Quantity,

case a.CANCELED WHEN 'C'

THEN case a.DocType When 'S'

 THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1) ELSE (b.Price * 1) * e.rate   END

ELSE case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)   ELSE (b.Price * b.Quantity) * e.rate  END

END

WHEN 'N' THEN

case a.DocType When 'S'

        THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1) * -1 ELSE (b.Price * 1) * e.rate * -1 END

ELSE          case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity) * -1  ELSE (b.Price * b.Quantity) * e.rate * -1 END

END

ELSE

case a.DocType When 'S'

        THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1) * -1  ELSE (b.Price * 1) * e.rate * -1 END

ELSE          case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)  * -1 ELSE (b.Price * b.Quantity) * e.rate * -1 END

END

END

AS [Total MXN],

case a.CANCELED WHEN 'C'

THEN case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1)  ELSE (b.Price * 1) / e.rate  END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity)   ELSE (b.Price * b.Quantity)  / e.rate  END

END

WHEN 'N' THEN

case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1) * -1 ELSE (b.Price * 1)  / e.rate * -1 END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity) * -1 ELSE (b.Price * b.Quantity) / e.rate * -1 END

END

ELSE case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1) * -1 ELSE (b.Price * 1)  / e.rate * -1 END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity) * -1 ELSE (b.Price * b.Quantity) / e.rate * -1 END

END

END

 AS [Total USD],

--d.ItemName, 

--b.Price,

--b.Quantity * -1,

--case a.CurSource WHEN 'L' THEN  b.Price * b.Quantity * -1 ELSE (b.Price * b.Quantity * -1) * e.rate END AS [Total MXN],

--case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity * -1) / e.rate ELSE (b.Price * b.Quantity * -1) END AS [Total USD],

a.CANCELED,

a.Comments

from ORIN a

--left join RIN1 e on a.Docentry = e.BaseEntry

inner join RIN1 b on  a.docentry = b.docentry

inner join OCRD c on a.CardCode = c.CardCode

left join OITM d on b.ItemCode = d.ItemCode 

left join ORTT e on a.CreateDate = e.RateDate and e.Currency = 'USD'

where a.docdate between [%0]  and [%1]