老龄化 - 日历和工作日不同的数据记录

I am currently using SQL Server 2012 and I have an aging question. I tried to include some sample data below with 4 records.

我目前正在使用SQL Server 2012，我有一个老化的问题。我试着将下面的一些样本数据包含在4条记录中。

FileNumber    FileType     CompletedDate
 90440        Internal      8/11/2017
 90440        Strategy       NULL
 90441        Internal      8/10/2017
 90441        Strategy       NULL

A Strategies' aging is calculated from the Internal FileType's Completed Date all the way up to the Strategy FileType's Completed Date. Every FileNumber can have multiple FileTypes associated with it. If the Strategy's Completed Date is NULL, then it would be from Internal FileType's Completed Date all the way up to today's date or GETDATE().

策略的老化是从内部文件类型的完成日期一直到策略文件类型的完成日期计算的。每个FileNumber都可以有多个与之关联的FileType。如果策略的完成日期为NULL，则它将从内部文件类型的完成日期一直到今天的日期或GETDATE（）。

So I'm trying to show Count of Pending Strategies and their current aging in Business and Calendar Days...so essentially I would want the data to return like this.

因此，我正在尝试显示“待定策略计数”以及它们在业务和日历日中的当前老化...所以基本上我希望数据像这样返回。

File Number    FileType    AgeBusiness  AgeCalendar
 90440          Strategy     2            4
 90441          Strategy     3            5

Any clue on how I would go about this? Any help is appreciated.

关于我如何解决这个问题的任何线索？任何帮助表示赞赏。

2 个解决方案

#1

Something like this may work but I'm not 100% sure since it depends on what sort of Filetypes are in the table, whether you are accounting for holidays, what happens when Strategy (or whatever is the latest file type) has a value for completed date.

这样的东西可能有用，但我不是100％肯定，因为它取决于表格中的Filetypes类型，是否考虑假期，当策略（或任何最新的文件类型）具有的值时会发生什么完成日期。

;WITH Internal AS
(
    select  FileNumber, FileType,
            DATEDIFF(day,CompletedDate,getdate())- (datediff(wk, CompletedDate, getdate()) * 2) -
                case when datepart(dw, CompletedDate) = 1 then 1 else 0 end +
                case when datepart(dw, getdate()) = 1 then 1 else 0 end as AgeBusiness,
            DATEDIFF(day,CompletedDate,getdate()) as AgeCalendar
    from @test
    where FileType = 'Internal'
)
select t.FileNumber, t.FileType, i.AgeBusiness, i.AgeCalendar
from @test t
inner join Internal i on
    (t.FileNumber = i.FileNumber)
where CompletedDate is null

the problem with the above is it wouldn't show a row if Strategy (or whatever is the latest file type) HAS a completed date. Therefore you may want something like

如果策略（或任何最新的文件类型）已完成日期，则上述问题是不会显示一行。因此你可能想要类似的东西

;WITH Internal AS
(
    select  FileNumber, FileType,
            DATEDIFF(day,CompletedDate,getdate())- (datediff(wk, CompletedDate, getdate()) * 2) -
                case when datepart(dw, CompletedDate) = 1 then 1 else 0 end +
                case when datepart(dw, getdate()) = 1 then 1 else 0 end as AgeBusiness,
            DATEDIFF(day,CompletedDate,getdate()) as AgeCalendar
    from @test
    where FileType = 'Internal'
), Latest AS
(
    select FileNumber, FileType, RANK() OVER   
        (PARTITION BY FileNumber ORDER BY COALESCE(CompletedDate,GETDATE()) DESC) AS rnk
    from @test
)
select l.FileNumber, l.FileType, i.AgeBusiness, i.AgeCalendar
from Latest l
inner join Internal i on
    (l.FileNumber = i.FileNumber)
where rnk = 1

where it would show the row regardless of if it's null or not, but at the expense of adding a RANK() which is more intensive.

它将显示行，无论它是否为null，但代价是添加更密集的RANK（）。

#2

You don't need a CTE but I included it to make it more readable.

您不需要CTE，但我将其包含在内以使其更具可读性。

WITH DateRanges AS (
SELECT yt.FileNumber
    ,yt.FileType
    ,SELECT TOP 1 CompletedDate FROM YourTable WHERE yt.FileNumber = FileNumber AND FileType = 'Internal'  AS InternalCompletedDate
    ,CASE WHEN yt.CompletedDate IS NULL THEN CAST(GETDATE() AS DATE) ELSE yt.CompletedDate END AS StrategyCompletedDate -- I forgot the NULL part too...
FROM YourTable yt
WHERE yt.FileType = 'Strategy'
)

SELECT FileNumber
    ,FileType
    ,(DATEDIFF(dd,InternalCompletedDate, StrategyCompletedDate) + 1)
  -(DATEDIFF(wk, InternalCompletedDate, StrategyCompletedDate) * 2)
  -(CASE WHEN DATENAME(dw, InternalCompletedDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, StrategyCompletedDate) = 'Saturday' THEN 1 ELSE 0 END) AS AgeBusiness    
    ,DATEDIFF(dd, InternalCompletedDate, StrategyCompletedDate) AS AgeCalendar
    FROM DateRanges

Edit: Should be good now. I rushed it a bit.

编辑：现在应该很好。我冲了一下。

#1

;WITH Internal AS
(
    select  FileNumber, FileType,
            DATEDIFF(day,CompletedDate,getdate())- (datediff(wk, CompletedDate, getdate()) * 2) -
                case when datepart(dw, CompletedDate) = 1 then 1 else 0 end +
                case when datepart(dw, getdate()) = 1 then 1 else 0 end as AgeBusiness,
            DATEDIFF(day,CompletedDate,getdate()) as AgeCalendar
    from @test
    where FileType = 'Internal'
)
select t.FileNumber, t.FileType, i.AgeBusiness, i.AgeCalendar
from @test t
inner join Internal i on
    (t.FileNumber = i.FileNumber)
where CompletedDate is null