There are tables transactions and currencyRate:
I am displaying transactions of client (sender_name) with total amount sent money more than (amount) > 1000 within seven days after the first transaction_date. So, all Alices transactions because the first payment is 03.01 and within seven days until 09.01 and her transactions total amount during these days is more than 1000. And all Bettys transactions also have the same logic.For Carol transactions 10.09, 11.09 and 17.09.
But now I want: first to convert amount of other currencies exept USD to USD, sellingRate column from currencyRate table. After sum of all amount converted to USD amount. Example, lets take: Alice transactions. There amount of other currencies: 450 EUR, 1000 EUR, 200 GBP. Converting:
450 EUR * (multiply) 1.09 = 490.5 USD
1000 EUR * (multiply) 1.09 = 1090 USD
200 GBP * (multiply) 1.25 = 250 USD
And after, total amount for Alice will be 2530.5.
Expecting result: Alices transactions because amount will be more than 1000 and for Carol transactions 10.09, 11.09 and 17.09 which amount also will be bigger than 1000 after converting. Betty`s transactions will not be displayed, because after converting will be less than 1000.
Could you help please, to add converting to the query?
As I said in a comment on your previous question, LEFT OUTER JOIN the two tables and then use a CASE expression to check if the currency is USD and then multiply 1 or by the selling rate:
SELECT id,
sender_name,
transaction_date,
amount,
usd_amount
FROM (
SELECT id,
sender_name,
transaction_date,
amount,
usd_amount,
COUNT(CASE WHEN previous_weekly_amount > 1000 THEN 1 END) OVER (
PARTITION BY sender_name
ORDER BY transaction_date
RANGE BETWEEN INTERVAL '0 00:00:00.000000000' DAY TO SECOND PRECEDING
AND INTERVAL '6 23:59:59.999999999' DAY TO SECOND FOLLOWING
) AS has_weekly_total_over_1000
FROM (
SELECT t.id,
t.sender_name,
t.transaction_date,
t.amount,
t.amount
* CASE WHEN t.currency = 'USD' THEN 1 ELSE c.sellingrate END
AS usd_amount,
SUM(
t.amount
* CASE WHEN t.currency = 'USD' THEN 1 ELSE c.sellingrate END
) OVER (
PARTITION BY t.sender_name
ORDER BY t.transaction_date
RANGE BETWEEN INTERVAL '6 23:59:59.999999999' DAY TO SECOND PRECEDING
AND INTERVAL '0 00:00:00.000000000' DAY TO SECOND FOLLOWING
) AS previous_weekly_amount
FROM transactions t
LEFT OUTER JOIN currencyRate c
ON (t.currency = c.currencyName)
)
)
WHERE has_weekly_total_over_1000 > 0;