vemula.narayanan - Friday, December 29, 2017 1:04 AM
Two suggestions, one for SQL Server 2008 and earlier, the other for 2012 and later
😎
Declare @Input Table(EmpID VARCHAR(50), from_value int, to_value int);
Insert @Input(EmpID, from_value,to_value) Values
('E1', 1, 4),
('E1', 5,null),
('E1', 7, Null);
--SQL SERVER 2008 AND EARLIER
;WITH BASE_DATA AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY IP.EmpID
ORDER BY IP.from_value ASC
) AS EMP_RID
,IP.EmpID
,IP.from_value
,IP.to_value
FROM @Input IP
)
SELECT
BD.EmpID
,BD.from_value
,ISNULL(BD.to_value,(B2.from_value - 1)) AS to_value
FROM BASE_DATA BD
LEFT OUTER JOIN BASE_DATA B2
ON BD.EMP_RID = (B2.EMP_RID - 1)
AND BD.EmpID = B2.EmpID;
-- SQL SERVER 2012 AND LATER
SELECT
IP.EmpID
,IP.from_value
,ISNULL( IP.to_value
,LEAD(IP.from_value,1,NULL) OVER
(
PARTITION BY IP.EmpID
ORDER BY IP.from_value
) - 1
) AS to_value
FROM @Input IP;