1

Please help with a problem I'm facing to properly join a few tables in a single query.

Simplified schema is as follows:

TRANSACTION TABLE:
-----------------

TRANSACTION_DATE     AMOUNT CURRENCY
---------------- ---------- --------
22/09/13                100 EUR      
22/09/13                200 FRA      
22/09/13                200 GBP   

CURRENCY_CONVERSION TABLE:
-------------------------

FROM_CURRENCY TO_CURRENCY CONVERSION_DATE CONVERSION_RATE
------------- ----------- --------------- ---------------
GBP           USD         23/09/13                   1.61 
EUR           USD         22/09/13                   1.35

Presently, the query that works is as follows (using Sybase/SQL Server join syntax)

SELECT  
    t.transaction_date, 
    t.amount, 
    t.currency, 
    t.amount * cc.conversion_rate as amount_usd
FROM transaction t, currency_conversion cc, 
WHERE t.transaction_date *= cc.conversion_date
AND t.currency *= cc.from_currency
AND cc.to_currency = 'USD'

We face issue in above query when currency_conversion table may not have conversion rate for certain dates. In this case of missing value, business wants to resort to last latest conversion rate available in DB. For example, if EUR to USD rate is not available for today, use yesterday's rate. I try to do this as follows:

SELECT  
    t.transaction_date, 
    t.amount, 
    t.currency, 
    t.amount * cc.conversion_rate as amount_usd
FROM transaction t, currency_conversion cc, 
WHERE t.transaction_date *= 
                          (SELECT max(c1.conversion_date) FROM currency_conversion c1 
                          WHERE c1.from_currency = t.from_currency
                          AND c1.to_currency = 'USD')
AND t.currency *= cc.from_currency
AND cc.to_currency = 'USD'

This query does not works. The left join on t.transaction_date is illegal. It would work if I let go of the join but then I miss some transactions in the final result.

Please advise.

Please note that:

  • its important to achive this in a single query (two step solution with two queries won't help)
    • DB used is Sybase 12 though I think this is a generic SQL stuff
    • this is a simplified schema, in the actual query I use there are two more such conversions to USD required for different amounts
    • if possible, please suggest a solution around the existing query with minimal changes

Thanks for your time.

Twelfth
  • 7,070
  • 3
  • 26
  • 34
haps10
  • 3,496
  • 6
  • 32
  • 38
  • changed tagging - no need to put oracle and mssql2008 tags on a sybase/sql question. You will want to have the max logic on the table level in the from statement not in the where clause. Can you assume the exchange rate you want is always the most recent – Twelfth Sep 23 '13 at 16:47
  • 1
    SInce SYbase and SQl Server are basesd on the same engine, I would be very wary of using implict syntax for left joins. It gave incorrect results at times in SQl Server (BAck in SQL Server 200 when it was still allowed) sometime interpreting it as a cross join. Infact, It is a SQL antipattern to use implict joins at all and you really should stop using this proactice for all queries. – HLGEM Sep 23 '13 at 16:55
  • PArt of your problem is bad table design. If your currency conversion table had begin and end dates, it would be easier to query. – HLGEM Sep 23 '13 at 16:57

4 Answers4

1

This looks an awful lot like T-SQL Subquery Max(Date) and Joins . Does that not help?

(Sorry, I can't quite comment yet!)

Community
  • 1
  • 1
WeaponsGrade
  • 878
  • 5
  • 13
1

Would it be reasonable to make a (temporary, if neccessary) table, which is complete? That is, generate a list of all dates you might need, then fill the table with a loop of INSERT, so that you know your join will always match a partner? It's a bit of extra up-front cost, and a longer join, but it's a simpler join that should be pretty quick with appropriate indexing.

zebediah49
  • 7,467
  • 1
  • 33
  • 50
1

As far as I know Sybase also supports window functions. The best I can come up with is this: 

select transaction_date, 
       amount,
       currency,
       conversion_rate,
       conversion_date as effective_conversion_date,
       amount_usd
from (
  select t.transaction_date, 
         t.amount, 
         t.currency, 
         cc.conversion_rate,
         cc.conversion_date,
         t.amount * cc.conversion_rate as amount_usd,
         dense_rank() over (partition by transaction_date, currency order by cc.conversion_date desc) as rn,
         max(cc.conversion_date) over (partition by currency) as max_conv_date
  from transactions t
    left join currency_conversion cc 
            on t.currency = cc.from_currency
           and cc.to_currency = 'USD'
) t
where (conversion_date = max_conv_date or rn = 1)
order by transaction_date;

The trick is to leave out the transaction_date from the outer join and calculate the latest conversion_date on the fly using a window function. At the same time dense_rank() applies a value to the "latest" conversion date. That will return multiple rows for a single transaction if there are several currency_conversions for that currency.

Those unwanted rows are then filtered out by only selecting those where either the transaction_date equals the latest conversion date or the one with the latest conversion date.

BUT: I don't think this is very efficient though. If your tables are really big (which is to be expected I guess) this might not perform very well.

Here is a little SQL Fiddle example: http://sqlfiddle.com/#!12/63ef4/1
(it is using Postgres and ANSI SQL but I think the query should run on Sybase as well)

1

The subquery is not doing a join between the two tables, it's just selecting a date... which is not the action you really want.

If I understand it correctly, you want to select a conversion rate for the record with the most recent date and then use that in your multiplication.

SELECT  
    t.transaction_date, 
    t.amount, 
    t.currency, 
    t.amount * (SELECT cc.conversion_rate FROM currency_conversion cc
                    WHERE cc.from_currency = t.from_currency
                    AND cc.to_currency = 'USD'
                    AND cc.conversion_date = (SELECT max(c1.conversion_date) FROM
                                                  FROM currency_conversion c1
                                                  WHERE c1.conversion_date <=
                                                        t.transaction_date)) as amount_usd
    FROM transaction t

If your currency_conversion table had start and end dates, you could get rid of the c1 subquery and just focus the cc subquery on dates within the range and <= the transaction_date, which would simplify things.

Everett Warren
  • 61
  • 3