2nd highest date

	
	SELECT reg.employee_id, reg.date AttendDate,reg.inTime_date AttendInTime, 
outTime_date AS AttendOutTime, RANK() over (order by reg.date desc) as _Rank
INTO #otDate_T

 FROM 	employee_attendance_register_t reg WITH (NOLOCK)
 INNER JOIN #EmpIds_T ON reg.employee_id =#EmpIds_T.EmployeeID 
 INNER JOIN #AbsentDate_T ON reg.employee_id =#AbsentDate_T.employee_id AND #AbsentDate_T.OT > 0

WHERE MONTH(reg.date) = MONTH(@GeneralDutyDate) AND YEAR(reg.date) = YEAR(@GeneralDutyDate) 
AND reg.InFlg in ('P','L','AL') 
AND #AbsentDate_T.AbsentDate <> reg.date

ORDER BY CAST(LEFT(REG.total_ot,2) as int)  ASC

------SELECT * from #otDate_T

----*** Note: this gives the second largest value. If there are ties for first, then it gives the second value.
SELECT T.* INTO #OTTransferDate_T
FROM (SELECT #otDate_T.*, 
	dense_rank() over (partition by employee_id order by AbsentDate desc) as serial -- set serial for date 
      FROM #otDate_T
     ) t
WHERE serial = 2; -- 2nd highest date

Serial

    select EMPID, Name,Salary,  
    RANK() over (order by Salary desc) as _Rank,  
    DENSE_RANK () over (order by Salary desc) as DenseRank ,  
    ROW_NUMBER() over (order by Salary desc) as RowNumber from Employee  
    
    
    
   --ROW_NUMBER(): rank with duplicate records .
   --DENSE_RANK():Used with distinct key & will always generate a contiguous sequence of ranks like (1,2,3,..).
   --RANK(): will leave gaps after two or more rows with the same rank (1,3...)