SQL Shift column down 1 row

Question

I am trying to subtract a value from a past time period with the value in the present time period.

*This is what I have:

Date,  present, past (identical to present)
 Jan,   100,        100
 Feb,   200,        200
March,  300,        300
April,  400,        400

*This is what I want;

Date,  present, past (past column shifted down 1 row)

     Jan,    100,       0
     Feb,   200,        100
    March,  300,        200
    April,  400,        300

Sample of code:

SELECT
      [month],
      sum([money]) as present,
      sum([money]) as past
FROM database
group by [month]

Answer 1

You can use the LAG() function, as in:

select
  d,
  present,
  lag(past, 1, 0) over (order by d) as past  
from t
order by d

Result:

d          present past
---------- ------- ----
2019-01-01 100        0
2019-02-01 200      100
2019-03-01 300      200
2019-04-01 400      300

See SQL Fiddle.

For the record, here's the data script:

create table t (
  d date,
  present int,
  past int
);

insert into t (d, present, past) values ('2019-01-01', 100, 100);
insert into t (d, present, past) values ('2019-02-01', 200, 200);
insert into t (d, present, past) values ('2019-03-01', 300, 300);
insert into t (d, present, past) values ('2019-04-01', 400, 400);