CODEWITHSUNDEEP
sqlsql-servert-sql
oibaf
oibaf

Reputation: 43

SQL Select: Get the previous Date in column

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: exemple2.0

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:

Upvotes: 4

Views: 4438

Answers (4)

JayaPrakash
JayaPrakash

Reputation: 199

For SQL Server 2008 and later:

Hope you did want an auto column generation with lagging one value behind from the past columns first value. Try the following snippet.

Created a dynamic query with respect to the number of columns in the dataset.

create table BFSStudierende
(
BFSSTudStichdatum  datetime
)
insert into BFSStudierende
Select getdate()
union
Select dateadd(day,1,getdate())
union
Select dateadd(day,2,getdate())
union
Select dateadd(day,3,getdate())
union
Select dateadd(day,4,getdate())

Declare @count int=(Select count(BFSSTudStichdatum ) from BFSStudierende)

Declare @query nvarchar(max)='with BFSStudierendeCte as (Select *,row_number() over(order by BFSSTudStichdatum)rn  from BFSStudierende)  Select *from BFSStudierendeCte as BFSStudierendeCte1'

Declare  @i int=2 ;
Declare  @j int ;

while(@i<=@count)
begin
Set @j=@i-1
Set @query=@query+' left outer join BFSStudierendeCte as BFSStudierendeCte'+cast(@i as varchar(5)) +' on BFSStudierendeCte1.rn+'+cast(@j as varchar(5))+'=BFSStudierendeCte'+cast(@i as varchar(5))+'.rn';
set @i+=1;
End
print @query
Execute(@query)

Note: Duplicate date will not be removed from the results. If you require duplicate to be removed. Please change the following line in the above snippet.

Declare @count int=(Select count(distinct BFSSTudStichdatum ) from BFSStudierende)

Declare @query nvarchar(max)='with BFSStudierendeCte as (Select *,row_number() over(order by BFSSTudStichdatum)rn from(Select distinct BFSSTudStichdatum from BFSStudierende)l   )  Select *from BFSStudierendeCte as BFSStudierendeCte1'

Upvotes: 0

gotqn
gotqn

Reputation: 43636

For SQL Server 2008 and later:

WITH DataSource AS
(
    SELECT DISTINCT *
          ,DENSE_RANK() OVER (ORDER BY c1) rowID
    FROM @t
)
SELECT DS1.[c1]
      ,DS2.[c1]
      ,DS3.[c1]
FROM DataSource DS1
LEFT JOIN DataSource DS2
    ON DS1.[rowID] = DS2.[rowID] + 1
LEFT JOIN DataSource DS3
    ON DS1.[rowID] = DS3.[rowID] + 2;

Upvotes: 0

iamdave
iamdave

Reputation: 12243

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                    |
+-------------------------+-------------------------+-------------------------+

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 2

Related Questions