SQL Server 2014 extract value from table with double join in row and column and subtotal 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
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 &lt;= 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 &lt;= 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 &lt;= s.PurchaseDate 
           ORDER BY  er.FXDate DESC) /    
           (SELECT TOP 1 ExchangeRate
           FROM      #exchange_rates_EUR er
           WHERE     er.Currency = 'EUR' AND er.FXDate &lt;= 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