7 Replies - 253 Views - Last Post: 16 August 2017 - 06:11 AM

#1 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4128
  • View blog
  • Posts: 13,020
  • Joined: 08-June 10

Subquery/Join for matching exchange rate

Posted 14 August 2017 - 08:13 AM

Hi,

tl;dr I'm trying to get the USD-EUR exchange rate for each day.

background: I have an invoice table with prices in EUR or USD. For ease of use I want to display for all invoices in USD the respective amount in EUR. For that I have a table with all EUR-USD exchange rates from the ECB. For all working days (Mo-Fr) this poses no problem (that's a simple left join on the PaidAt column). For week-ends and holidays this is a bit less trivial (get the last day before the paid date, which can be a couple of days, though). However, I want to have both the exchange rate and the converted date in the query. I tried that with subqueries in the SELECT clause, but it feels quite slow in PMA and looks clunky in the query (SELECT USD FROM ExchangeRate WHERE Date <= ? ORDER BY Date DESC LIMIT 1).

So my question is if there is a possibility to use a sufficiently fast subquery-join I can't figure out right now?

CREATE TABLE `Invoice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Price` decimal(10,2) unsigned NOT NULL,
  `PriceSymbol` char(3) NOT NULL DEFAULT 'EUR',
  `PaidAt` date,
  -- ...
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci



CREATE TABLE `ExchangeRate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Date` date NOT NULL,
  `USD` decimal(5,4) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Date` (`Date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8



Is This A Good Question/Topic? 0
  • +

Replies To: Subquery/Join for matching exchange rate

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13400
  • View blog
  • Posts: 53,477
  • Joined: 12-June 08

Re: Subquery/Join for matching exchange rate

Posted 14 August 2017 - 11:07 AM

You want the specific USD conversion amount on a specific date from the ExchangeRate? As in you want to join up on the date for both tables to get that conversion value?

cre ate table #invoice (id int, invoice_date datetime, invoicevalue int)
cre ate table #exchange (id int, exchangedate datetime, val int)

ins ert into #invoice(id, invoice_date, invoicevalue) values (1, '08/01/2017', 10)
ins ert into #invoice(id, invoice_date, invoicevalue) values (2, '07/01/2017', 4)
ins ert into #invoice(id, invoice_date, invoicevalue) values (3, '06/01/2017', 13)

ins ert into #exchange values (1, '08/01/2017', 3)
ins ert into #exchange values (1, '07/01/2017', 21)

--select *  from #invoice
--select *  from #exchange

select a.id, a.invoice_date, invoicevalue, invoicevalue * val
from #invoice a
left join #exchange b on a.invoice_date = b.exchangedate

dr op table #invoice 
dr op table #exchange


id          invoice_date            invoicevalue 
----------- ----------------------- ------------ -----------
1           2017-08-01 00:00:00.000 10           30
2           2017-07-01 00:00:00.000 4            84
3           2017-06-01 00:00:00.000 13           NULL

Was This Post Helpful? 0
  • +
  • -

#3 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4128
  • View blog
  • Posts: 13,020
  • Joined: 08-June 10

Re: Subquery/Join for matching exchange rate

Posted 14 August 2017 - 11:37 PM

Quote

You want the specific USD conversion amount on a specific date from the ExchangeRate?

I want the price for each invoice in USD converted to EUR using the current official exchange rate (which in the best case is from the same day, or a couple of days before in case of a week-end) respective to each individual invoice.

As you can see in your example--since Aug 6th is a sunday--there is no official exchange rate, so I would have to use the last official one for that, being friday Aug 4th.

This post has been edited by Dormilich: 14 August 2017 - 11:34 PM

Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13400
  • View blog
  • Posts: 53,477
  • Joined: 12-June 08

Re: Subquery/Join for matching exchange rate

Posted 15 August 2017 - 07:28 AM

Sure.. those were just dummy dates I threw in. Wouldn't it make more sense to match up the invoice date to the exchange date? You know if you print something six months from now you would want to print what the actual value was and not some whacked out new exchange rate?

At least that's how I would interpret the business requirements since money for the invoice most likely changed hands at that rate.

Regardless.. using the date part to get the day of the week number you can break it up into three queries and just put a union on them.

Same format from above:

inse rt into #invoice(id, invoice_date, invoicevalue) values (1, '08/01/2017', 10) 
inse rt into #invoice(id, invoice_date, invoicevalue) values (2, '07/01/2017', 4)
inse rt into #invoice(id, invoice_date, invoicevalue) values (3, '06/30/2017', 13)
inse rt into #invoice(id, invoice_date, invoicevalue) values (4, '07/02/2017', 8)
	 
inse rt into #exchange values (1, '08/01/2017', 3)
inse rt into #exchange values (1, '07/01/2017', 21)
inse rt into #exchange values (1, '06/30/2017', 19)



id          invoice_date            invoicevalue day#
----------- ----------------------- ------------ -----------
1           2017-08-01 00:00:00.000 10           3
2           2017-07-01 00:00:00.000 4            7
3           2017-06-30 00:00:00.000 13           6
4           2017-07-02 00:00:00.000 8            1


select a.id, a.invoice_date, invoicevalue, invoicevalue * val as ExchVal--,  DATEPART(dw, a.invoice_date)
from #invoice a
left join #exchange b on a.invoice_date = b.exchangedate
where DATEPART(dw, a.invoice_date) in (2, 3, 4, 5, 6)
union 
select a.id, a.invoice_date, invoicevalue, invoicevalue * val  as ExchVal
from #invoice a
left join #exchange b on DATEADD(day, -1,a.invoice_date) =  b.exchangedate
where DATEPART(dw, a.invoice_date) = 7
union
select a.id, a.invoice_date, invoicevalue, invoicevalue * val  as ExchVal
from #invoice a
left join #exchange b on DATEADD(day, -2,a.invoice_date) =  b.exchangedate
where DATEPART(dw, a.invoice_date) = 1


id          invoice_date            invoicevalue ExchVal
----------- ----------------------- ------------ -----------
1           2017-08-01 00:00:00.000 10           30
2           2017-07-01 00:00:00.000 4            76
3           2017-06-30 00:00:00.000 13           247
4           2017-07-02 00:00:00.000 8            152


For holidays..you could to hard code them all in, or I would think you could just insert the previous day into the exchange table.
Was This Post Helpful? 0
  • +
  • -

#5 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4128
  • View blog
  • Posts: 13,020
  • Joined: 08-June 10

Re: Subquery/Join for matching exchange rate

Posted 15 August 2017 - 07:35 AM

Quote

Wouldn't it make more sense to match up the invoice date to the exchange date?

That would make the invoice invalid ...

It seems that there is no easy solution that I may have overlooked.
Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: Subquery/Join for matching exchange rate

Posted 15 August 2017 - 12:09 PM

I may be missing something...

You want the current exchange rate? That would be, the one with the max date, as you're not looking into the future, so:
select val
  from #exchange 
  where exchangedate = (select max(exchangedate) from #exchange)



Want to apply that result to every total?
select a.id, a.invoice_date, a.invoicevalue, a.invoicevalue * b.val
  from #invoice a
    cross join (
      select val
        from #exchange 
        where exchangedate = (select max(exchangedate) from #exchange)
    ) b


Was This Post Helpful? 0
  • +
  • -

#7 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4128
  • View blog
  • Posts: 13,020
  • Joined: 08-June 10

Re: Subquery/Join for matching exchange rate

Posted 15 August 2017 - 11:48 PM

Quote

Want to apply that result to every total?

No.

What I essentially want is
SELECT USD FROM ExchangeRate WHERE Date <= invoice.PaidAt ORDER BY Date DESC LIMIT 1

as a JOIN
Was This Post Helpful? 0
  • +
  • -

#8 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: Subquery/Join for matching exchange rate

Posted 16 August 2017 - 06:11 AM

Ewww... ok, this is fun. Forgive me, I'm using MSSQL, but it should translate.

First, some data
CREATE TABLE #Invoice (
  [Id] int identity(1,1) not null primary key,
  [Price] decimal(10,2) NOT NULL,
  [PriceSymbol] char(3) NOT NULL DEFAULT 'EUR',
  PaidAt date
)

CREATE TABLE #ExchangeRate (
  [EffectiveDt] datetime not null primary key,
  [USD] decimal(5,4) NOT NULL
)

insert into #Invoice(Price, PaidAt) values(100.0, '8/1/2017')
insert into #Invoice(Price, PaidAt) values(101.0, '8/2/2017')
insert into #Invoice(Price, PaidAt) values(30.0, '8/3/2017')
insert into #Invoice(Price, PaidAt) values(40.0, '8/3/2017')
insert into #Invoice(Price, PaidAt) values(50.0, '8/4/2017')
insert into #Invoice(Price, PaidAt) values(60.0, '8/5/2017')

insert into #ExchangeRate  values('7/1/2017', 1.5)
insert into #ExchangeRate  values('8/1/2017', 0.92)
insert into #ExchangeRate  values('8/4/2017', 1.2)



Now, let's just do a naive join to see what things look like:
select *
  from #Invoice a
    join #ExchangeRate b on b.EffectiveDt <= a.PaidAt

Id          Price                                   PriceSymbol PaidAt     EffectiveDt             USD
----------- --------------------------------------- ----------- ---------- ----------------------- ---------------------------------------
1           100.00                                  EUR         2017-08-01 2017-08-01 00:00:00.000 0.9200
2           101.00                                  EUR         2017-08-02 2017-08-01 00:00:00.000 0.9200
3           30.00                                   EUR         2017-08-03 2017-08-01 00:00:00.000 0.9200
4           40.00                                   EUR         2017-08-03 2017-08-01 00:00:00.000 0.9200
5           50.00                                   EUR         2017-08-04 2017-08-01 00:00:00.000 0.9200
6           60.00                                   EUR         2017-08-05 2017-08-01 00:00:00.000 0.9200
5           50.00                                   EUR         2017-08-04 2017-08-04 00:00:00.000 1.2000
6           60.00                                   EUR         2017-08-05 2017-08-04 00:00:00.000 1.2000

(8 row(s) affected)



Obviously, we want a one to one, so we start with the date:
select a.Id, max(b.EffectiveDt)
  from #Invoice a
    join #ExchangeRate b on b.EffectiveDt <= a.PaidAt
  group by a.Id

Id          
----------- -----------------------
1           2017-08-01 00:00:00.000
2           2017-08-01 00:00:00.000
3           2017-08-01 00:00:00.000
4           2017-08-01 00:00:00.000
5           2017-08-04 00:00:00.000
6           2017-08-04 00:00:00.000

(6 row(s) affected)



Much better, but only half way there. Now, the other half.
select a.Id, b.USD
  from (
    select a.Id, max(b.EffectiveDt) as EffectiveDt
      from #Invoice a
        join #ExchangeRate b on b.EffectiveDt <= a.PaidAt
      group by a.Id
    ) a
    join #ExchangeRate b on b.EffectiveDt = a.EffectiveDt

Id          USD
----------- ---------------------------------------
1           0.9200
2           0.9200
3           0.9200
4           0.9200
5           1.2000
6           1.2000

(6 row(s) affected)



Now, the whole mess together:
select a.Id, a.Price, rate.USD * a.Price as USD
  from #Invoice a
    join (
      select a.Id, b.USD
        from (
          select a.Id, max(b.EffectiveDt) as EffectiveDt
            from #Invoice a
              join #ExchangeRate b on b.EffectiveDt <= a.PaidAt
            group by a.Id
          ) a
          join #ExchangeRate b on b.EffectiveDt = a.EffectiveDt
      ) rate on a.Id = rate.Id


Id          Price                                   USD
----------- --------------------------------------- ---------------------------------------
1           100.00                                  92.000000
2           101.00                                  92.920000
3           30.00                                   27.600000
4           40.00                                   36.800000
5           50.00                                   60.000000
6           60.00                                   72.000000

(6 row(s) affected)



Looking at this, I could have chosen my test data better, but I think this covers it.

Note, my exchange rate table has effective date as its primary key. I'd advise this, as a duplicate date will not only be logically invalid but will yield unpredictable results.

For table design, I'd actually prefer something like this for exchange:
CREATE TABLE #ExchangeRate (
  [EffectiveDt] datetime not null,
  [PriceSymbol] char(3) NOT NULL,
  [Rate] decimal(5,4) NOT NULL,
  primary key ([EffectiveDt], [PriceSymbol])
)



But, that's a challenge for another time.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1