SQL string comparison by dates?

Question

SQL

There is a log table with the following logs:
id | link_id | date | value
where id is a sequence number, link_id is a link to another table, date is the record creation date, value is a value.
It is required to find the difference between the value of the last and the penultimate date for each link_id, the one itself gets something like this:

select log1.link_id,log2.link_id,log1.value,log2.value,log1.value - log2.value as diff,log1.date,log2.date
from log as log1, log as log2
where log1.link_id = log2.link_id and 
log2.date = (select max(date) from log as log3 where log3.link_id = log2.link_id) and 
log1.date = (select max(date) from log as log4 where log4.link_id = log1.link_id and log4.date = (select max(date) from log as log5 where log5.link_id = log4.link_id))

In my opinion, the logic is as follows: we display the difference between value where link_id match and where one date is the maximum, the other before the maximum

Answer 1

Why don't you like your own version? There you only need to change the "=" to "<" in the "log4.date =" condition.

Answer 2

For MS SQL, you can use the following query:

select link_id, sum(case NUM when 1 then [date] when 2 then [date]*(-1) else 0 end)
from (select ROW_NUMBER() over (PARTITION BY link_id order by [date]) as NUM, link_id, [date] from [log])t
group by link_id

Answer 3

@SabMakc , slightly off. We need the value difference, not [date]. And what is [date]*(-1)???
It will be correct like this:

select link_id, sum(case NUM 
                      when 1 then value 
          when 2 then value*(-1) 
          else 0 
        end)
from (select ROW_NUMBER() over (PARTITION BY link_id order by [date] desc) as NUM, link_id, value from log) t
group by link_id