Help with CASE instead of PIVOT

Transact-SQL
1

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)

2

Please provide:

  • Table descriptions of all tables involved, in the form of "create table" statement
  • Sample data of all tables involved, in the form of "insert into" statement
  • Expected output from the sample data you provide

You use left outer join on exchangerates table, so you expect there is no changes to the rate in this month. Also you don't specify the currency when joining the exchangerates table, which means you only have one currency in this table - am I correct this far?

I would have expected an exchangerate table to contain the currencycode, exchangerate and a date (possibly also time) indicating when this rate takes effect. I would also expect the query to look up the rate matching on this particular order, no matter when the rate was effectuated (shouldn't have to be same year/month).

Please elaborate on how it should be done in your case.

ps.: Compatibility mode 80/MSSQL 2000 is way do for an upgrade!

3

-The orderheader table contains all order related data such as customer name, total charges, shipment value, sales/booker IDs and sales/book offices as well as the customer currency type ( Either USD$ or CDN$) ( Revenue)
-Paydetails is all cost information, shipper currency type ( The difference between revenue and costs is our profit margin)
-The exchange rate table contains the month, year, rate and yearmonth which is updated daily based on new rates entered every monring. We use more daily rates on our more granular reporting but for this purpose we're only using the rolling monthly average.
The left outer join was taken from an existing query, but there is always a monthly average rate entered.I edited the query in my main post since it works the same with a normal join.

INSERT INTO ORDERHEADER (ord_currency, ord_charge, ord_totalcharge,ord_startdate)
VALUES ('CDN$','1000','1500','01/01/2016')

INSERT INTO PAYDETAILS (pyd_currency, pyd_amount, pyt_itemcode)
VALUES ('USD$','1000','ShipCost')

INSERT INTO Exchange_Rates(Year,month,rate,yearmonth)
VALUES(2016,01,1.30,201601)

Output:
Currency TotalRevenue TotalCost GP
CDN$ 1500 1300 200
USD$ 1153.85 1000 153.85

So the output should show the revenue, cost and GP in both USD$ and CDN$ where the rate is applied both ways ( CDN$ will times the rate by either the revenue or cost if it's US$ and the GP is just the difference. The USD$ will divide the amount by the rate to switch it to US$ if the amount is in CDN$.

There will only ever be more than 1 currency type for the Costs ( I.E we pay be paying different shippers in both USD$ and CDN$ on the same order), so for each amount we want to apply the exchange rate based on the currency type, sum them together and display the total cost in both CDN$ on the first row and USD$ on the second row. I hope this covers everything i missed.

Also, we can't upgrade compatibility cuz our database is the backend to a 3rd party application...We do have a datawarehouse with the latest verison, but we don't have enough bandwidth to query those tables for this purpose since this query is displayed within the 3rd party app and can/will be run hundreds if not thousands of times a day for every single order looked up in the application.

4

I'm not sure if this will work for you, as out have left out many fields in your sample data, so you'll probably have to tweak it to fit your needs. But I get the same output as you requested:

select cur.currency
      ,round(o1.ord_totalcharge
            /case
                when cur.currency=o1.ord_currency
                then 1
                else x.rate
             end
            ,2
            )
       as totalrevenue
      ,round(o1.ord_charge
            *case
                when cur.currency=o1.ord_currency
                then x.rate
                else 1
             end
            ,2
            )
       as totalcost
      ,round(o1.ord_totalcharge
            /case
                when cur.currency=o1.ord_currency
                then 1
                else x.rate
             end
            ,2
            )
      -round(o1.ord_charge
            *case
                when cur.currency=o1.ord_currency
                then x.rate
                else 1
             end
            ,2
            )
       as gp
  from orderheader as o1
       left outer join exchange_rates as x
                    on x.yearmonth=convert(varchar(6),o1.ord_startdate,112)
       cross apply (select 'CDN$' as currency
                    union all
                    select 'USD$'
                   ) as cur
;

By the way, I don't see where paydetails and excluded_paytypes tables fits in

5

PAydetails is where the values for costs are stored and it's joined to Orderheader on the order# ( In this case it's called mov_number) which isn't part of the result output. The excluded_paytypes is just a list a unique IDs for paytypes we don't want to include in the costs. I'm going to try this out and update in a bit.

Thank you for taking the time to look into this!!