时间戳

首页 > 解决方案 > 特定日期的汇率

sql - 特定日期的汇率

问题描述

我正在创建下面显示的查询，该查询返回索赔文件的付款/准备金金额。这个想法是在指定的日期 exch_r."AtDate" 转换为当地货币作为储备。但是，结果返回以 EUR 为单位的金额总和，而不是以 Lei 为单位的值的总和。

例如：有 3 个欧元储备（200 欧元；9 欧元；56 欧元）和一个雷（110 LEI）储备。

返回的结果是 265 EUR * 4,8567 LEI = 1.287,0255 LEI，不包括转换后的 110 Lei。我的结果应该是 1.287,0255 + 110 LEI = 1.397,0255

select d."DAMFileNr", plata_lei
,coalesce(SUM(CASE WHEN d_rez."EID"='10' THEN d_rez."RDAM" ELSE 
           d_rez."RDAM"*exch_r."EXCValue" END),0) as rez_lei
from "DAM" as d
left join "DAMRez" as d_Rez on d."Index"=d_rez."DID"
left join lateral (
    select coalesce(sum(case when d_pay."EID"='10' then d_pay."PAYVal" else
                                d_pay."PAYVal"*exch_p."EXCValue" end),0) as plata_lei
    from "DAMPay" as d_pay
    LEFT JOIN "EXCValues" exch_p ON exch_p."AtDate"=d_pay."PAYDate" and exch_p."EID"=d_pay."EID"
    where d."Index" = d_pay."DID"
) d_pay on true
LEFT JOIN "EXCValues" exch_r ON exch_r."EID"=d_rez."EID"
where exch_r."AtDate"='2020-09-16' and d."DAMFileNr" like 'S/SC/05/2020'
group by d."DAMFileNr", plata_lei
order by d."DAMFileNr"

你能告诉我如何处理这个问题吗？谢谢

标签： sqlpostgresql

解决方案

解决了。我添加了一个新条件： where (d_rez."EID"='10' or exch_r."AtDate"='2020-09-16') and d."DAMFileNr" like 'S/SC/05/2020'

推荐阅读