Sql-server – SQL Server 2014 extract value from table with double join in row and column and subtotal

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