Hey all,
I am trying to join an exchange rate table in my code to the order lines table so I can convert the currency to USD in all my reports. The issue is for order lines that are already in USD, on the exchange rates table there is no conversion from USD to USD so my join isn't bringing in all the order lines. How can I make this join work so that anytime there is an order line in USD currency, it doesn't try to find a match in the exchange rates table but still shows the order lines in USD currency in my output?
select
ol.account_name
,a."segment-2" as Cost_Center
,dgl."name"
,bm."budget owner"
,oh.requested_by as PO_Requester
,oh."po-number"
,ol."line-num" as PO_Line
,oh.requisition_header_id
,cast(rh."submitted-at" as Date) as req_submitted_date
,cast(oh."created-at" as Date) as order_date
,cast(ol.start_date as Date)
,cast(ol.end_date as Date)
,ol.supplier_name
,oh.status
,ol.description as Item_Description
,ol.quantity
,ol.total as Line_Total
,ol.currency_code
,ol.received as Received_Amt_Qty
,ol.invoiced as Invoiced_Amt_Qty
,er.rate as Exchange_Rate
,round(case when oh.currency_code = 'USD' then ol.total::FLOAT else ol.total::FLOAT * er.rate::FLOAT end,2) as Converted_Total
,ol."savings-pct"
,round(case when ol.currency_code = 'USD' then ol.total::FLOAT / ('1' - (ol."savings-pct"::FLOAT / '100')) * (ol."savings-pct"::FLOAT / '100') else (ol.total::Float * er.rate::Float) / ('1' - (ol."savings-pct"::FLOAT / '100')) * ol."savings-pct"::FLOAT / '100' end, 0) as Savings_Total
FROM stg_coupa.order_lines AS ol
join stg_coupa.accounts AS a
on ol.account_name = a."name"
join stg_coupa.budget_mapping as bm
on bm."cost center #" = a."segment-2"
join stg_coupa.purchase_orders as oh
on ol.po_id = oh.id
join stg_coupa.requisitions as rh
on rh.id = oh.requisition_header_id
join stg_coupa.exchange_rates er
on cast(ol."created-at" as Date) + ol.currency_code + 'USD' = cast(er."rate-date" as Date) + er.from_currency + er.to_currency
join stg_coupa.dept_gl_lookups dgl
on bm."cost center #" = dgl."external ref num"