SQL - calculation made on values found in same table

Question

Assume that I have this table:

Name,date,value
Mr.X,2018-03-20,1
Mr.A,2018-03-20,10
Mr.B,2018-03-20,11
Mr.C,2018-03-20,12
Mr.A,2018-03-22,20
Mr.B,2018-03-22,22
Mr.C,2018-03-22,25
Mr.D,2018-03-22,42

How can I then generate this result:

Mr.A,10
Mr.B,11
Mr.C,13

I thus want to show all the names who appear on both dates and then show the difference in the value for the two days next to each person..

Answer 1

You can join the table to itself, matching the same person on different days:

select  d1.name
,       d2.value - d1.value
from    YourTable d1
join    YourTable d2
on      d1.date = '2018-03-20'
        and d2.date = 2018-03-22'
        and d1.name = d2.name