CODEWITHSUNDEEP
sqlsql-server
baimzz
baimzz

Reputation: 41

SQL Server : aging based on last date

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?

Upvotes: 3

Views: 223

Answers (5)

Jakub Ojmucianski
Jakub Ojmucianski

Reputation: 307

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

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use max and partition by along with CTE

;with cte as (
select *, DateDiff(day, ED, LSD) as Ag, Max(Ed) over (partition by rn) MaxEd from #yourAging
) Select a.RN, a.LSD, a.ED, Aging =  sum(tmpSm) over(partition by a.rn) 
from  ( select *,case when max(ed) over (partition by rn)=ED then Ag else 0 end as TmpSm from cte ) a

Your Code demo with input tables

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

Your update will look like this:

update tab as t1
set aging = 
  (select (DATEDIFF(day, MIN(startdate), MAX(enddate))
   from tab as t2
   where t1.rn = t2.rn
  )

But you might do this dynamically in a view using

DATEDIFF(day, MIN(startdate) OVER (PARTITION BY RN), 
              MAX(enddate) OVER (PARTITION BY RN)) as Aging

Upvotes: 0

Sowmiya Raja Radhakrishnan
Sowmiya Raja Radhakrishnan

Reputation: 41

You Can Try This I guess. But I am not Clear with your requirement. The code you find below is not the shortest of the solutions, but will do the job.

SELECT X.RN , X.LSD, X.ED , B.Aging
FROM X 
LEFT JOIN
(
    SELECT RN , DATEDIFF(day,LSD,ED) Aging
    FROM
    (
        SELECT RN , MIN(LSD) LSD, MAX(ED) ED
        FROM X
        GROUP BY RN
    )A
)B
ON X.RN = B.RN

Upvotes: 0

NashPL
NashPL

Reputation: 481

I would recommend changing date format to yyyy-mm-dd as it makes easier in calculation and sorting. Then you should be able to work out the difference with SQL query.

Syntax 

DATEDIFF(datepart, startdate, enddate)

Example

SELECT DATEDIFF(day, '2014-08-05', '2014-06-05') AS DiffDate

Upvotes: 0

Related Questions