Percentage Difference Between two fields?

[natonstan]

I have a table named tlbForEx, there's 2 columns in that table one called ForExRate and one called SalesRate, I need to make a query that takes those 2 fields and works out the percentage difference between the two, is there any easy way to do this? My SQL experience is very limited currently..

[ranman256]

In a query, Make a 3rd field,( ForExRate-SalesRate)

[natonstan]

Hi ranman, thanks for the help, so I have my blank query:

SELECT tblForEx.ForExRate, SalesRate;
FROM tblForEx;

How would I add the 3rd field creation to this?

[natonstan]

Okay, scrap that question, I got it working by simply adding:

,(ForExRate-SalesRate)

Now how would I get that to show as a percentage, I've tried using AS Percent, but I'm getting an error saying incorrect spelling of Percent?

[Micron]

, Format(ForExRate-SalesRate, percent)?

[Bob Fitz]

Percentage of what. ForExRate or SalesRate

[natonstan]

I need it to display the average as a percentage, so where I have (ForExRate-SalesRate) I need that total to be in a percentage

I've tried adding , Format(ForExRate-SalesRate, percent)

But I get an input box asking for a percentage

[andy49]

Do you mean the mathematical idea of a % increase or decrease

that would be

Format((ForExRate-SalesRate)/salesrate, "percent")

[natonstan]

I think I may be going about this the wrong way now that I look at it, the main goal of this is to see if there is a -/+ 5% difference in the ForEx Rate since the LastSalesRateChangeDate column changed.

So I think I could probably simplify it a bit, basically I would need for my query to check the LastSalesRateChangeDate in the tblForEx table, then look at the ForExRate field and calculate the percentage since the last time the LastSalesRateChangeDate changed.

I hope that is clear enough, it sounds clear in my head but actually writing out a query is confusing me slightly..

[natonstan]

I received this help from someone, but I can't work out if the 't's are supposed to be in reference to my table tblForEx, would I essentially replace all 't' to tblForEx?

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;

[andy49]

doesnt it work as it is

If not, what does it produce

[natonstan]

Andy, if I try to run that code as is, I get Syntax Error (missing operator) in query expression '', when I click ok it highlights a section in the second to last line, the tprev.ForCur (but weirdly only the 'ev.Fo' letters

[andy49]

I think your missing an AND at the end of the second last line

[natonstan]

Do you mean I need an ; at the end of the second line?

If I do that I get "Character found after end of SQL statement

[andy49]

I think your missing an AND at the end of the second last line

typed it too late