计算过去三年的车辆总数-解网

问：

我的查找表数据如下所示：数据库是 SQL Server。

policy_number	policy_eff_dt	lob_cd	Number_of_vehicles
1234	2022-10-12	自动	5
1234	2021-10-12	自动	6
1234	2020-10-12	自动	3
1234	2019-10-12	自动	2

我的源表提供以下过滤器列，分别是 Policy_number、policy_eff_dt LOB_CD

如果传入（源）值为 policy_number=1234，policy_eff_dt=2022-10-12，loc_cd=AUTO

那么我应该确定前三年（2021、2020、2019），并将（6+3+2） = 11 总结为PREV3_TOTAL_NBR_OF_VEHICLES。

我的筛选条件是

in_policy_number = policy_number and

in_policy_eff_dt <= policy_eff_dt and

in_lob_cd = lob_cd

这样，我将获得 2021 年、2020 年和 2019 年）

我想到了子查询来获取前 3 年的行。我在这里需要某种帮助。

有没有办法编写查询来计算车辆总数？

注意：这是开发的初始查询：

select 
   policy_num, lob_cd, sum(number_of_vehicles) 
from table1 
where policy_num='S 2455350'  
    and lob_cd = 'AU'  
    and year(policy_eff_dt) >= '2022'-3  
    and year(policy_eff_dt) <= '2022' 
group by policy_num , lob_cd

新 SQL：

select A.policy_num, A.lob_cd, A.policy_eff_dt,sum(NUM1+NUM2+NUM3) as PREV3_NUMBER_OF_VEHICLES from (select  policy_num, lob_cd,curr. A.policy_eff_dt,  case when year (curr.policy_eff_dt) - year (prev.policy_eff_dt) =1 then prev.number_of_vehicles else 0 end) as NUM1, case when year (curr.policy_eff_dt) - year (prev.policy_eff_dt) =2 then prev.number_of_vehicles else 0 end as NUM2, case when year(curr.policy_eff_dt) - year (prev.policy_eff_dt) =3 then prev.number_of_vehicles else 0 end as NUM3 from table1 curr 
     left join table1 prev  on curr.policy_num=prev.policy_num  and curr.lob_cd = prev.lob_cd where curr.policy_num='S 2455350'  
    and curr.lob_cd = 'AU'  ) A

按A.policy_num、A.lob_cd、A.policy_eff_dt分组

sql sql-server sql-server-2012

drop table if exists table1
go
create table table1 (
    policy_number int,
    policy_eff_dt date,
    lob_cd char(4),
    Number_of_vehicles int)
go
INSERT into table1
values (1234, '2022-10-12', 'AUTO', 5),
(1234, '2021-10-12', 'AUTO', 6),
(1234, '2020-10-12', 'AUTO', 3),
(1234, '2019-10-12', 'AUTO', 2)
GO
select a.*,
       prev3_total_nbr_of_vehicles,
       case
            when p.total_prev_years = 3 then p.prev3_total_nbr_of_vehicles
            else 0 end strict_prev3_total_nbr_of_vehicles
  from table1 a
 outer apply (   select sum(Number_of_vehicles) prev3_total_nbr_of_vehicles,
                        count(1) total_prev_years
                   from table1 b
                  where a.policy_number = b.policy_number
                    and a.lob_cd        = b.lob_cd
                    and YEAR(b.policy_eff_dt) BETWEEN YEAR(a.policy_eff_dt) - 3 and YEAR(a.policy_eff_dt) - 1) p
GO

1赞 Zorkolot 10/21/2023 #2

我会通过使用声明的变量预先简化它来解决这个问题。一般来说，我会计算出保单编号的最大保单日期，然后为 3 年前的开始日期添加 -3 年。这样做的好处是，通过避免 Year（）函数，最终查询是可优化的。

--Specify the policy_number
DECLARE @policynum int = 1234;

--Get the most recent policy_eff_dt for the policynum
DECLARE @MaxPolicyDate date = (SELECT MAX(policy_eff_dt) WHERE policy_number = @policynum);

--Get start date 3 years prior
DECLARE @startdate date = DATEADD(YEAR, -3, @MaxPolicyDate);


--Main Query
SELECT policy_num
      ,lob_cd
      ,SUM(Number_of_vehicles) AS [PREV3_TOTAL_NBR_OF_VEHICLES]
  FROM table1
 WHERE policy_num = @policynum
   AND policy_eff_dt >= @startdate
   AND Policy_eff_dt < @MaxPolicyDate

GROUP BY policy_num,lob_cd

上一个：排除具有部分匹配条件的记录

下一个：WHERE 子句中的 Date CAST 不返回项目

计算过去三年的车辆总数

Calculate last three years total vehicle count

评论