Query de ventas por dia sap b1

SAP Business One
1

Hola,

¿Cómo puedo mostrar a venta diaria incluido los vendedores que aún no facturan?

Este es mi script, y no me funciona

SELECT YEAR(T0.DocDate) Año, MONTH(T0.DocDate) Mes, T2.SlpName, SUM(T1.LineTotal)Total
FROM OINV T0
LEFT JOIN INV1 T1 ON T1.DocEntry = T0.DocEntry
LEFT JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
WHERE YEAR(T0.DocDate) = 2021 AND MONTH(T0.DocDate) = 8 AND DAY(T0.DocDate) = 30
GROUP BY YEAR(T0.DocDate), MONTH(T0.DocDate), T2.SlpName
2

Buenas, deberias probar quitando esta porcion de tu codigo a ver que te devuelve

3

Si, le quité pero, solo me muestra los vendedores que han facturado.

4

es por que estas consultando facturas, deberias hacer la consulta comenzando por OSLP y luego left con la factura y su detalle

6

Hice de esta manera, y aun asi no cargan todos

SELECT YEAR(T1.DocDate) Año, MONTH(T1.DocDate) Mes, T0.SlpName, SUM(T1.LineTotal)Total
FROM OSLP T0
LEFT JOIN INV1 T1 ON T1.SlpCode = T0.SlpCode
WHERE YEAR(T1.DocDate) = 2021 AND MONTH(T1.DocDate) = 8 
GROUP BY YEAR(T1.DocDate), MONTH(T1.DocDate), T0.SlpName
7

Deberia quedar asi

SELECT YEAR(T1.DocDate) Año, MONTH(T1.DocDate) Mes, T0.SlpName, SUM(T1.LineTotal)Total
FROM OSLP T0
LEFT JOIN INV1 T1 ON T1.SlpCode = T0.SlpCode and YEAR(T1.DocDate) = 2021 AND MONTH(T1.DocDate) = 8 
GROUP BY YEAR(T1.DocDate), MONTH(T1.DocDate), T0.SlpName
1 me gusta
8

Prueba con este código:

SELECT YEAR(T0.DocDate) Año, MONTH(T0.DocDate) Mes, day(t0.docdate) dia, T2.SlpName, SUM(T1.LineTotal)Total
FROM OINV T0
inner join INV1 T1 ON T1.DocEntry = T0.DocEntry
LEFT JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
WHERE YEAR(T0.DocDate) = 2021 AND MONTH(T0.DocDate) = 8 
GROUP BY YEAR(T0.DocDate), MONTH(T0.DocDate), day(t0.docdate), T2.SlpName
order by YEAR(T0.DocDate), MONTH(T0.DocDate), day(t0.docdate)
1 me gusta
9

Hola, hice un ajuste a la sentencia propuesta de @Dark_katt y le agrtegue la validación de ISNULL para los registros que no umplen con la condición de facturas en el mes.

SELECT ISNULL(YEAR(T1.DocDate), 2021) AS Año, ISNULL(MONTH(T1.DocDate), 6) AS Mes, T0.SlpName, ISNULL(SUM(T1.LineTotal), 0) AS Total
FROM OSLP T0
LEFT JOIN INV1 T1 ON T1.SlpCode = T0.SlpCode
WHERE ISNULL(YEAR(T1.DocDate), 2021) = 2021 AND ISNULL(MONTH(T1.DocDate), 6) = 6
GROUP BY YEAR(T1.DocDate), MONTH(T1.DocDate), T0.SlpName

Espero sirva

1 me gusta
10

Gracias por la corrección

11

Gracias por la corrección.

12

El siguiente código trae la facturación neta del día. Contempla facturas y notas crédito.

Select T0.SlpName, sum(TBL0.LineTotal) as Total from OSLP T0
Left join 
(select TB0.SlpCode, sum(TB0.LineTotal) as LineTotal from INV1 TB0 where year(TB0.DocDate) = year(getdate()) and month(TB0.DocDate) = month(getdate()) and day(TB0.DocDate) = day(getdate()) group by TB0.SlpCode 
	UNION ALL select TB1.SlpCode, sum(TB1.LineTotal*-1) from RIN1 TB1 where year(TB1.DocDate) = year(getdate()) and month(TB1.DocDate) = month(getdate()) and day(TB1.DocDate) = day(getdate()) group by TB1.SlpCode) TBL0 
ON TBL0.SlpCode = T0.SlpCode
Where T0.Active = 'Y'
group by T0.SlpName

Solo es necesario modificar el WHERE de las 2 consultas TB0 y TB1 para identificar el día para el cual uno quiere la consulta. O si prefiere una tabla con la facturación por día del mes en curso:

Select T0.SlpName, 
sum (case when day(TBL0.DocDate) = 1 then TBL0.LineTotal else 0 end) as '1', 
sum (case when day(TBL0.DocDate) = 2 then TBL0.LineTotal else 0 end) as '2',
sum (case when day(TBL0.DocDate) = 3 then TBL0.LineTotal else 0 end) as '3',
sum (case when day(TBL0.DocDate) = 4 then TBL0.LineTotal else 0 end) as '4',
sum (case when day(TBL0.DocDate) = 5 then TBL0.LineTotal else 0 end) as '5',
sum (case when day(TBL0.DocDate) = 6 then TBL0.LineTotal else 0 end) as '6',
sum (case when day(TBL0.DocDate) = 7 then TBL0.LineTotal else 0 end) as '7',
sum (case when day(TBL0.DocDate) = 8 then TBL0.LineTotal else 0 end) as '8',
sum (case when day(TBL0.DocDate) = 9 then TBL0.LineTotal else 0 end) as '9',
sum (case when day(TBL0.DocDate) = 10 then TBL0.LineTotal else 0 end) as '10',
sum (case when day(TBL0.DocDate) = 11 then TBL0.LineTotal else 0 end) as '11',
sum (case when day(TBL0.DocDate) = 12 then TBL0.LineTotal else 0 end) as '12',
sum (case when day(TBL0.DocDate) = 13 then TBL0.LineTotal else 0 end) as '13',
sum (case when day(TBL0.DocDate) = 14 then TBL0.LineTotal else 0 end) as '14',
sum (case when day(TBL0.DocDate) = 15 then TBL0.LineTotal else 0 end) as '15',
sum (case when day(TBL0.DocDate) = 16 then TBL0.LineTotal else 0 end) as '16',
sum (case when day(TBL0.DocDate) = 17 then TBL0.LineTotal else 0 end) as '17',
sum (case when day(TBL0.DocDate) = 18 then TBL0.LineTotal else 0 end) as '18',
sum (case when day(TBL0.DocDate) = 19 then TBL0.LineTotal else 0 end) as '19',
sum (case when day(TBL0.DocDate) = 20 then TBL0.LineTotal else 0 end) as '20',
sum (case when day(TBL0.DocDate) = 21 then TBL0.LineTotal else 0 end) as '21',
sum (case when day(TBL0.DocDate) = 22 then TBL0.LineTotal else 0 end) as '22',
sum (case when day(TBL0.DocDate) = 23 then TBL0.LineTotal else 0 end) as '23',
sum (case when day(TBL0.DocDate) = 24 then TBL0.LineTotal else 0 end) as '24',
sum (case when day(TBL0.DocDate) = 25 then TBL0.LineTotal else 0 end) as '25',
sum (case when day(TBL0.DocDate) = 26 then TBL0.LineTotal else 0 end) as '26',
sum (case when day(TBL0.DocDate) = 27 then TBL0.LineTotal else 0 end) as '27',
sum (case when day(TBL0.DocDate) = 28 then TBL0.LineTotal else 0 end) as '28',
sum (case when day(TBL0.DocDate) = 29 then TBL0.LineTotal else 0 end) as '29',
sum (case when day(TBL0.DocDate) = 30 then TBL0.LineTotal else 0 end) as '30',
sum (case when day(TBL0.DocDate) = 31 then TBL0.LineTotal else 0 end) as '31',
sum (isnull(TBL0.LineTotal,0)) as Total
from OSLP T0
Left join 
(select TB0.DocDate, TB0.SlpCode, sum(TB0.LineTotal) as LineTotal from INV1 TB0 where year(TB0.DocDate) = year(getdate()) and month(TB0.DocDate) = month(getdate())  group by TB0.SlpCode, TB0.DocDate 
	UNION ALL select TB1.DocDate, TB1.SlpCode, sum(TB1.LineTotal*-1) as LineTotal from RIN1 TB1 where year(TB1.DocDate) = year(getdate()) and month(TB1.DocDate) = month(getdate())  group by TB1.SlpCode, TB1.DocDate) AS TBL0 
ON TBL0.SlpCode = T0.SlpCode
Where T0.Active = 'Y'
group by T0.SlpName
1 me gusta