sql - 根据当前行的条件计算前 24 个月的行

Question

如何根据 principalreliefflag 从当前行计算前 24 个月（它应该基于每一行（每个月与前 24 个月）。所以从当前行计算主要标志（Y）到前 24 个月。）？

我拥有的数据：

我需要的数据：

代码：

       -------------------------------------------------------------------------
----Identify customers who are on principal relief more than one month
-------------------------------------------------------------------------

IF OBJECT_ID('tempdb..##PRappliedmorethan_once') IS NOT NULL
    DROP TABLE ##PRappliedmorethan_once
select *,
case when  PrincipalReliefFlag='Y'  then 1 else 0
                                      end  PR_applied_months

 into ##PRappliedmorethan_once
 from ##TL_details_dates2 



 IF OBJECT_ID('tempdb..##PRappliedmorethan_once1') IS NOT NULL
    DROP TABLE ##PRappliedmorethan_once1
select *,
--Identify customers who have applied for principal relief within the past 12 months



sum(PR_applied_months) over    (partition by productitemcode, productcode
                                      order by dim_snapshotdate_key
                                      rows between  23 preceding and current row
                                     )
                                      abcd





 into ##PRappliedmorethan_once1
 from ##PRappliedmorethan_once

它有效，但有没有更好的方法？

标签： sqltsql

Answer 1

尝试使用该DATEDIFF()功能检查过去 24 个月，例如：

SELECT COUNT(*) FROM YOUR_TABLE 
WHERE DATEDIFF(month, current_row_date, date_to_be_compared) <= 24