Applying fxRates in a query 1

[jpadie]

I'm having a brain fart on this and can't see the wood for the trees.

i have a table of transactions as follows

CREATE TABLE `transactions` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(11) DEFAULT NULL,
 `txtime` datetime NOT NULL,
 `amount` decimal(10,2) NOT NULL,
 `fee` decimal(10,2) NOT NULL,
 `currency` varchar(5) NOT NULL,
 PRIMARY KEY (`id`)

and another table to hold historic fxRates

CREATE TABLE `fxRates` (
 `date` date NOT NULL DEFAULT '0000-00-00',
 `fxRate` float DEFAULT NULL,
 `currency` varchar(5) NOT NULL,
 PRIMARY KEY (`date`,`currency`)
)

bear in mind i'm helping someone else out and am not the developer (although the fx come from one of my scripts). To my mind the converted amount should be stored at the time of purchase...

anyhoo....

i'm trying to write a report in a single currency. simplifying a lot this is my current state of play

SELECT
	user_id,
	CAST(tx.txtime as DATE) as 'Transaction Date',
	tx.currency as Currency,
	tx.amount as 'Base Amount',
	CASE
		tx.currency
		WHEN 'EUR'
		THEN tx.amount
		ELSE tx.amount / (
							SELECT 	fx.fxRate 
						 	FROM 	fxRates fx 
							WHERE 
									fx.date <= CAST(tx.date as DATE) 
									and 
									fx.currency=tx.currency 
							HAVING 	MAX(fx.date)
						)
	END as 'Converted Amount'
FROM 
	transactions tx
ORDER BY
	txtime asc

and what I get are null values for those currencies that are not EUR.

I know that I can replace the inline fx rates get with a join instead, but when finished the table will be a reporting by month and will sum all the transactions in a single currency. hence the desire to do the conversion inline - or am I missing something and might a join still work?

 

[r937]

caution, untested --

SELECT user_id
     , CAST(tx.txtime as DATE) as 'Transaction Date'
     , tx.currency as Currency
     , tx.amount as 'Base Amount'
     , CASE tx.currency
            WHEN 'EUR' 
            THEN tx.amount  
            ELSE tx.amount / fx.fxRate
        END AS 'Converted Amount'
  FROM transactions tx
INNER
  JOIN fxRates fx 
    ON fx.currency = tx.currency
   AND fx.date = 
       ( SELECT MAX(date)
           FROM fxRates  
          WHERE currency = tx.currency
            AND date <= CAST(tx.date as DATE) )
ORDER 
    BY tx.txtime asc

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jpadie]

thank you Rudy. I had to change to a left outer join in order to cater for the fact that the fxRates table is EUR based and thus there are never any conversion rates in their for euros. I guess I could just have added one at a rate of 1 as an alternative.

brilliant as always.

Justin