Agrupar por Clase

SAP Business One
#1

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
#2

Gracias,

Ya lo pude resolver aquí le dejo el Query.

declare @dataInicio datetime
declare @dataFinal datetime
declare @tempo int


SET @tempo=(SELECT TOP 1 T0.TRANSNUM FROM OINM T0 WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1])
SET @dataInicio=(SELECT '[%0]')
SET @dataFinal=(SELECT '[%1]')


SELECT
[Clase]=T5.ItmsGrpnam,

Consumo=isnull(sum(t1.consumo),0),
[Consumo medio diario]=isnull(sum(t1.consumo)/datediff(day,@dataInicio,@dataFinal),0),
[Stock inicial]=isnull(coalesce(sum(t0.saldoinic),0),0),
[Stock final]=isnull(coalesce(sum(t0.saldoinic),0)+coalesce(sum(t1.saldofin),0),0),
[Stock comprometido]=sum(T4.Iscommited),
[Stock promedio]= case when (isnull((coalesce(sum(t0.saldoinic),0)+(coalesce(sum(t0.saldoinic),0)+sum(t1.saldofin)))/2,0))=0 then isnull(sum(t1.consumo),0) else isnull((coalesce(sum(t0.saldoinic),0)+(coalesce(sum(t0.saldoinic),0)+sum(t1.saldofin)))/2,0) end,
[Rotación stock]= case when (isnull((coalesce(sum(t0.saldoinic),0)+(coalesce(sum(t0.saldoinic),0)+sum(t1.saldofin)))/2,0)=0 and isnull(sum(t1.consumo),0)=0) then 0 else case when (isnull((coalesce(sum(t0.saldoinic),0)+(coalesce(sum(t0.saldoinic),0)+sum(t1.saldofin)))/2,0)=0 and isnull(sum(t1.consumo),0)<>0) 
then 1 else isnull(sum(t1.consumo) / ((coalesce(sum(t0.saldoinic),0)+(coalesce(sum(t0.saldoinic),0)+sum(t1.saldofin)))/2) ,0) end end,
[Cobertura de stock]= case isnull(coalesce(sum(t1.consumo)/datediff(day,@dataInicio,@dataFinal),0),0) when 0 then 0 else isnull(((coalesce(sum(t0.saldoinic),0)+(sum(t1.saldofin))))/ (SUM(t1.consumo)/datediff(day,@dataInicio,@dataFinal)),0) end

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 T4.ItmsGrpCod,T5.ItmsGrpnam
1 me gusta
#3