sql - Aging - Calendar and Business Days Different Records of Data -
i using sql server 2012 , have aging question. tried include sample data below 4 records.
filenumber filetype completeddate 90440 internal 8/11/2017 90440 strategy null 90441 internal 8/10/2017 90441 strategy null
a strategies' aging calculated internal filetype's completed date way strategy filetype's completed date. every filenumber can have multiple filetypes associated it. if strategy's completed date null, internal filetype's completed date way today's date or getdate().
so i'm trying show count of pending strategies , current aging in business , calendar days...so want data return this.
file number filetype agebusiness agecalendar 90440 strategy 2 4 90441 strategy 3 5
any clue on how go this? appreciated.
something may work i'm not 100% sure since depends on sort of filetypes in table, whether accounting holidays, happens when strategy (or whatever latest file type) has value completed date.
;with internal ( select filenumber, filetype, datediff(day,completeddate,getdate())- (datediff(wk, completeddate, getdate()) * 2) - case when datepart(dw, completeddate) = 1 1 else 0 end + case when datepart(dw, getdate()) = 1 1 else 0 end agebusiness, datediff(day,completeddate,getdate()) agecalendar @test filetype = 'internal' ) select t.filenumber, t.filetype, i.agebusiness, i.agecalendar @test t inner join internal on (t.filenumber = i.filenumber) completeddate null
the problem above wouldn't show row if strategy (or whatever latest file type) has completed date. therefore may want like
;with internal ( select filenumber, filetype, datediff(day,completeddate,getdate())- (datediff(wk, completeddate, getdate()) * 2) - case when datepart(dw, completeddate) = 1 1 else 0 end + case when datepart(dw, getdate()) = 1 1 else 0 end agebusiness, datediff(day,completeddate,getdate()) agecalendar @test filetype = 'internal' ), latest ( select filenumber, filetype, rank() on (partition filenumber order coalesce(completeddate,getdate()) desc) rnk @test ) select l.filenumber, l.filetype, i.agebusiness, i.agecalendar latest l inner join internal on (l.filenumber = i.filenumber) rnk = 1
where show row regardless of if it's null or not, @ expense of adding rank() more intensive.
Comments
Post a Comment