December 29, 2017 at 1:04 am
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);
Select * from @Input
Need out put like
EmpID from_value to_value
E1 1 4
E1 5 6
E1 7 NULL
Next from value of the employee is 7 so 7-1 =6. this 6 value will be the to_value of previous record
December 29, 2017 at 1:19 am
vemula.narayanan - Friday, December 29, 2017 1:04 AMDeclare @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);Select * from @Input
Need out put like
EmpID from_value to_value
E1 1 4
E1 5 6
E1 7 NULLNext from value of the employee is 7 so 7-1 =6. this 6 value will be the to_value of previous record
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;
December 29, 2017 at 1:26 am
Thanks a lot Eirikur:)
December 29, 2017 at 3:41 am
vemula.narayanan - Friday, December 29, 2017 1:26 AMThanks a lot Eirikur:)
You are welcome!
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply