ayuda query, Facturado y Pagos por descripcion de Producto

[Former Member]

Que tal Amigos, aprovechandome nuevamente de su buena Fe, quisiera ver si alguien puede echarme la mano, con este query, ya que me da un error (Divide by zero error encountered.) al Momento de Dividir por el MAXIMO del conteo de Lineas en el detalle de la factura, este error es el que arroja, y no he podido pasar de ahi,

ojala pudieran apoyarme, les agradezco de antemano.

DECLARE @FECHAINI DATETIME, @FECHAFIN DATETIME

SET @FechaIni = '2013-05-01'

SET @FechaFin = '2013-05-30'

SELECT distinct F1.cardcode, F1.cardname,

        ISNULL((select sum(A0.linetotal) as 'FACTURADO'

        from INV1 A0

        join OINV t1 on A0.docentry = t1.docentry

        where A0.dscription like 'PAPA %' and T1.cardcode = F1.cardcode and t1.docdate between @fechaiNI and @Fechafin

        group by t1.cardcode),0) as 'FACTURADO',

       ISNULL((select DISTINCT 'PRUEBA' = CASE

                    WHEN COUNT(A0.Linenum) > '0' THEN SUM(A0.linetotal) / MAX(A0.linenum)

                    WHEN COUNT(A0.linenum) = '1' THEN SUM(T3.DOCTOTAL) END

        from INV1 A0

        join OINV t1 on A0.docentry = t1.docentry

        join RCT2 T2 on T1.docentry = T2.docentry

        join ORCT T3 on T2.docnum = T3.docnum

        where A0.dscription like 'PAPA %' and T1.cardcode = F1.cardcode and t1.docdate between @fechaiNI and @Fechafin

        group by t1.cardcode),0) as 'PAGOS'

FROM    OCRD F1

WHERE   ISNULL((select sum(A0.linetotal) as 'FACTURADO'

              from INV1 A0

              join OINV t1 on A0.docentry = t1.docentry

              where A0.dscription like 'PAPA %' and T1.cardcode = F1.cardcode and t1.docdate between @fechaiNI and @Fechafin

              group by t1.cardcode),0) > '0'

GROUP BY f1.cardcode, f1.cardname

order by 'PAGOS' DESC

[former_member203638 · Accepted solution]

Hola Luis, intenta este, la diferencia esta en el parametro NULLIF en MAX(A0.LineNum)

DECLARE @FECHAINI DATETIME, @FECHAFIN DATETIME
SET @FechaIni = '2013-05-01'
SET @FechaFin = '2013-05-30'
SELECT distinct F1.cardcode, F1.cardname,
ISNULL((select sum(A0.linetotal) as 'FACTURADO'
from INV1 A0
join OINV t1 on A0.docentry = t1.docentry
where A0.dscription like 'PAPA %' and T1.cardcode = F1.cardcode and t1.docdate between @fechaiNI and @Fechafin
group by t1.cardcode),0) as 'FACTURADO',
ISNULL((select DISTINCT 'PRUEBA' = CASE
       WHEN COUNT(A0.Linenum) > '0' THEN SUM(A0.linetotal) / NULLIF(MAX(A0.linenum),0)
       WHEN COUNT(A0.linenum) = '1' THEN SUM(T3.DOCTOTAL) END
       from INV1 A0
       join OINV t1 on A0.docentry = t1.docentry
       join RCT2 T2 on T1.docentry = T2.docentry
       join ORCT T3 on T2.docnum = T3.docnum
       where A0.dscription like 'PAPA %' and T1.cardcode = F1.cardcode and t1.docdate between @fechaiNI and @Fechafin
       group by t1.cardcode),0) as 'PAGOS'
FROM    OCRD F1
WHERE   ISNULL((select sum(A0.linetotal) as 'FACTURADO'
                from INV1 A0
                join OINV t1 on A0.docentry = t1.docentry
                where A0.dscription like 'PAPA %' and T1.cardcode = F1.cardcode and t1.docdate between @fechaiNI and @Fechafin
                group by t1.cardcode),0) > '0'
GROUP BY f1.cardcode, f1.cardname
order by 'PAGOS' DESC

Saludos.

Alessandro.

[Former Member · Accepted solution]

Gracias Alessandro, aunque estaba mal mi razonamiento, ya pude obtener el resultado que buscaba

asi quedo mi query

DECLARE @FECHAINI DATETIME, @FECHAFIN DATETIME

SET @FechaIni = '2013-05-01'

SET @FechaFin = '2013-05-30'

SELECT distinct F1.cardcode, F1.cardname,

        ISNULL((select sum(A0.linetotal) as 'FACTURADO'

        from INV1 A0

        join OINV t1 on A0.docentry = t1.docentry

        where A0.dscription like 'PAPA %' and T1.cardcode = F1.cardcode and t1.docdate between @fechaiNI and @Fechafin

        group by t1.cardcode),0) as 'FACTURADO',

       ISNULL((select SUM(t2.sumapplied)

        from INV1 A0

        join OINV t1 on A0.docentry = t1.docentry

        join RCT2 T2 on T1.docentry = T2.docentry

        join ORCT T3 on T2.docnum = T3.docnum

        where A0.linenum = '0' AND A0.dscription like 'PAPA %' and T1.cardcode = F1.cardcode and t1.docdate between @fechaiNI and @Fechafin

        group by t1.cardcode),0) as 'PAGOS'

FROM    OCRD F1

WHERE   ISNULL((select sum(A0.linetotal) as 'FACTURADO'

              from INV1 A0

              join OINV t1 on A0.docentry = t1.docentry

              where A0.dscription like 'PAPA %' and T1.cardcode = F1.cardcode and t1.docdate between @fechaiNI and @Fechafin

              group by t1.cardcode),0) > '0'

GROUP BY f1.cardcode, f1.cardname

order by 'PAGOS' DESC

[waldo · Accepted solution]

Todo esto es SAP Business One (Español), no?

Si me confirman, hay que pedir que lo muevan al sub-espacio correspondiente.

Saludos

[former_member203638 · Accepted solution]

Si Osvaldo, es Business One.

[Former Member]

Hola Amigo

Parece muy bueno tu Query solo que a mi punto de vista no habrá faltado restar las notas de créditopara ver el monto real??

Saludos