I have this query i built i'm trying to integrate that will display charge, costs and profit in both CDN$ and USD$. I want the results to always appear as 2 rows and the columns : CurrencyType, Charge currency rate, customer charge, accessorial charge, total charge, cost currency, cost amount, revenue ( Total charge - cost amount) such that the Currency type will be the "pivot" where it's always 2 rows for CDN$ and USD$.
This is the query i'm currently working with
select o1.ord_currency, --Charge Currency CASE WHEN o1.ord_currency='CAN$' THEN o1.ord_charge ELSE o1.ord_charge*x.rate END as 'TotLH', --Customer Charge CASE WHEN o1.ord_currency='CAN$' THEN o1.ord_totalcharge-o1.ord_charge ELSE o1.ord_totalcharge*x.rate-o1.ord_charge*x.rate END as 'TotAcc', --Total Accessorial charges CASE WHEN o1.ord_currency='CAN$' THEN o1.ord_totalcharge ELSE o1.ord_totalcharge*x.rate END as 'TotRev', --Total charge x.rate, --Monthly average rate based on startdate CASE WHEN p.pyd_currency='CAN$' THEN p.pyd_amount ELSE p.pyd_amount*x.rate END as 'TotalCost', (CASE WHEN o1.ord_currency='CAN$' THEN o1.ord_totalcharge ELSE o1.ord_totalcharge*x.rate END) - (CASE WHEN p.pyd_currency='CAN$' THEN p.pyd_amount ELSE p.pyd_amount*x.rate END ) 'GP', p.pyd_currency'CostCurrency', from orderheader o1 left outer join exchangerates x on convert(varchar(6), o1.ord_startdate, 112)=x.year+x.month --How Exchanges rates are linked to order level join paydetail p on o1.mov_number=p.mov_number or o1.ord_hdrnumber=p.ord_hdrnumber and p.pyt_itemcode not in (select pyt_itemcode from excluded_paytypes) where o1.ord_hdrnumber=@OrdNumber GROUP BY o1.ord_currency,o1.ord_charge,x.rate, o1.ord_totalcharge,p.pyd_currency,p.pyd_amount,o1.ord_startdate
We're on Compataiblity 80 so i can't use PIVOT; Need to figure out how to use CASE statement to get the currencies as the PIVOT point and have the charge/costs/profit calculated for each currency based on the exchange rate taken from the left outer join in the query ( x.rate)