Hey all,
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)