on 05-08-2014 4:40 PM
Buenos días comunidad SAP
Necesito ayuda con una consulta .
Tengo que sacar la cantidad de artículos comprados por cada cliente y el total de la factura hice la siguiente consulta :
SELECT SUM(INV1.Price) as 'total vendido',
(SELECT DISTINCT OCRD.CardName FROM OCRD WHERE ocrd.CardCode = INV1.BaseCard) as 'Cliente',
INV1.BaseCard,COUNT(INV1.ItemCode) as 'Total articulos'
FROM OINV INNER JOIN INV1 on oinv.DocEntry = INV1.DocEntry WHERE inv1.BaseCard = oinv.CardCode
GROUP BY BaseCard
ORDER BY SUM(INV1.Price) DESC
en teoría los datos estan bien pero no resta las que tienen nota credito :
Pero como puedo añadir una condición para que me reste la $ suma si tienen nota crédito la factura ?
De antemano gracias.
Prueba Con esta consulta
SELECT SUM(XX.Total),XX.cliente,XX.BaseCard,sum(XX.Totalart) FROM(
SELECT SUM(INV1.Price) as 'Total',
(SELECT DISTINCT OCRD.CardName FROM OCRD WHERE ocrd.CardCode = INV1.BaseCard) as 'Cliente',
INV1.BaseCard,COUNT(INV1.ItemCode) as 'Totalart'
FROM OINV INNER JOIN INV1 on oinv.DocEntry = INV1.DocEntry WHERE inv1.BaseCard = oinv.CardCode
GROUP BY BaseCard
union all
SELECT SUM(RIN1.Price)*-1 as 'Total',
(SELECT DISTINCT OCRD.CardName FROM OCRD WHERE ocrd.CardCode = RIN1.BaseCard) as 'Cliente',
RIN1.BaseCard,COUNT(RIN1.ItemCode)*-1 as 'Totalart'
FROM ORIN INNER JOIN RIN1 on RIN1.DocEntry = RIN1.DocEntry WHERE RIN1.BaseCard = ORIN.CardCode
GROUP BY BaseCard
)XX
group by XX.cliente,XX.BaseCard
saludos,
Wuilmer Venegas
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
99 | |
11 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.