2

I have a table with daily exchange rates from ECB

FXDate              EUR     USD     AUD     BGN     BRL     CAD     CHF     ....
30/06/2017 00:00    1.00    1.1412  1.4851  1.9558  3.76    1.4785  1.093
29/06/2017 00:00    1.00    1.1413  1.4868  1.9558  3.7476  1.4867  1.0935
28/06/2017 00:00    1.00    1.1375  1.4986  1.9558  3.7632  1.4888  1.0913
27/06/2017 00:00    1.00    1.1278  1.4819  1.9558  3.7216  1.4895  1.0883
26/06/2017 00:00    1.00    1.1187  1.4771  1.9558  3.7259  1.4812  1.0881
23/06/2017 00:00    1.00    1.1173  1.4764  1.9558  3.7293  1.4783  1.0851
22/06/2017 00:00    1.00    1.1169  1.4812  1.9558  3.7164  1.4853  1.0867
21/06/2017 00:00    1.00    1.1147  1.4738  1.9558  3.7028  1.4803  1.0857
20/06/2017 00:00    1.00    1.1156  1.4674  1.9558  3.6845  1.4812  1.0854
19/06/2017 00:00    1.00    1.1199  1.4705  1.9558  3.6957  1.4827  1.087

then I have a sales table like this:

UserName    Purchasedate        Currency    Price   Quantity    
John        24/06/2017 14:53    EUR         5.4     3
Jude        24/06/2017 18:47    USD         4       2
Jack        25/06/2017 07:34    USD         6.35    2
Jill        25/06/2017 11:42    EUR         4.15    1
Joe         26/06/2017 11:13    USD         4.50    1

I need to create a table where I have the latest exchange rate for the given Currency, and a summary of daily amount, if possible, like this:

UserName    PurchaseDate        Currency    Price   Quantity    ExchangeRate    ConvertedAmountEUR
John        24/06/2017          EUR         5.4     3           1.00            16.20
Jude        24/06/2017          USD         4       2           1.1173           8.94       //Exchange rate of 23rd since for 24th is not available
DAILY TOTAL 24/06/2017                                                          25.14
Jack        25/06/2017          USD         6.35    2           1.1173          14.19       //Exchange rate of 23rd since for 24th is not available
Jill        25/06/2017          EUR         4.15    1           1.00             4.15
DAILY TOTAL 25/06/2017                                                          18.34
Joe         26/06/2017          USD         4.50    1           1.1187           5.03       //Exchange rate of 26th
DAILY TOTAL 26/06/2017                                                           5.03

I tried with cross join to extract the right exchange rate but there is the problem of the "not matching days" while cannot use rollup for subtotal since it requires grouping while I need separated rows for each purchase

can suggest even a partial solution?
Thanks

0
2

This can be done in a step-by-step fashion.

First: we have the table sales differentiating between the PurchaseDateTime and the PurchaseDate:

CREATE TABLE sales
(
    UserName         varchar(100), 
    PurchaseDateTime datetime, 
    Currency         character(3),
    Price            numeric(10,2),
    Quantity         numeric(10,2),

    PurchaseDate AS CAST(PurchaseDateTime AS date),  -- Computed column
    PRIMARY KEY (UserName, PurchaseDateTime)
) ;

-- Secondary index, to help some JOINs
CREATE INDEX idx_sales_date ON sales(PurchaseDate, UserName);

This will make our life easier, as we already have the date precomputed.


Next thing you need to do is to unpivot your exchange_rate table:

CREATE TABLE #exchange_rates_EUR 
(
    FXDate date,
    Currency char(3),
    ExchangeRate decimal(10,4),
    PRIMARY KEY (Currency, FXDate)
) ;

INSERT INTO #exchange_rates_EUR 
SELECT
    FXDate, 'EUR' AS Currency, EUR AS ExchangeRate
FROM
    exchange_rates_pivot
UNION
SELECT
    FXDate, 'USD' AS Currency, USD AS ExchangeRate
FROM
    exchange_rates_pivot
// do the same for all currencies .. omitted here for simplicity

NOTE: I've used the name exchange_rates_pivot for your original pivoted exchange rates table.

You need this intermediate step to be able to do later on the proper JOINs (you can't actually JOIN in row and column. You cannot treat column names as if they were values [at least, not in any easy fashion]).

You can also use the UNPIVOT infrastructure from SQL Server, but this is more SQL Standard, although probably not as efficient.

The content of this table looks like:

SELECT TOP 12 * FROM #exchange_rates_EUR ORDER BY FXDate, Currency ;
FXDate              | Currency | ExchangeRate
:------------------ | :------- | :-----------
19/06/2017 00:00:00 | AUD      | 1.4705      
19/06/2017 00:00:00 | BGN      | 1.9558      
19/06/2017 00:00:00 | CAD      | 1.4827      
19/06/2017 00:00:00 | CHF      | 1.0870      
19/06/2017 00:00:00 | EUR      | 1.0000      
19/06/2017 00:00:00 | USD      | 1.1199      
20/06/2017 00:00:00 | AUD      | 1.4674      
20/06/2017 00:00:00 | BGN      | 1.9558      
20/06/2017 00:00:00 | CAD      | 1.4812      
20/06/2017 00:00:00 | CHF      | 1.0854      
20/06/2017 00:00:00 | EUR      | 1.0000      
20/06/2017 00:00:00 | USD      | 1.1156      

Second thing you need to do is finding the exchange rate for all your sales and put the info together.

For each specific purchase, the exchange rate is found by means of:

SELECT TOP 1
   ExchangeRate
FROM
   #exchange_rates_EUR er
WHERE
   er.Currency = s.Currency AND er.FXDate <= s.PurchaseDate 
ORDER BY
   er.FXDate DESC ;

Note that the combination of TOP 1 and ORDER BY will give you the currency exchange for the day, and if not available, for the closest previous date where this info is available; as I've inferred from your example. For the sake of completeness, it should actually be divided by the exchange rate of EUR, in case the exchange data is not based on EUR = 1.00. I haven't done at this point not to overcomplicate things.

This will lead us to the following query:

SELECT
    q1.UserName, q1.PurchaseDate, q1.Currency, q1.Price, q1.Quantity, q1.ExchangeRate, 
    CAST(q1.Price * q1.Quantity * q1.ExchangeRate AS decimal(10,2)) AS ConvertedAmountEUR
FROM
    (
    SELECT
        s.UserName, s.PurchaseDate, s.Currency, s.Price, s.Quantity, 
        -- Subquery to retrieve Exchange Rate
        CAST (
           (SELECT TOP 1
              ExchangeRate
           FROM
              #exchange_rates_EUR er
           WHERE
              er.Currency = s.Currency AND er.FXDate <= s.PurchaseDate 
           ORDER BY
              er.FXDate DESC
           ) 
           AS decimal(10,4))          
        AS ExchangeRate
    FROM
        sales s
    ) AS q1 
ORDER BY 
    PurchaseDate, UserName ;

... which results in:

UserName | PurchaseDate        | Currency | Price | Quantity | ExchangeRate | ConvertedAmountEUR
:------- | :------------------ | :------- | :---- | :------- | :----------- | :-----------------
John     | 24/06/2017 00:00:00 | EUR      | 5.40  | 3.00     | 1.0000       | 16.20             
Jude     | 24/06/2017 00:00:00 | USD      | 4.00  | 2.00     | 1.1173       |  8.94              
Jack     | 25/06/2017 00:00:00 | USD      | 6.35  | 2.00     | 1.1173       | 14.19             
Jill     | 25/06/2017 00:00:00 | EUR      | 4.15  | 1.00     | 1.0000       |  4.15              
Joe      | 26/06/2017 00:00:00 | USD      | 4.50  | 1.00     | 1.1187       |  5.03              

Last, we compute also the 'DAILY TOTAL', and perform a UNION ALL with what we already have. A WITH statement comes in handy in this case. Some smallish trickery is needed to ease the ordering (look for the OrderBy column).

This is done with the following statement:

; WITH exchanged_sales AS
(
SELECT
    q1.UserName, q1.PurchaseDate, q1.Currency, q1.Price, q1.Quantity, q1.ExchangeRate, 
    CAST(q1.Price * q1.Quantity * q1.ExchangeRate AS decimal(10,2)) AS ConvertedAmountEUR, 0 AS OrderBy
FROM
    (
    SELECT
        s.UserName, s.PurchaseDate, s.Currency, s.Price, s.Quantity, 
        -- Subqueries to retrieve Exchange Rate
        CAST (
           (SELECT TOP 1 ExchangeRate
           FROM      #exchange_rates_EUR er
           WHERE     er.Currency = s.Currency AND er.FXDate <= s.PurchaseDate 
           ORDER BY  er.FXDate DESC) /    
           (SELECT TOP 1 ExchangeRate
           FROM      #exchange_rates_EUR er
           WHERE     er.Currency = 'EUR' AND er.FXDate <= s.PurchaseDate 
           ORDER BY  er.FXDate DESC) AS decimal(10,4)) AS ExchangeRate
    FROM
        sales s
    ) AS q1 
)
SELECT 
    UserName, PurchaseDate, Currency, Price, Quantity, ExchangeRate, ConvertedAmountEUR
FROM
    (-- The purchases date, user
    SELECT   *
    FROM     exchanged_sales
    UNION 
    -- The daily totals
    SELECT   'DAILY TOTAL' AS UserName, PurchaseDate, NULL AS Currency, NULL AS Price, NULL AS Quantity, NULL AS ExchangeRate,
        SUM(ConvertedAmountEUR) AS ConvertedAmountEUR, 1 AS OrderBy
    FROM     exchanged_sales
    GROUP BY PurchaseDate
    ) AS q
ORDER BY
    PurchaseDate, OrderBy, UserName ;

... whose result is:

UserName    | PurchaseDate | Currency | Price | Quantity | ExchangeRate | ConvertedAmountEUR
:---------- | :----------- | :------- | :---- | :------- | :----------- | :-----------------
John        |   24/06/2017 | EUR      | 5.40  | 3.00     | 1.0000       | 16.20             
Jude        |   24/06/2017 | USD      | 4.00  | 2.00     | 1.1173       |  8.94              
DAILY TOTAL |   24/06/2017 |          |       |          |              | 25.14             
Jack        |   25/06/2017 | USD      | 6.35  | 2.00     | 1.1173       | 14.19             
Jill        |   25/06/2017 | EUR      | 4.15  | 1.00     | 1.0000       |  4.15              
DAILY TOTAL |   25/06/2017 |          |       |          |              | 18.34             
Joe         |   26/06/2017 | USD      | 4.50  | 1.00     | 1.1187       |  5.03              
DAILY TOTAL |   26/06/2017 |          |       |          |              |  5.03              

You can check all the step-by-step details at dbfiddle here

If you would need everything in just one statement, you could put the equivalent of #exchange_rate as part of the WITH. This will give you a very large statement, not so easy to read... For the all-in-one version dbfiddle here

1
  • Wow.. thanks a lot for the step by step explanation.. will try to understand all steps.. But first of all I understood I have to change the way I save data from ECB so to avoid unpivot step, that was the biggest issue I had.. – Joe Jul 3 '17 at 8:26
1

I am not discussing index here because this is not real table schema.

This is for sure your ECB table is wrong,you should never store data in pivot manner.for particular scenario you can pivot it but always store data as prescribe by Normalization rule.

Also you cannot pull all the data,so you have to filter data base on date or some method of pagination.

Also I will manipulate part of your desire resultset in UI application to reduce pressure on Sql server.Since lot of manipulation is going on. For Example I do 'Daily total" part in GUI .

So Accordingly my script can be optimized .

create table #ECB(FXDate datetime,EUR float,USD float,AUD float
,BGN float,BRL float,CAD float,CHF float)
insert into #ECB VALUES
 ('2017/06/30 00:00',1.00 ,1.1412 , 1.4851 , 1.9558  , 3.76   , 1.4785 , 1.093  ) 
,('2017/06/29 00:00',1.00 ,1.1413 , 1.4868 , 1.9558 , 3.7476 , 1.4867 , 1.0935 )
,('2017/06/28 00:00',1.00 ,1.1375 , 1.4986 , 1.9558 , 3.7632 , 1.4888 , 1.0913 )
,('2017/06/27 00:00',1.00 ,1.1278 , 1.4819 , 1.9558 , 3.7216 , 1.4895 , 1.0883 )
,('2017/06/26 00:00',1.00 ,1.1187 , 1.4771 , 1.9558 , 3.7259 , 1.4812 , 1.0881 )
,('2017/06/23 00:00',1.00 ,1.1173 , 1.4764 , 1.9558 , 3.7293 , 1.4783 , 1.0851 )
,('2017/06/22 00:00',1.00 ,1.1169 , 1.4812 , 1.9558 , 3.7164 , 1.4853 , 1.0867 )
,('2017/06/21 00:00',1.00 ,1.1147 , 1.4738 , 1.9558 , 3.7028 , 1.4803 , 1.0857 )
,('2017/06/20 00:00',1.00 ,1.1156 , 1.4674 , 1.9558 , 3.6845 , 1.4812 , 1.0854 )
,('2017/06/19 00:00',1.00 ,1.1199 , 1.4705 , 1.9558 , 3.6957 , 1.4827 , 1.087  )



create table #Sales(UserName varchar(50),Purchasedate datetime,Currency char(4)
,Price float,Quantity int)
  insert into #Sales VALUES
 ('John','2017/06/24 14:53','EUR', 5.4  ,3)
,('Jude','2017/06/24 18:47','USD', 4    ,2)
,('Jack','2017/06/25 07:34','USD', 6.35 ,2)
,('Jill','2017/06/25 11:42','EUR', 4.15 ,1)
,('Joe ','2017/06/26 11:13','USD', 4.50 ,1)

;

WITH cte
AS (
    SELECT *
    FROM (
        SELECT *
        FROM #ECB
        ) src
    unpivot(CurrencyRate FOR CurrencyValue IN (
                EUR
                ,USD
                ,AUD
                ,BGN
                ,BRL
                ,CAD
                ,CHF
                )) unpvt
    )
    ,CTE1
AS (
    SELECT UserName
        ,cast(Purchasedate AS DATE) Purchasedate
        ,Currency
        ,Price
        ,Quantity
    FROM #Sales
    )
    ,cte2
AS (
    SELECT c1.username
        ,c1.purchasedate
        ,c1.currency
        ,c1.price
        ,c1.quantity
        ,(
            SELECT TOP 1 c.CurrencyRate
            FROM cte c
            WHERE c1.Currency = c.CurrencyValue
                AND c.FXDate <= c1.Purchasedate
            ORDER BY fxdate DESC
            ) ExchangeRate
    FROM cte1 c1
    )
    ,cte3
AS (
    SELECT username
        ,purchasedate
        ,currency
        ,price
        ,quantity
        ,ExchangeRate
        ,(price * quantity * ExchangeRate) ConvertedAmountEUR
    FROM cte2
    )
SELECT *
FROM (
    SELECT *
    FROM cte3

    UNION ALL

    SELECT 'Daily Total'
        ,purchasedate
        ,NULL
        ,NULL
        ,NULL
        ,NULL
        ,sum(ConvertedAmountEUR)
    FROM cte3
    GROUP BY purchasedate
    ) t4
ORDER BY purchasedate


drop table #Sales
drop table #ECB 
2
  • hi, thanks for hints and for solution. I've rebuilt the Exchange Rate table as per your suggestion You solution is currently too complicated for me: but will try to learn from it – Joe Jul 4 '17 at 1:29
  • Since you have change the table design, all the complication gone.Also don't forget "manipulate part of your desire resultset in UI" idea. – KumarHarsh Jul 4 '17 at 4:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.