Buenas Tardes,
Agradecería de su ayuda en a este reporte para poder agruparlo por Clases.
declare @dataInicio datetime
declare @dataFinal datetime
declare @tempo int
SET @tempo=(SELECT TOP 1 T0.TRANSNUM FROM OINM T0 WHERE T0.[DocDate] >='20200901' AND T0.[DocDate] <='20200930')
SET @dataInicio=(SELECT '20200901')
SET @dataFinal=(SELECT '20200930')
SELECT
T5.ItmsGrpnam AS CLASE,
[Descripcion]=t1.Descripcion,
Consumo=isnull(t1.consumo,0),
[Consumo medio diario]=isnull(t1.consumo/datediff(day,@dataInicio,@dataFinal),0),
[Stock inicial]=isnull(coalesce(t0.saldoinic,0),0),
[Stock final]=isnull(coalesce(t0.saldoinic,0)+coalesce(t1.saldofin,0),0),
[Stock comprometido]=T4.Iscommited,
[Stock promedio]= case when (isnull((coalesce(t0.saldoinic,0)+(coalesce(t0.saldoinic,0)+t1.saldofin))/2,0))=0 then isnull(t1.consumo,0) else isnull((coalesce(t0.saldoinic,0)+(coalesce(t0.saldoinic,0)+t1.saldofin))/2,0) end,
[Rotación stock]= case when (isnull((coalesce(t0.saldoinic,0)+(coalesce(t0.saldoinic,0)+t1.saldofin))/2,0)=0 and isnull(t1.consumo,0)=0) then 0 else case when (isnull((coalesce(t0.saldoinic,0)+(coalesce(t0.saldoinic,0)+t1.saldofin))/2,0)=0 and isnull(t1.consumo,0)<>0) then 1 else isnull(t1.consumo / ((coalesce(t0.saldoinic,0)+(coalesce(t0.saldoinic,0)+t1.saldofin))/2) ,0) end end,
[Cobertura de stock]= case isnull(coalesce(t1.consumo/datediff(day,@dataInicio,@dataFinal),0),0) when 0 then 0 else isnull(((coalesce(t0.saldoinic,0)+(t1.saldofin)))/ ( t1.consumo/datediff(day,@dataInicio,@dataFinal)),0) end,
[UltimaCompra]=T4.LastPurDat,
[Proveedor]=T4.CardCode
FROM (
SELECT
a1.itemcode,
Descripcion = Max(a1.itemname),
saldoInic=isnull(sum(coalesce(s1.Inqty,0))-sum(coalesce(s1.outqty,0)),0)
FROM OITM a1
JOIN OINM s1 on a1.itemcode=s1.itemcode
WHERE s1.docdate<@dataInicio and a1.ItmsGrpCod in ('101','105','124')
GROUP BY a1.itemcode
) as t0
RIGHT JOIN (
SELECT
a.itemcode,
Descripcion = Max(a.itemname),
entradas = isnull(sum(s.Inqty),0),
consumo = isnull(sum(m.outqty),0),
saldoFin = isnull(sum(s.Inqty)-sum(s.outqty),0)
FROM OINM s
JOIN OITM a on a.itemcode=s.itemcode JOIN OINM_1 m on s.transnum = m.transnum and s.transtype = m.transtype and s.doclinenum = m.doclinenum and s.itemcode = m.itemcode
WHERE s.docdate BETWEEN @dataInicio and @dataFinal and a.ItmsGrpCod in ('101','105','124')
GROUP BY a.itemcode) t1 on t1.itemcode = t0.itemcode join oitm t4 on t1.itemcode = t4.itemcode join oitb T5 ON T4.ItmsGrpCod = T5.ItmsGrpCod
GROUP BY T5.ItmsGrpnam,t1.consumo,t0.saldoinic,t0.saldoinic,t1.saldoFin,t1.ItemCode,t1.Descripcion,T4.Iscommited,T4.LastPurDat,T4.CardCode