Consulta ventas con error

[diego1986]

BUEN DIA

estimados el presente es para consultarles un error que tengo con una consulta es que la ejecuto y para el mes de noviembre y octubre si me sale reporte pero cuando intento generar el de enero 2020 o de enero a noviembre me sale el error de que hay una division por cero pero no la detecto

gracias

pdt esta consulta es para validar ventas de clientes o poder hacer presupuesto para el otro año identificar estacionalidades

/*SELECT FROM OFPR P0*/ 
DECLARE FECHAINI DATETIME;
FECHAINI = /*WHERE P0."F_RefDate" */ '[%0]';

/*SELECT FROM OFPR P0*/ 
DECLARE FECHAFIN DATETIME;
FECHAFIN = /*WHERE P0."T_RefDate" */ '[%1]';

SELECT 
	YEAR(Tz."DocDate") "AÑO", MONTH(Tz."DocDate") "MES", Tz."SlpName" "VENDEDOR", 
	Tz."CardCode" "CODIGO CLIENTE", Tz."CardName" "NOMBRE CLIENTE", Tz."CityB" "CIUDAD", 
	Tz."ItmsGrpNam" "GRUPO ARTICULOS", Tz."Dscription" "PRODUCTO", SUM(Tz."Quantity") "CANTIDAD", 
	AVG(Tz."LineTotal"/Tz."Quantity") "PROMEDIO PRECIO VENTA LOCAL", SUM(Tz."LineTotal") "TOTAL LOCAL", 
	AVG(Tz."TotalSumSy"/Tz."Quantity") "PROMEDIO PRECIO VENTA USD", SUM(Tz."TotalSumSy") "TOTAL USD", 
	AVG(Tz."GrssProfit"/Tz."LineTotal"*100) "PROMEDIO MARGEN" 
FROM (
	SELECT
		T1."DocEntry", T0."DocDate", T4."SlpName", 
		T0."CardCode", T0."CardName", T5."CityB", 
		T3."ItmsGrpNam", T1."Dscription", T1."Quantity", 
		CASE WHEN T0."DiscSum" = 0 THEN T1."LineTotal"
			ELSE (T1."LineTotal" - (T1."LineTotal" * (T0."DiscSum" / (T0."DocTotal" + T0."WTSum" - T0."VatSum" - T0."RoundDif" - T0."TotalExpns" + T0."DpmAmnt" + T0."DiscSum")))) 
			END "LineTotal",
		CASE WHEN T0."DiscSumSy" =0 THEN T1."TotalSumSy"
			ELSE (T1."TotalSumSy" - (T1."TotalSumSy" * (T0."DiscSumSy" / (T0."DocTotalSy" + T0."WTSumSC" - T0."VatSumSy" - T0."RoundDifSy" - T0."TotalExpSC" + T0."DpmAmntSC" + T0."DiscSumSy")))) 
			END "TotalSumSy", 
		T1."GrssProfit" 
	FROM OINV T0  
		INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" 
		INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode" 
		INNER JOIN OITB T3 ON T2."ItmsGrpCod" = T3."ItmsGrpCod" 
		INNER JOIN OSLP T4 ON T0."SlpCode" = T4."SlpCode" 
		LEFT JOIN INV12 T5 ON T0."DocEntry" = T5."DocEntry" 
	WHERE T0."DocDate" >=FECHAINI AND  T0."DocDate" <=FECHAFIN 

	UNION ALL

	SELECT
		T1."DocEntry", T0."DocDate", T4."SlpName", 
		T0."CardCode", T0."CardName", T5."CityB", 
		T3."ItmsGrpNam", T1."Dscription", -T1."Quantity", 
		CASE WHEN T0."DiscSum" = 0 THEN -T1."LineTotal"
			ELSE -(T1."LineTotal" - (T1."LineTotal" * (T0."DiscSum" / (T0."DocTotal" + T0."WTSum" - T0."VatSum" - T0."RoundDif" - T0."TotalExpns" + T0."DpmAmnt" + T0."DiscSum")))) 
			END "LineTotal",
		CASE WHEN T0."DiscSumSy" =0 THEN T1."TotalSumSy"
			ELSE -(T1."TotalSumSy" - (T1."TotalSumSy" * (T0."DiscSumSy" / (T0."DocTotalSy" + T0."WTSumSC" - T0."VatSumSy" - T0."RoundDifSy" - T0."TotalExpSC" + T0."DpmAmntSC" + T0."DiscSumSy")))) 
			END -"TotalSumSy", 
		-T1."GrssProfit" 
	FROM ORIN T0  
		INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry" 
		INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode" 
		INNER JOIN OITB T3 ON T2."ItmsGrpCod" = T3."ItmsGrpCod" 
		INNER JOIN OSLP T4 ON T0."SlpCode" = T4."SlpCode" 
		LEFT JOIN INV12 T5 ON T0."DocEntry" = T5."DocEntry" 
	WHERE T0."DocDate" >=FECHAINI AND  T0."DocDate" <=FECHAFIN) Tz

GROUP BY YEAR(Tz."DocDate"), MONTH(Tz."DocDate"), Tz."SlpName", 
	Tz."CardCode", Tz."CardName", Tz."CityB", 
	Tz."ItmsGrpNam", Tz."Dscription"
ORDER BY 1,2,3,4,7,8;

[Ares17000]

Hola buenas tardes…

No veo que estés haciendo separación de Facturas de artículos de las facturas de Servicio. Las facturas de servicio tienen un LineTotal, pero no tienen un Quantity y por eso te está arrojando el error de dividir por 0.

Tz.“LineTotal”/Tz.“Quantity” aquí
Tz.“TotalSumSy”/Tz.“Quantity” aquí principalmente

o separas y que no te traiga las facturas de Servicio (OINV.Doctype <> ‘S’) o haces un case, donde si Quantity = 0 entonces 1, sino Quantity

[diego1986]

muchas gracias ares por tu respuesta, pero era poner un caso en el margen cuando el precio de articulo es 0

asi quedo la consulta

/*SELECT FROM OFPR P0*/ 
DECLARE FECHAINI DATETIME;
FECHAINI = /*WHERE P0."F_RefDate" */ '[%0]';

/*SELECT FROM OFPR P0*/ 
DECLARE FECHAFIN DATETIME;
FECHAFIN = /*WHERE P0."T_RefDate" */ '[%1]';

SELECT 
	YEAR(Tz."DocDate") "AÑO", MONTH(Tz."DocDate") "MES", Tz."SlpName" "VENDEDOR", 
	Tz."CardCode" "CODIGO CLIENTE", Tz."CardName" "NOMBRE CLIENTE", Tz."CityB" "CIUDAD", 
	Tz."ItmsGrpNam" "GRUPO ARTICULOS", Tz."Dscription" "PRODUCTO", SUM(Tz."Quantity") "CANTIDAD", 
	AVG(Tz."LineTotal"/Tz."Quantity") "PROMEDIO PRECIO VENTA LOCAL", SUM(Tz."LineTotal") "TOTAL LOCAL", 
	AVG(Tz."TotalSumSy"/Tz."Quantity") "PROMEDIO PRECIO VENTA USD", SUM(Tz."TotalSumSy") "TOTAL USD", 
	AVG(CASE WHEN Tz."LineTotal" = 0 THEN NULL ELSE Tz."GrssProfit"/Tz."LineTotal"*100 END) "PROMEDIO MARGEN" 
FROM (
	SELECT
		T1."DocEntry", T0."DocDate", T4."SlpName", 
		T0."CardCode", T0."CardName", T5."CityB", 
		T3."ItmsGrpNam", T1."Dscription", CASE WHEN T0."DocType" = 'I' THEN T1."Quantity" ELSE 1 END "Quantity", 
		CASE WHEN T0."DiscSum" = 0 THEN T1."LineTotal"
			ELSE (T1."LineTotal" - (T1."LineTotal" * (T0."DiscSum" / (T0."DocTotal" + T0."WTSum" - T0."VatSum" - T0."RoundDif" - T0."TotalExpns" + T0."DpmAmnt" + T0."DiscSum")))) 
			END "LineTotal",
		CASE WHEN T0."DiscSumSy" =0 THEN T1."TotalSumSy"
			ELSE (T1."TotalSumSy" - (T1."TotalSumSy" * (T0."DiscSumSy" / (T0."DocTotalSy" + T0."WTSumSC" - T0."VatSumSy" - T0."RoundDifSy" - T0."TotalExpSC" + T0."DpmAmntSC" + T0."DiscSumSy")))) 
			END "TotalSumSy", 
		T1."GrssProfit" 
	FROM OINV T0  
		INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" 
		INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode" 
		INNER JOIN OITB T3 ON T2."ItmsGrpCod" = T3."ItmsGrpCod" 
		INNER JOIN OSLP T4 ON T0."SlpCode" = T4."SlpCode" 
		LEFT JOIN INV12 T5 ON T0."DocEntry" = T5."DocEntry" 
	WHERE T0."DocDate" >=FECHAINI AND  T0."DocDate" <=FECHAFIN 

	UNION ALL

	SELECT
		T1."DocEntry", T0."DocDate", T4."SlpName", 
		T0."CardCode", T0."CardName", T5."CityB", 
		T3."ItmsGrpNam", T1."Dscription", CASE WHEN T0."DocType" = 'I' THEN -T1."Quantity" ELSE -1 END "Quantity", 
		CASE WHEN T0."DiscSum" = 0 THEN -T1."LineTotal"
			ELSE -(T1."LineTotal" - (T1."LineTotal" * (T0."DiscSum" / (T0."DocTotal" + T0."WTSum" - T0."VatSum" - T0."RoundDif" - T0."TotalExpns" + T0."DpmAmnt" + T0."DiscSum")))) 
			END "LineTotal",
		CASE WHEN T0."DiscSumSy" =0 THEN T1."TotalSumSy"
			ELSE -(T1."TotalSumSy" - (T1."TotalSumSy" * (T0."DiscSumSy" / (T0."DocTotalSy" + T0."WTSumSC" - T0."VatSumSy" - T0."RoundDifSy" - T0."TotalExpSC" + T0."DpmAmntSC" + T0."DiscSumSy")))) 
			END -"TotalSumSy", 
		-T1."GrssProfit" 
	FROM ORIN T0  
		INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry" 
		INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode" 
		INNER JOIN OITB T3 ON T2."ItmsGrpCod" = T3."ItmsGrpCod" 
		INNER JOIN OSLP T4 ON T0."SlpCode" = T4."SlpCode" 
		LEFT JOIN INV12 T5 ON T0."DocEntry" = T5."DocEntry" 
	WHERE T0."DocDate" >=FECHAINI AND  T0."DocDate" <=FECHAFIN
	) Tz

GROUP BY YEAR(Tz."DocDate"), MONTH(Tz."DocDate"), Tz."SlpName", 
	Tz."CardCode", Tz."CardName", Tz."CityB", 
	Tz."ItmsGrpNam", Tz."Dscription"
ORDER BY 1,2,3,4,7,8

;