Hi!
I'm trying to create a query that shows the Currency for each operation by day, in this case are the payments, the operations. But the problem is that my query get all the payments when the paymeny is by local currency and foreign currency, i mean when is dollars and when is pesos, so even when the payments are in pesos whe need to get the currency of the dollar at these day, so my query get the currency from the table of the currencies when is in pesos and when is in dollars it get the currency from the payments table, so this is my query.
/*
Reporte de Pagos
*/
/SELECT FROM OINV T1/
DECLARE @FECHA AS DATETIME
/* WHERE */
SET @FECHA = /* T1.DocDate */ [%0]
/SELECT FROM OINV T2/
DECLARE @FECHA2 AS DATETIME
/* WHERE */
SET @FECHA2 = /* T2.DocDate */ [%1]
SELECT DISTINCT T0.[DocNum],T1.[DocEntry],T2.[NumAtCard],T0.[CheckAcct],T0.[DocType],T0.[Canceled],T0.[CardName],T0.[DocDate],
CASE
WHEN T0.[DocCurr] = 'USD' THEN T0.[DocTotalFC]
WHEN T0.[DocCurr] = 'MXP' THEN T0.[DocTotal]
END AS 'Total'
,
CASE
WHEN T0.[DocRate] = 0 THEN (SELECT CAST(T3.[Rate] AS float) FROM ORTT T3 WHERE T3.[RateDate] = T0.[DocDate])
WHEN T0.[DocRate] <> 0 THEN CAST(T0.[DocRate] AS float)
END AS 'T.C.'
,T0.[DocCurr],
(SELECT T4.[SlpName] FROM OSLP T4 WHERE T4.[SlpCode] = T2.[SlpCode]) AS 'Vendedor'
FROM ORCT T0
INNER JOIN RCT2 T1 ON T0.DocNum = T1.DocNum
INNER JOIN OINV T2 ON T1.[DocEntry] = T2.[DocEntry]
WHERE T0.[DocDate] >= @FECHA
AND T0.[DocDate] <= @FECHA2
The real problem is that with this, the query just bring me 2 decimals in the currency and i need at least 4 decimal, i don't know what happen because if i run the query in the SQL Management Studio the decimals are correct it brings me more than 4 decimals, but when i run the same query inside SAP Query Manager it brings me just 2 decimals.
How can i fix that?
Hope you help me.
Thanks in advance
Regards!
Xavier