SQL Server How to get previous row's value without LAG function

Question

This is a example of Lag function but i am looking for another example where i will get same result without using Lag function. please guide me.

DECLARE   @Employee TABLE  
   (  
        EmpCode VARCHAR(10),  
        EmpName   VARCHAR(10),  
        JoiningDate  DATE  
     )  
 INSERT INTO @Employee VALUES ('1', 'Rajendra', '1-Sep-2018')  
 INSERT INTO @Employee VALUES ('2', 'Manoj', '1-Oct-2018')  
 INSERT INTO @Employee VALUES ('3', 'Sonu', '10-Mar-2018')  
 INSERT INTO @Employee VALUES ('4', 'Kashish', '25-Oct-2018')  
 INSERT INTO @Employee VALUES ('5', 'Tim', '1-Dec-2018')  
 INSERT INTO @Employee VALUES ('6', 'Akshita', '1-Nov-2018')  
   
 SELECT * FROM   @Employee  
 SELECT *,   
        Lag(JoiningDate, 1) OVER(  
        ORDER BY JoiningDate ASC) AS EndDate  
 FROM @Employee  

Thanks

Answer 1

Hi TZacks-2728,

set dateformat ymd;  
  
DECLARE @Employee TABLE  
   (  
        EmpCode VARCHAR(10),  
        EmpName   VARCHAR(10),  
        JoiningDate  DATE  
     );  
  
 INSERT INTO @Employee VALUES ('1', 'Rajendra', '2018-09-01')  
 INSERT INTO @Employee VALUES ('2', 'Manoj', '2018-10-01')  
 INSERT INTO @Employee VALUES ('3', 'Sonu', '2018-03-10')  
 INSERT INTO @Employee VALUES ('4', 'Kashish', '2018-10-25')  
 INSERT INTO @Employee VALUES ('5', 'Tim', '2018-12-01')  
 INSERT INTO @Employee VALUES ('6', 'Akshita', '2018-11-01')  
  
   
 SELECT * FROM  @Employee;  
   
 SELECT *,   
        Lag(JoiningDate, 1) OVER(  
        ORDER BY JoiningDate ASC) AS EndDate  
 FROM @Employee;   
  
  
  
 WITH CTE AS (  
	SELECT *,    
	ROW_NUMBER() OVER (  
	ORDER BY JoiningDate  
	) row_num  
	FROM  @Employee  
)  
  
SELECT c1.EmpCode, c1.EmpName, c1.JoiningDate, c2.JoiningDate AS EndDate   
FROM CTE c1  
	LEFT JOIN CTE c2 ON (c1.row_num -1 = c2.row_num)  
  
   

Regards Jörg

Answer 2

Hi @T.Zacks
There are three solutions to get previous row's value without using LAG function.

1. You can use MAX or MIN along with OVER clause and add extra condition to it.
   The extra condition is "ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING" which will fetch previous row value.
   Check this:

   SELECT *,MIN(JoiningDate) OVER (ORDER BY JoiningDate ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS EndDate  
   FROM @Employee;  
   

2. Use LEFT JOIN along with on condition: Cur.Row_Number - 1 = Pre.Row_Number
   Check this：

   ;WITH CTE AS   
   (SELECT *,ROW_NUMBER()OVER(ORDER BY JoiningDate) AS RNum   
    FROM  @Employee  
   )  
   SELECT Cur.EmpCode, Cur.EmpName, Cur.JoiningDate, Pre.JoiningDate AS EndDate   
   FROM CTE Cur LEFT JOIN CTE Pre ON Cur.RNum - 1 = Pre.RNum  
   

3. Use OUTER APPLY
   Check this：

   SELECT  A.*,T.JoiningDate AS EndDate   
   FROM @Employee AS A    
   OUTER APPLY (SELECT TOP 1 JoiningDate FROM @Employee AS B  
             WHERE B.JoiningDate < A.JoiningDate  
             ORDER BY B.JoiningDate DESC) AS T  
   ORDER BY JoiningDate ASC  
   

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

A correlated subquery will work:

 SELECT  t1.EmpCode,t1.EmpName,t1.JoiningDate,
(SELECT TOP 1 JoiningDate FROM @Employee AS t2
              WHERE t2.JoiningDate < t1.JoiningDate
              ORDER BY t2.JoiningDate DESC) AS EndDate
 FROM @Employee AS t1  
 ORDER BY t1.JoiningDate ASC

Answer 4

How to get previous row's

How do you define a "previous row"? Data don't have a natural order, so there is no previous as long as you don't define an order logic.