SQL Select: Get the previous Date in column

Question

I have a table in my SQL server with some dates. Now I would like to create a Select which gives me a column with all dates then a second column with the previous dates of the first column and a third column with the previous dates of the previous date column(c2). For Exempel:

  c1(orginal)          c2(prevoius of c1)    c3(previous of c2)
2017-10-15 00:00:00   2017-04-15 00:00:00   2016-10-15 00:00:00
2017-04-15 00:00:00   2016-10-15 00:00:00   2016-04-15 00:00:00
2016-10-15 00:00:00   2016-04-15 00:00:00   2015-10-15 00:00:00
2016-04-15 00:00:00   2015-10-15 00:00:00           null
2015-10-15 00:00:00           null                  null

Example with colors:

Is it possible to make a SELECT where the first row would be the first date from column 1, the second from column 1 and the third from column 1. The second row would be the second date from column1, the third from column 1 and the forth from column 1.

My current query

SELECT DISTINCT(BFSSTudStichdatum) AS C1, BFSSTudStichdatum AS C2, 
BFSSTudStichdatum AS C3  FROM BFSStudierende
ORDER BY C1 DESC

result:

Answer 1

Are you looking for lag()?

select col1,
       lag(col1, 1) over (order by col1) as col1_prev,
       lag(col1, 2) over (order by col1) as col1_prev2
from t;

Answer 2

Because you need to get a distinct list of your dates first, you will need to split your query into a common table expression and then use lag to get your c2 and c3 values:

declare @t table(c1 datetime);
insert into @t values ('2017-10-15 00:00:00'),('2017-04-15 00:00:00'),('2016-10-15 00:00:00'),('2016-04-15 00:00:00'),('2015-10-15 00:00:00')
                     ,('2017-10-15 00:00:00'),('2017-04-15 00:00:00'),('2016-10-15 00:00:00'),('2016-04-15 00:00:00'),('2015-10-15 00:00:00');

with c as
(
    select distinct c1
    from @t
)
select c1
      ,lag(c1, 1) over (order by c1) as c2
      ,lag(c1, 2) over (order by c1) as c3
from c
order by c1 desc;

Output:

+-------------------------+-------------------------+-------------------------+
|           c1            |           c2            |           c3            |
+-------------------------+-------------------------+-------------------------+
| 2017-10-15 00:00:00.000 | 2017-04-15 00:00:00.000 | 2016-10-15 00:00:00.000 |
| 2017-04-15 00:00:00.000 | 2016-10-15 00:00:00.000 | 2016-04-15 00:00:00.000 |
| 2016-10-15 00:00:00.000 | 2016-04-15 00:00:00.000 | 2015-10-15 00:00:00.000 |
| 2016-04-15 00:00:00.000 | 2015-10-15 00:00:00.000 | NULL                    |
| 2015-10-15 00:00:00.000 | NULL                    | NULL                    |
+-------------------------+-------------------------+-------------------------+