Percentage difference between numbers in two columns

Question

My SQL experience is fairly minimal so please go easy on me here. I have a table tblForEx and I'm trying to create a query that looks at one particular column LastSalesRateChangeDate and also ForExRate.

Basically what I want to do is for the query to check that LastSalesRateChangeDate and then pull the ForExRate that is on the same line (obviously in the ForExRate column), then I need to check to see if there is a +/- 5% change since the last time the LastSalesRateChangeDate changed. I hope this makes sense, I tried to explain it as clearly as possible.

I believe I would need to create a 'subquery' to look at the LastSalesRateChangeDate and pull the ForEx rate from that date, but I just don't know how to go about this.

I should add this is being done in Access (SQL)

Sample data, here is what the table looks like:

| BaseCur | ForCur | ForExRate | LastSalesRateChangeDate
| USD     | BRL    | 1.718     | 12/9/2008      
| USD     | BRL    | 1.65      | 11/8/2008

So I would need a query to look at the LastSalesRateChangeDate column, check to see if the date has changed, if so take the ForExRate value and then give a percentage difference of that ForExRate value since the last record.

So the final result would likely look like

"BaseCur" "ForCur" "Percentage Change since Last Sales Rate Change"
USD         BRL            X%

Answer 1

Gordon's answer pointed in the right direction:

SELECT t2.*, (SELECT top 1 t.ForExRate
FROM tblForEx t
where t.BaseCur=t2.BaseCur AND t.ForCur=t2.ForCur and t.LastSalesRateChangeDate<t2.LastSalesRateChangeDate
order by t.LastSalesRateChangeDate DESC, t.ForExRate DESC
) AS PreviousRate, [ForExRate]/[PreviousRate]-1 AS ChangeRatio
FROM tblForEx AS t2;

Access gives errors where the TOP 1 in the subquery causes "ties". We broke the ties and therefore removed the error by adding an extra item to the ORDER BY clause. To get the ratio to display as a percentage, switch to the design view and change the properties of that column accordingly.

Answer 2

SELECT basetbl.BaseCur, basetbl.ForCur, basetbl.NewDate, basetbl.OldDate, num2.ForExRate/num1.ForExRate*100 AS PercentChange FROM 
(((SELECT t.BaseCur, t.ForCur, MAX(t.LastSalesRateChangeDate) AS NewDate, summary.Last_Date AS OldDate
FROM (tblForEx AS t 
    LEFT JOIN (SELECT TOP 2 BaseCur, ForCur, MAX(LastSalesRateChangeDate) AS Last_Date FROM tblForEx AS t1 
            WHERE LastSalesRateChangeDate <> 
            (SELECT MAX(LastSalesRateChangeDate) FROM tblForEx t2 WHERE t2.BaseCur = t1.BaseCur AND t2.ForCur = t1.ForCur) 
            GROUP BY BaseCur, ForCur) AS summary 
        ON summary.ForCur = t.ForCur AND summary.BaseCur = t.BaseCur)
GROUP BY t.BaseCur, t.ForCur, summary.Last_Date) basetbl
LEFT JOIN tblForEx num1 ON num1.BaseCur=basetbl.BaseCur AND num1.ForCur = basetbl.ForCur AND num1.LastSalesRateChangeDate = basetbl.OldDate))
LEFT JOIN tblForEx num2 ON num2.BaseCur=basetbl.BaseCur AND num2.ForCur = basetbl.ForCur AND num2.LastSalesRateChangeDate = basetbl.NewDate;

This uses a series of subqueries. First, you are selecting the most recent date for the BaseCur and ForCur. Then, you are joining onto that the previous date. I do that by using another subquery to select the top two dates, and exclude the one that is equal to the previously established most recent date. This is the "summary" subquery.

Then, you get the BaseCur, ForCur, NewDate, and OldDate in the "basetbl" subquery. After that, it is two simple joins of the original table back onto those dates to get the rate that was applicable then.

Finally, you are selecting your BaseCur, ForCur, and whatever formula you want to use to calculate the rate change. I used a simple ratio in that one, but it is easy to change. You can remove the dates in the first line if you want, they are there solely as a reference point.

It doesn't look pretty, but complicated Access SQL queries never do.

Answer 3

As per my understanding, you can use LEAD function to get last changed date Rate in a new column by using below query:

WITH CTE AS (
SELECT *, LEAD(ForExRate, 1) OVER(PARTITION BY BaseCur, ForCur ORDER BY LastChangeDate DESC) LastValue
FROM #TT
)
SELECT BaseCur, ForCur, ForExRate, LastChangeDate , CAST( ((ForExRate - ISNULL(LastValue, 0))/LastValue)*100 AS float)
FROM CTE

Problem here is:

1. for every last row in group by you will have new calculalted column which we have made using LEAD function.

2. If there is only a single row for a particular BaseCur and ForCur, then also you will have NULL in column.

Resolution: If you are sure that there will be at least two rows for each BaseCur and ForCur, then you can use WHERE clause to remove NULL values in final result.

WITH CTE AS (
SELECT *, LEAD(ForExRate, 1) OVER(PARTITION BY BaseCur, ForCur ORDER BY LastChangeDate DESC) LastValue
FROM #TT
)
SELECT BaseCur, ForCur, ForExRate, LastChangeDate , CAST( ((ForExRate - ISNULL(LastValue, 0))/LastValue)*100 AS float) Percentage
FROM CTE
    WHERE LastValue IS NOT NULL

Answer 4

If I understand correctly, you want the previous value. In MS Access, you can use a correlated subquery:

select t.*,
       (select top (1) t2.LastSalesRateChangeDate
        from tblForEx as t2
        where t2.BaseCur = t.BaseCur and t2.ForCur = t.ForCur
              t2.LastSalesRateChangeDate < t.LastSalesRateChangeDate
        order by t2.LastSalesRateChangeDate desc
       ) as prev_LastSalesRateChangeDate
from t;

Now, with this as a subquery, you can get the previous exchange rate using a join:

select t.*, ( (t.ForExRate / tprev.ForExRate) - 1) as change_ratio
from (select t.*,
             (select top (1) t2.LastSalesRateChangeDate
              from tblForEx as t2
              where t2.BaseCur = t.BaseCur and t2.ForCur = t.ForCur
                    t2.LastSalesRateChangeDate < t.LastSalesRateChangeDate
              order by t2.LastSalesRateChangeDate desc
             ) as prev_LastSalesRateChangeDate
      from t
     ) as t inner join
     tblForEx as tprev
     on tprev.BaseCur = t.BaseCur and tprev.ForCur = t.ForCur
        tprev.LastSalesRateChangeDate = t.prev_LastSalesRateChangeDate;