SQL Server : aging based on last date

Question

RN          LSD         ED          Aging
------------------------------------------
11111111    09-Feb-2017 06-Feb-2017  3
22222222    09-Feb-2017 02-Feb-2017  7
33333333    20-Jan-2017 29-Nov-2016 44
33333333    20-Jan-2017 07-Dec-2016 44
33333333    20-Jan-2017 29-Nov-2016 44
33333333    20-Jan-2017 30-Nov-2016 44
33333333    20-Jan-2017 29-Nov-2016 44
44444444    12-Dec-2016 19-Nov-2016 17
44444444    12-Dec-2016 19-Nov-2016 17
44444444    12-Dec-2016 25-Nov-2016 17
55555555    07-Feb-2017 04-Dec-2016 56
55555555    07-Feb-2017 04-Dec-2016 56
55555555    07-Feb-2017 13-Dec-2016 56

How can we update aging column based on last (max) ED Date column by grouping by RN column?

Answer 1

I think the best way to achieve your goal is something like bellow

DECLARE @MyTable TABLE 
(
RN int,
LSD date,
ED date,
Aging int
)

INSERT INTO @MyTable VALUES
('11111111' ,'09-Feb-2017' ,'06-Feb-2017' ,'3'),
 ('22222222' ,'09-Feb-2017' ,'02-Feb-2017' ,'7' ),
 ('33333333' ,'20-Jan-2017' ,'29-Nov-2016' ,'44'),
 ('33333333' ,'20-Jan-2017' ,'07-Dec-2016' ,'44'), 
 ('33333333' ,'20-Jan-2017' ,'29-Nov-2016' ,'44'),
 ('33333333' ,'20-Jan-2017' ,'30-Nov-2016' ,'44') ,
 ('33333333' ,'20-Jan-2017' ,'29-Nov-2016' ,'44'),
 ('44444444' ,'12-Dec-2016' ,'19-Nov-2016' ,'17') ,
 ('44444444' ,'12-Dec-2016' ,'19-Nov-2016' ,'17'),
 ('44444444' ,'12-Dec-2016' ,'25-Nov-2016' ,'17'),
 ('55555555' ,'07-Feb-2017' ,'04-Dec-2016' ,'56') ,
 ('55555555' ,'07-Feb-2017' ,'04-Dec-2016' ,'56'),
 ('55555555' ,'07-Feb-2017' ,'13-Dec-2016' ,'56')


 ;with cteToUpdate AS
 (
 SELECT RN,DATEDIFF(D,MAX(ED),MIN(LSD)) as Aging FROM @MyTable
 GROUP BY RN
 )

UPDATE source
SET source.Aging = cteToUpdate.Aging
FROM @MyTable as source
JOIN cteToUpdate ON cteToUpdate.RN =source.RN