MYSQL - Finding row delta using date rows that include holidays

Question

I have a table that includes a field with dates (call it date) and a field with a cumulative running total (call it X) | call it table SAMPLE.

***My data in field DATE does not include weekends and holidays.

I can find the delta in the numbers from day to day by simply subtracting any chosen value in "X" and subtracting that from the row above.

Here's my current query:

select
date,
a.X - b.X as 'Daily Total'

from SAMPLE as a 
left join SAMPLE as b
on b.date = if(weekday(a.date) = 0 , a.date - interval 3 day, a.date- interval 1 day);

The problem is that the above values work until I hit dates with holidays. If Monday is a holiday, then the values return null because a.date - interval 1 day will not exist. What's the best way to go about solving the holidays issue?

the below are the current results:

+------------+---------------+
| date       | X             |
+------------+---------------+
| 2018-03-26 |     -40105.00 |
| 2018-03-27 |      28470.00 |
| 2018-03-28 |       5265.00 |
| 2018-03-29 |     -23010.00 |
| 2018-04-02 |          NULL |
| 2018-04-03 |     -24830.00 |
| 2018-04-04 |     -21970.00 |
| 2018-04-05 |      -9620.00 |
| 2018-04-06 |      36465.00 |

Thanks in advance!!

Answer 1

I will sort the table by date then assign a sequence or series of numbers from 1 to n. I will then subtract the value of current row from the previous row except the first row. For first row, i will copy the value X.

select rnk2.`date`, 
case when rnk1.r1=1 and rnk2.r2=1 then rnk1.X else rnk2.X-rnk1.X end as 'Daily Total'
from (
  select `date`,X,@r1:=@r1+1 as r1
  from  samples, (select @r1:=0) a 
  order by `date` ) rnk1
inner join 
  (select `date`,X,@r2:=@r2+1 as r2
   from  samples, (select @r2:=0) b
   order by `date`) rnk2
on (rnk1.r1=1 and rnk2.r2=1) or (rnk1.r1+1=rnk2.r2) 
order by rnk2.`date`