Query ventas contado restando notas credito

Nelson183 · 2 Marzo, 2021 13:19

Buenos días amigos,

Solicito su colaboración en la elaboración de un Query que me muestre el total de las ventas contado pero restando las notas crédito, esas no deben estar en el total de las ventas. Tengo un código que me compartieron pero es por facturas y necesito es un general de las ventas contado y que me reste las notas credito en general

Este es el codigo de facturas y ventas general, la idea es tener un total general ventas contado sin las notas credito

SELECT T90."ItemCode", T90."Dscription", SUM(T90."Quantity"), SUM(T90."LineTotal")
FROM 
(
	SELECT T1."ItemCode", T1."Dscription", T1."Quantity", T1."LineTotal" 
	FROM OINV T0  
	INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" 
	WHERE T0."CANCELED" ='N'

	UNION ALL

	SELECT T1."ItemCode", T1."Dscription", T1."Quantity" * -1, T1."LineTotal" * -1
	FROM ORIN T0  
	INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry" 
	WHERE T0."CANCELED" ='N'
) T90
GROUP BY T90."ItemCode", T90."Dscription"

Agradecería de su ayuda

cdeleon · 2 Marzo, 2021 17:17

Hola en tu SELECT correspondiente a las facturas deberás checar que el campo GroupNum, que es el código de las condiciones de pago, sea contado

SELECT T1."ItemCode", T1."Dscription", T1."Quantity", T1."LineTotal" 
	FROM OINV T0  
	INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" 
	WHERE T0."CANCELED" ='N' and T0.GroupNum= "Código de tu condición de pago contado"

El de las notas de crédito esta correcto.

Nelson183 · 2 Marzo, 2021 20:20

Hola @cdeleon eniendo tu punto con respecto a pago de contado, pero como podria modificar el codigo para que me traiga el total de todas esas facturas no una por una como la tengo en este codigo

cdeleon · 2 Marzo, 2021 22:27

OK @Nelson183 en ese caso tienes que alterar la ultima sentencia, el group by, ahora esta agrupando por articulo y descripción, tendrías que modificar dicha sentencia para que te agrupe por periodo o el criterio que tu prefieras, un mes especifico, un año especifico, cliente, etc…

En el Select tendrías que quitar el itemcode y la descripción ya que no tendrían razón de estar ahí, y poner en su lugar el criterio por el cual estas agrupando…y dejar las sumas de importe y articulos.

Nelson183 · 2 Marzo, 2021 22:31

@cdeleon que pena me podria mostrar mas o menos como queda el codigo es que apenas estoy empezando en esto y no se mucho del tema

cdeleon · 2 Marzo, 2021 23:32

Ok @Nelson183 el código quedaría algo como esto…

Te agruparía por año el monto total de la venta, si quieres que funcione la parte de solo contado tienes que quitar los 2 guiones medios – y sustituir por el código que tenga dicha forma de pago en tu sistema, en el mío era 14

Si no los quitas te dará la sumatoria de todas las ventas, sean de contado o a crédito.

SELECT  T1.Year, SUM(T1.Total) AS Total
FROM
     (SELECT year(t0.docdate) AS Year, sum(T0.[DocTotal]) AS Total 
      FROM OINV T0 
      WHERE T0.[CANCELED] <>'Y' and  T0.[DocDate] between [%0] and [%1] 
      -- and t0.GroupNum=14
      GROUP BY year(t0.docdate)

      union all 

      SELECT year(t0.docdate) AS YEAR, sum(T0.[DocTotal]*-1) AS TOTAL  
      FROM ORIN T0 
      WHERE T0.[CANCELED] <>'Y' and  T0.[DocDate] between [%0] and [%1] 
      GROUP BY year(t0.docdate)) t1 

group by year

Nelson183 · 3 Marzo, 2021 12:57

Hola @cdeleon no me funciono el codigo no se si es por lo que estoy en SAP B1 me ayudas

cdeleon · 3 Marzo, 2021 16:29

Perdón @Nelson183 pero pues ya no puedo hacer mas, el código si lo pegas en un query, del query manager funciona perfecto, lo probé en 2 entornos diferentes

Si lo pruebas en SQL server te funcionara igual solo sustituye los parametros [%0] y [%1] por constantes de fechas.

Nelson183 · 3 Marzo, 2021 18:46

Hola @cdeleon entiendo lo que me dices pero me podrias confirmar que pena si este codigo como lo tengo me resta las notas credito?

SELECT T1."DocNum", T1."CardCode", T1."DocDate", T1."DocTotal"
FROM 
(
          SELECT T0."DocNum", T0."CardCode", T0."DocDate", T0."DocTotal"
          FROM OINV T0
          WHERE T0."CANCELED" = 'N' AND T0."CardCode" LIKE '%%VC%%'

          UNION ALL

          SELECT T0."DocNum", T0."CardCode", T0."DocDate", T0."DocTotal" *-1
          FROM ORIN T0  
          INNER JOIN OINV T1 ON T0."AnnInvDecR" = T1."DocEntry"
          WHERE T0."CANCELED" = 'N' AND T0."CardCode" LIKE '%%VC%%'
)T1

GROUP BY T1."DocNum", T1."CardCode", T1."DocDate", T1."DocTotal"

Furilo · 5 Marzo, 2021 20:27

Prueba con este Query nada mas es que te lo da solo por Año y mes.

/* SELECT FROM [dbo].[OINV] T1 */                          
  DECLARE @fechainicio AS DATE                            
  /* WHERE */                                                
  SET @fechainicio = /* T1.DocDate */'[%1]'

/* SELECT FROM [dbo].[OINV] T2 */                          
  DECLARE @fechafin AS DATE                            
  /* WHERE */                                                
  SET @fechafin = /* T2.DocDate */'[%2]'

;WITH TR as
(
select * from
(
select year(T0.DocDate) as 'Año', Month(T0.DocDate) as 'Mes', (T0.DocTotal) as 'TotVta'
FROM OINV T0 
 WHERE T0.CANCELED = 'N'  and T0.DocDate between @fechainicio and @fechafin
Union all
select year(T1.DocDate) as 'Año', Month(T1.DocDate) as 'Mes', ((T1.DocTotal)*-1) as 'TotVta'
FROM ORIN T1 
WHERE T1.CANCELED = 'N'   and T1.DocDate between @fechainicio and @fechafin 
) as TP
PIVOT (SUM(TP.TotVta) FOR TP.Mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pvrt

) 
select TR.Año, 
ISNULL([1],0) as 'ENE', ISNULL([2],0) as 'FEB', ISNULL([3],0) as 'MAR', ISNULL([4],0) as 'ABR',
ISNULL([5],0) as 'MAY', ISNULL([6],0) as 'JUN', ISNULL([7],0) as 'JUL', ISNULL([8],0) as 'AGO',
ISNULL([9],0) as 'SEP', ISNULL([10],0) as 'OCT', ISNULL([11],0) as 'NOV', ISNULL([12],0) as 'DIC',
(ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)+ISNULL([5],0)+ISNULL([6],0)+
ISNULL([7],0)+ISNULL([8],0)+ISNULL([9],0)+ISNULL([10],0)+ISNULL([11],0)+ISNULL([12],0)) as 'Total'

from TR
ORDER BY TR.Año

Ares17000 · 5 Abril, 2021 17:43

Hola Nelson…
Veo que en tu perfil colocaste que eres del área de sistemas y soporte. Te recomiendo AMPLIAMENTE que te lances unos días a ver videos tutoriales de bases de datos y de Querys en SQL. esto te ayudara a entender mejor que es lo que estás haciendo y pidiendo.

es una recomendación, ya que el query lo tienes ya realizado, solo te falta primero que tu entiendas que es lo que te está dando y que necesitas, pero para eso debes entender primero que es una tabla, que es un campo, que es un dato, luego como ligar tablas (join’s) y como funcionan los GroupBy y otros operadores…

Cuando entiendas esto, será mucho más sencillo para ti estos temas de querys, que veo que varias de tus consultas solo solicitas los querys ya funcionando, pero no un pequeño esfuerzo en entender o aprender “¿porqué funciona?”

IKASHIMI · 6 Abril, 2021 16:59

Tus NC estan ligadas fielmente a la factura? si es así con las transacciones aplicadas puedes usar los docentrys y baseentry de la FC Y NC para que hagan match y puedas descontarlas. ejemplo:

SELECT DISTINCT T0."CardCode",T0."CardName", T0."DocNum",T0."DocDate",T0."DocDueDate",T0."DiscSum",T0."DocTotal",T0."PaidToDate",T0."VatSum",T1."ReconSum",T1."SumMthCurr",
CASE WHEN T1."ReconNum" <> 0 THEN (SELECT T2."DocNum" FROM ORIN T2 LEFT JOIN ITR1 T3 ON T2."DocEntry"= T3."SrcObjAbs" WHERE T3."SrcObjTyp" =14 AND  T2."CANCELED"='N' AND T3."ReconNum"=T1."ReconNum" ) ELSE '0' END AS "N/C",
CASE WHEN T1."ReconNum" <> 0 THEN (SELECT T2."DocTotal" 
FROM ORIN T2 LEFT JOIN ITR1 T3 ON T2."DocEntry"= T3."SrcObjAbs" WHERE T3."SrcObjTyp" =14 AND  T2."CANCELED"='N' AND T3."ReconNum"=T1."ReconNum" ) ELSE '0' END AS "Total N/C",
T1."ReconNum",  T1."SrcObjAbs", T1."SrcObjTyp" 

FROM OINV T0 

LEFT JOIN ITR1 T1 ON T0."DocEntry"= T1."SrcObjAbs"  and  T1."IsCredit" <>'C'

WHERE T0."DocDate">={?01.-FechaIni}and T0."DocDate" <={?02.-FechaFin} AND T0."CANCELED"='N'


ORDER BY 1

En mi caso la NC no tiene relación alguna por eso es mas complicado pero al final la relación la lleva en la reconciliación y de ahí se hacen los movimientos necesarios.

Furilo · 11 Mayo, 2021 15:57

IKASSHIMI,
Probe el Query, pero me trae las Notas de Credito de otro Socio de Negocio.

IKASHIMI · 11 Mayo, 2021 17:58

No esta filtrado por que cliente, si necesitas el parámetro agrega en el where este parámetro T0.“CardCode”=[%2] me imagino sustituiste los de fecha por T0.“DocDate”>= [%0] and T0.“DocDate”<= [%1]

Furilo · 12 Mayo, 2021 21:27

IKASHIMI. No es el Filtro, lo que quiero decir es que el Query me muestra en algunos casos notas de credito que no corresponden al Socio de negocios. que se muestra en el Query.