question

A simple way is to assign a row number based on the dates (as I'm presuming that's the intended order) and then self-join to get the offset rows by 1 declare @YourTable table (EmpId int, TransactionDate datetime) insert into @YourTable select 1, '1 Jan 2014' insert into @YourTable select 1, '2 Feb 2014' insert into @YourTable select 1, '1 Mar 2014' ;with cte as ( select row_number()over(order by TransactionDate asc) as rn, EmpId, TransactionDate from @YourTable ) select c1.EmpId, c1.TransactionDate as StartDate, c2.TransactionDate as EndDate from cte c1 left join cte c2 on c1.rn = c2.rn-1 This approach doesn't take into account any change in EmpId, nor will it be fast for large tables.