Un saludos para todos,
Tengo este query para ver las ventas diarias por vendedor más el acumulado mensual de cada uno:
SELECT DISTINCT T1.[SlpCode], T1.[SlpName],
ISNULL((SELECT SUM(T.[DocTotal]-T.[Vatsum]) FROM OINV T WHERE
YEAR(T.[DocDate]) = YEAR(T0.[DocDate]) AND
MONTH(T.[DocDate]) = MONTH(T0.[DocDate]) AND
DAY(T.[DocDate]) = DAY(T0.[DocDate]) AND T.[SlpCode] = T1.[SlpCode]),0)
-ISNULL((SELECT SUM(Y.[DocTotal]-Y.[Vatsum]) FROM ORIN Y WHERE
YEAR(Y.[DocDate]) = YEAR(T0.[DocDate]) AND
MONTH(Y.[DocDate]) = MONTH(T0.[DocDate]) AND
DAY(Y.[DocDate]) = DAY(T0.[DocDate]) AND
Y.[SlpCode] = T1.[SlpCode]),0) AS 'Facturado Hoy',
ISNULL((SELECT SUM(T.[DocTotal]-T.[Vatsum]) FROM OINV T WHERE
YEAR(T.[DocDate]) = YEAR(T0.[DocDate]) AND
MONTH(T.[DocDate]) = MONTH(T0.[DocDate]) AND T.[SlpCode] = T1.[SlpCode]),0)
-ISNULL((SELECT SUM(Y.[DocTotal]-Y.[Vatsum]) FROM ORIN Y WHERE
YEAR(Y.[DocDate]) = YEAR(T0.[DocDate]) AND
MONTH(Y.[DocDate]) = MONTH(T0.[DocDate]) AND Y.[SlpCode] = T1.[SlpCode]),0) AS 'Acumulado del Mes'
FROM OINV T0
INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]
WHERE DAY(T0.[DocDate]) = DAY(GETDATE()) AND MONTH(T0.[DocDate]) = MONTH(GETDATE()) AND YEAR(T0.[DocDate]) = YEAR(GETDATE())
ORDER BY T1.[SlpCode]
el cual me genera estos resultados:
Este query lo tengo asociado a una alerta que se dispara diariamente a las 6:30pm y se envía por correo electrónico a ciertos usuarios:
El problema que tengo, es que cuando se envía el correo, se repiten las líneas por cada factura que está a nombre de x vendedor, si por ejemplo x vendedor facturó $500.000 en el día distribuidos en 5 facturas, el resultado saldrá 5 veces:
Lo cual me resulta extraño porque desde el query manager no me pasa. Por lo que recurro a su ayuda para encontrar una solución a este inconveniente, ya que este informe se debe enviar diaramente para hacer un seguimiento a las ventas de todos los asesores.
Muchas gracias.
tu query esta tomando el FROM de OINV, solo deberia tomar de OSLP, y en el WHERE colocar el filtro de empleados con ventas diferentes de cero:
Select * FROM
(
SELECT DISTINCT T1.[SlpCode], T1.[SlpName],
ISNULL((SELECT SUM(T.[DocTotal]-T.[Vatsum]) FROM OINV T WHERE
YEAR(T.[DocDate]) = YEAR(Getdate()]) AND
MONTH(T.[DocDate]) = MONTH(Getdate()) AND
DAY(T.[DocDate]) = DAY(Getdate()) AND T.[SlpCode] = T1.[SlpCode]),0)
-ISNULL((SELECT SUM(Y.[DocTotal]-Y.[Vatsum]) FROM ORIN Y WHERE
YEAR(Y.[DocDate]) = YEAR(Getdate()) AND
MONTH(Y.[DocDate]) = MONTH(Getdate()) AND
DAY(Y.[DocDate]) = DAY(Getdate()) AND
Y.[SlpCode] = T1.[SlpCode]),0) AS 'Facturado Hoy',
ISNULL((SELECT SUM(T.[DocTotal]-T.[Vatsum]) FROM OINV T WHERE
YEAR(T.[DocDate]) = YEAR(Getdate()) AND
MONTH(T.[DocDate]) = MONTH(Getdate()) AND T.[SlpCode] = T1.[SlpCode]),0)
-ISNULL((SELECT SUM(Y.[DocTotal]-Y.[Vatsum]) FROM ORIN Y WHERE
YEAR(Y.[DocDate]) = YEAR(Getdate()) AND
MONTH(Y.[DocDate]) = MONTH(Getdate()) AND Y.[SlpCode] = T1.[SlpCode]),0) AS 'Acumulado del Mes'
FROM OSLP T1
) as TR
WHERE TR.'Facturado Hoy' !=0 or TR.'Acumulado del Mes'!=0
1 me gusta
Muchas gracias, el query funciona!
Adjunto query final:
Select * FROM
(
SELECT DISTINCT T1.[SlpCode], T1.[SlpName],
ISNULL((SELECT SUM(T.[DocTotal]-T.[Vatsum]) FROM OINV T WHERE
YEAR(T.[DocDate]) = YEAR(GETDATE()) AND
MONTH(T.[DocDate]) = MONTH(GETDATE()) AND
DAY(T.[DocDate]) = DAY(GETDATE()) AND T.[SlpCode] = T1.[SlpCode]),0)
-ISNULL((SELECT SUM(Y.[DocTotal]-Y.[Vatsum]) FROM ORIN Y WHERE
YEAR(Y.[DocDate]) = YEAR(GETDATE()) AND
MONTH(Y.[DocDate]) = MONTH(GETDATE()) AND
DAY(Y.[DocDate]) = DAY(GETDATE()) AND
Y.[SlpCode] = T1.[SlpCode]),0) AS 'Facturado_Hoy',
ISNULL((SELECT SUM(T.[DocTotal]-T.[Vatsum]) FROM OINV T WHERE
YEAR(T.[DocDate]) = YEAR(GETDATE()) AND
MONTH(T.[DocDate]) = MONTH(GETDATE()) AND T.[SlpCode] = T1.[SlpCode]),0)
-ISNULL((SELECT SUM(Y.[DocTotal]-Y.[Vatsum]) FROM ORIN Y WHERE
YEAR(Y.[DocDate]) = YEAR(GETDATE()) AND
MONTH(Y.[DocDate]) = MONTH(GETDATE()) AND Y.[SlpCode] = T1.[SlpCode]),0) AS 'Acumulado_del_Mes'
FROM OSLP T1
) as TR
WHERE TR.Facturado_Hoy !=0 or TR.Acumulado_del_Mes !=0