CODEWITHSUNDEEP
sqlsql-servert-sql
Arash
Arash

Reputation: 1826

sql server query , each cell's value is next cell - 1

i have a table like below :

id    DateFrom      
--------------------
1     2000/05/01    
2     2000/05/05
3     2000/05/05
4     2000/05/05    
5     2000/05/10    
6     2000/05/15    
7     2000/05/25

and i want to make it like this . which DateTo contain the next Date - 1 Day

id    DateFrom      DateTo
---------------------------
1     2000/05/01    2000/05/04  
2     2000/05/05    2000/05/09
3     2000/05/05    2000/05/09
4     2000/05/05    2000/05/09
5     2000/05/10    2000/05/14
6     2000/05/15    2000/05/24
7     2000/05/25    null

i used RowNumber() function but i didng get any result . can anybody help me how to do it or even from which method or function should i perform my job . thank you

Edited. note : my table has duplicate dates

Upvotes: 0

Views: 150

Answers (2)

jpw
jpw

Reputation: 44911

One way is to use a self-join like this:

SELECT 
    t1.id, 
    t1.DateFrom, 
    DATEADD(DAY,-1,t2.DateFrom) AS DateTo
FROM table1 t1
LEFT JOIN table1 t2 ON t2.id = t1.id+1

Sample SQL Fiddle

Edit: As you have duplicates, you can use a correlated subquery instead. I've updated the SQL Fiddle to include the second solution:

select 
    id, 
    DateFrom, 
    (
        select top 1 dateadd(day,-1,datefrom) 
        from table1 
        where id > t1.id and datefrom > t1.datefrom 
        order by id
    ) as DateTo
from table1 t1

Upvotes: 2

StuartLC
StuartLC

Reputation: 107357

Here's an alternative using LEAD:

SELECT
  DateFrom, 
  DATEADD(dd, -1, LEAD(DateFrom) OVER (ORDER BY DateFrom)) AS DateTo
FROM SomeTable;

SqlFiddle here

Edit
If you want to eliminate duplicates, first pass the date data through a DISTINCT filter (e.g. a CTE or Derived Table).

WITH cteUniqueDates AS
(
  SELECT DISTINCT DateFrom
  FROM SomeTable
)
SELECT
  DateFrom, 
  DATEADD(dd, -1, LEAD(DateFrom) OVER (ORDER BY DateFrom)) AS DateTo
FROM cteUniqueDates;

Updated Fiddle

Upvotes: 1

Related Questions