计算到目前为止的滚动季度

Question

我正在尝试计算每个财年的“季度至今”值。我的数据集中的主要字段可以总结为：

客户ID、服务开始日期、服务结束日期、服务级别

我有一个函数，当交叉乘以开始和结束日期字段时，为每个月创建一条记录。请注意，我的财年从4月份开始。所以我的表看起来像这样：

Customer ID, Service Start Date, Service End Date, Service Level, Fiscal Year, Fiscal Quarter, Fiscal Month Start
12345, Jan 1, 2019, Apr 30, 2019, 93, Q4, Jan 2019
12345, Jan 1, 2019, Apr 30, 2019, 93, Q4, Feb 2019
12345, Jan 1, 2019, Apr 30, 2019, 93, Q4, Mar 2019
12345, Jan 1, 2019, Apr 30, 2019, 93, Q1, Apr 2019

我正在苦苦思索的是，我希望在一个会计年度内为客户的最高服务级别计算一个独特的季度。因此，我们希望将每个财年与该客户的最高服务级别进行分组。

例如:如果客户在2018年11月开始服务，服务级别为93,2019年5月结束服务，服务级别为94

如果此假想患者在1819财年期间切换了服务级别，我希望能够在以下财年计数内获得到目前为止的最后一个季度: Q3 2018: 1对于SRC94 Q4 2019: 2对于SRC94 Q1 2019: 1对于SRC94 Q2 2019: 1对于SRC94 Q3 2019:1对于SRC94

但如果他们在2019年4月改变服务级别(这是一个不同的财年)，我的计数将是

Q3 2018: 1 for SRC 93
Q4 2018: 2 for SRC 93
Q1 2019: 1 for SRC 94
Q2 2019: 1 for SRC 94
Q3 2019: 1 for SRC 94

到目前为止，我使用row_number函数得到的是每季度每个患者的“唯一计数”：

select ROW_NUMBER() OVER(PARTITION BY customer_ID, FISCALYEAR, Fiscalquarterstartdate ORDER BY fiscalmonthstartdate DESC) PtRnk
into #Temp

然后，我将选择所有ptRnk = 1的记录，然后将该ptrnk字段相加。

虽然我有点挣扎，但如何处理我上面概述的情况。感谢您的帮助。

Answer 1

使用子查询获取最高服务级别，然后执行简单的group by：

未经测试的

select T1.Customer_ID, T1.Fiscal_Quarter, T1.Fiscal_Year,
   count(*) Services_Received, T2.Service_Level
from YourTable T1
inner join (
   select Customer_ID, Fiscal_Year, max(Service_Level) Service_Level
   from YourTable
   group by Customer_ID, Fiscal_Year
) T2
   on T1.Customer_ID = T2.Customer_ID
   and T1.Fiscal_Year = T2.Fiscal_Year
group by T1.Customer_ID, T1.Fiscal_Quarter, T1.Fiscal_Year, T2.Service_Level