on 03-07-2013 6:01 PM
Hola chikos
Estoy tratando de realizar un Query que me realice el mismo informe que da SAP en el Analisis de Compras pero me encontre con un pequeño problema al querer realizar una operación aritmetica en el script.
Este query me muestra el saldo total de todas las facturas de proveedor pero falta que le reste las notas de credito de los proveedores que tienen esos documentos.
Espero que alguien me pueda dar una idea para poder seguir desarrollando este query.
Mil Gracias!!!
Select T3.CardCode,T3.CardName,
SUM(T4.DocTotal-T4.VatSum+T4.WTSum)
from JDT1 T1
inner join OJDT T2
on T2.TransId=T1.TransId
inner join OCRD T3
on T3.CardCode=T1.ShortName
inner join OPCH T4
on T4.CardCode=t1.ShortName and T4.TransId=T2.TransId
left join ORPC T5
on T5.CardCode=T1.ShortName and T5.TransId=T1.TransId
where T2.RefDate>='2012-01-01' and T2.RefDate<='2012-12-31'
group by T3.CardCode,T3.CardName
Hola fabiola, Puedes empezar con este:
SELECT
T1.[SlpName],
T0.[CardCode],
T0.[CardName],
T0.[NumAtCard],
T0.[DocNum],
T0.[DocDate],
T0.[DocDueDate],
T0.[DocTotal],
T0.[PaidToDate],
(T0.[DocTotal] - T0.[PaidToDate]) Saldo,'Factura'
FROM OPCH T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.docdate between [%1] and [%2]
UNION ALL
SELECT
T1.[SlpName],
T0.[CardCode],
T0.[CardName],
T0.[NumAtCard],
T0.[DocNum],
T0.[DocDate],
T0.[DocDueDate],
T0.[DocTotal],
T0.[PaidToDate],
((T0.[DocTotal] - T0.[PaidToDate])*-1) Saldo,'NC'
FROM ORPC T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.docdate Between [%1] and [%2]
Igual puedes revisar este tema, para que tomes en consideracion algunos factores.
Saludos.
Alessandro.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola Alessandro
muchas gracias por la consulta esta bien solo que yo necesito que me agrupe por codigo de socio de negocio realice otra consulta y esta me funciono y si me trae el importe total de la factura de proveedores claro menos las notas de credito.
solo que ahora al ejecutarla en SAP me envia el siguiente error:
1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Must specify table to select from. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Acuerdo global' (OOAT) (s) could not be prepared.
Este es el query
SELECT T.CardCode,T.CardName,T0.LicTradNum,T0.AddID, SUM(T.importe) as 'Total Factura de Proveedores'
FROM
(Select a.cardcode,a.cardname,a.docdate,a.transid, SUM(a.doctotal-a.vatsum+a.wtsum) as importe
From OPCH a
group by a.CardCode,a.cardname,a.DocDate,a.transid
UNION ALL
Select a.cardcode,a.cardname,a.DocDate,a.TransId,-SUM(a.doctotal-a.vatsum+a.wtsum) as importe
From ORPC a
group by a.CardCode,a.CardName,a.DocDate,a.TransId ) T
inner join OCRD T0
on T0.CardCode=T.CardCode
inner join OJDT T1
on T1.TransId=T.TransId
--WHERE T.DocDate>='2012-01-01' and T.DocDate<='2012-12-31'
WHERE T.DocDate>='[%01]' and T.DocDate<='[%02]'
GROUP BY T.CardCode,T.cardname,T0.LicTradNum,T0.AddID
Order by 1
si lo ejecuto en sql serve sin problema me trae bien los datos
Hola Alessandro por su puesto espero que les pueda servir.
Saludos!!
SELECT T.CardCode,
T.CardName,
T0.LicTradNum,
T0.AddID,
COUNT(T.DOCNUM) as 'Factura de Proveedores',
SUM(T.importe) as 'Total Factura de Proveedores'
FROM
(Select a.cardcode,a.cardname,a.docdate,a.transid,a.docnum, SUM(a.doctotal-a.vatsum+a.wtsum) as importe
From OPCH a
group by a.CardCode,a.cardname,a.docdate,a.transid,a.docnum
UNION ALL
Select a.cardcode,a.cardname,a.DocDate,a.TransId,a.DocNum,-SUM(a.doctotal-a.vatsum+a.wtsum) as importe
From ORPC a
group by a.CardCode,a.CardName,a.docdate,a.transid,a.DocNum
)as T
inner join OCRD T0
on T0.CardCode=T.CardCode
inner join OJDT T1
on T1.TransId=T.TransId
where T1.RefDate>='[%01]' and T1.RefDate<='[%02]'
GROUP BY T.CardCode,T.cardname,T0.LicTradNum,T0.AddID
Order by 1
User | Count |
---|---|
103 | |
14 | |
10 | |
5 | |
4 | |
3 | |
3 | |
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.