Use the right language for the job–if you know

Many years ago I have done a site that lists the exchange rates from BNR and BCE ( www.infovalutar.ro ). It reads the exchanges from BNR html page and then put into a database. The use is for programmers – there are many methods to find latest exchange rates ( RSS, SOAP, url, by MVC, JSON… –  see  all there : http://infovalutar.ro/programatori )

I keep the values in a simple table like

IDMoneda    Valoare    DataValorii
EUR    3.5842    2006-07-07 00:00:00
EUR    3.5892    2006-06-29 00:00:00
EUR    3.5887    2006-07-13 00:00:00
EUR    3.5942    2006-07-19 00:00:00
EUR    3.5642    2006-07-20 00:00:00
EUR    3.5672    2006-07-24 00:00:00

As you see . the value of the date is not contiguous – in the weekends is not a new exchange rate.

Now, the problem:  How can we detect if there is an error with parsing the values  ? The usual error is putting different values from NBR ( maybe they have also some csv file to parse ?). For example take this

IDMoneda    Valoare    DataValorii
RSD    0.0396    2019-01-17 00:00:00
RSD    0.0397    2019-01-18 00:00:00
RSD    0.0398   2019-01-21 00:00:00
RSD    3.98E-06    2019-01-22 00:00:00
RSD    4.02E-06    2019-01-23 00:00:00
RSD    4.02E-06    2019-01-25 00:00:00

How can we find where the difference starts for the currency ? ( We assume a relatively stable Romanian economy …. not like Venezuela )

The answer how to calculate where the problem is relatively easy: the percentage of how much the value has varied : ( CurrentValue  – Last_Value)/ Last_Value * 100 .  It should not pass 1 or , maximum 2 % .

But how to calculate this ?

For C# – or any other OOP programs- it is normal to read record by record , order by IDMoneda and DataValorii – and calculate the percentage. However , C# is not made for this kind of calculus.

Then we can see that is a database involved – and we  think about SQL . It is a complicated SQL –because of the fact that the dates are not contiguous – because of the Weekends. However , there is a simple SQL construct, LAG:https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2016

select
abs(LAG(Valoare,1,0)Over(Partition BY IDMoneda Order BY DataValorii) – Valoare)/Valoare *100 ,Valoare , from CV_Valori

order by 1 desc

This query executes in seconds and we can see the problems fast

Sq- if you know the tool , please use in your current task!((

If you ask me about functional programming(F#) my answer it will be DSL)