I have a query in which I need to perform on various portfolios. i need to set the portinfoid and then change it several times to run it on each portfolio that I want to update. Any ideas on how to do this without manually changing the portinfoid and rerunning the query each time I change the portinfoid value. I would like this process to be automatic by passing my set of portinfoids into the query and having to run my script below only once.
declare @portinfoid int
declare @WindRCCap float
declare @EQResAdj float
declare @HUResAdj float
declare @TOResAdj float
declare @EQComAdj float
declare @HUComAdj float
declare @TOComAdj float
declare @EQAutoAdj float
declare @HUAutoAdj float
declare @TOAutoAdj float
/*******************************
Define Portfolio Number
*******************************/
set @portinfoid =11
/*******************************
Define all Adjustment Factors
*******************************/
/*********** RESIDENTIAL REPLACEMENT COST CAP **********/
set @WindRCCap = 1
/*********** RESIDENTIAL ADJUSTMENT FACTORS ************/
set @EQResAdj = 1.365
set @HUResAdj = 1.167
set @TOResAdj = 1.266
/************ COMMERCIAL ADJUSTMENT FACTORS ************/
set @EQComAdj = 1.556
set @HUComAdj = 1.135
set @TOComAdj = 1.362
/************ AUTOMOBILE ADJUSTMENT FACTORS ************/
set @EQAutoAdj = 1.037
set @HUAutoAdj = 1.037
Set @TOAutoAdj = 1.037
/*
select sum(valueamt), sum(limitamt), peril, labelid from loccvg group by peril, labelid
select sum(sitelimamt) from todet
select sum(sitelimamt) from eqdet
select sum(sitelimamt) from hudet
select sum(blanlimamt) from policy
select distinct occtype, labelid, sum (limitamt) from loccvg as a join loc as b on a.locid = b.locid
group by occtype, labelid
--LIMIT AND VALUE CHECK
--LIMIT ADJUSTMENTS
--Update Res Blanket limits =0
update policy
set blanlimamt = 0
where accgrpid in (select accgrpid from loc where occtype in (0,1,2, 40,41,42,43))
and accgrpid in (select accgrpid from portacct where portinfoid = @portinfoid)
--Strip out Sitelimamt
update hudet
set sitelimamt = 0
where locid in (select locid from loc where occtype in (0,1,2, 40,41,43))
and locid in (select locid from loc where accgrpid in (select accgrpid from portacct where portinfoid = @portinfoid))
update todet
set sitelimamt = 0
where locid in (select locid from loc where occtype in (0,1,2, 40,41,43))
and locid in (select locid from loc where accgrpid in (select accgrpid from portacct where portinfoid = @portinfoid))
update eqdet
set sitelimamt = 0
where locid in (select locid from loc where occtype in (0,1,2, 40,41,43))
and locid in (select locid from loc where accgrpid in (select accgrpid from portacct where portinfoid = @portinfoid))
--update Building Residential loccvg limits, both hurricane and tornado
update loccvg set limitamt = limitamt * @WindRCCap
where labelid in (24,28,32) and locid in (select locid from loc where occtype in (0,1,2, 40,41,43))
and locid in (select locid from loc where accgrpid in (select accgrpid from portacct where portinfoid = @portinfoid))*/
--VALUE ADJUSTMENTS
/***********RESIDENTIAL************/
--earthquake residential adjustment
update loccvg set valueamt = valueamt * @EQResAdj where peril = 1 and locid in
(select locid from loc where occtype in (0,1,2, 40,41,43) and bldgclass not in ('44','45','56'))
and locid in (select locid from loc where accgrpid in (select accgrpid from portacct where portinfoid = @portinfoid))
--hurricane residential adjustment
update loccvg set valueamt = valueamt * @HUResAdj where peril = 2 and locid in
(select locid from loc where occtype in (0,1,2, 40,41,43) and bldgclass not in ('44','45','56'))
and locid in (select locid from loc where accgrpid in (select accgrpid from portacct where portinfoid = @portinfoid))
--tornado residential adjustment
update loccvg set valueamt = valueamt * @TOResAdj where peril = 3 and locid in
(select locid from loc where occtype in (0,1,2, 40,41,43) and bldgclass not in ('44','45','56'))
and locid in (select locid from loc where accgrpid in (select accgrpid from portacct where portinfoid = @portinfoid))
/********COMMERCIAL***********/
--earthquake commercial adjustment
update loccvg set valueamt = valueamt * @EQComAdj where peril = 1 and locid in
(select locid from loc where occtype not in (0,1,2, 40,41,43) and bldgclass not in ('44','45','56'))
and locid in (select locid from loc where accgrpid in (select accgrpid from portacct where portinfoid = @portinfoid))
--hurricane commercial adjustment
update loccvg set valueamt = valueamt * @HUComAdj where peril = 2 and locid in
(select locid from loc where occtype not in (0,1,2, 40,41,43) and bldgclass not in ('44','45','56'))
and locid in (select locid from loc where accgrpid in (select accgrpid from portacct where portinfoid = @portinfoid))
--tornado commercial adjustment
update loccvg set valueamt = valueamt * @TOComAdj where peril = 3 and locid in
(select locid from loc where occtype not in (0,1,2, 40,41,43) and bldgclass not in ('44','45','56'))
and locid in (select locid from loc where accgrpid in (select accgrpid from portacct where portinfoid = @portinfoid))
/*****AUTO*****/
--earthquake auto adjustment
update loccvg set valueamt = valueamt * @EQAutoAdj where peril = 1 and locid in
(select locid from loc where bldgclass in ('44','45','56'))
and locid in (select locid from loc where accgrpid in (select accgrpid from portacct where portinfoid = @portinfoid))
--hurricane auto adjustment
update loccvg set valueamt = valueamt * @HUAutoAdj where peril = 2 and locid in
(select locid from loc where bldgclass in ('44','45','56'))
and locid in (select locid from loc where accgrpid in (select accgrpid from portacct where portinfoid = @portinfoid))
--tornado auto adjustment
update loccvg set valueamt = valueamt * @TOAutoAdj where peril = 3 and locid in
(select locid from loc where bldgclass in ('44','45','56'))
and locid in (select locid from loc where accgrpid in (select accgrpid from portacct where portinfoid = @portinfoid))
Our community of experts have been thoroughly vetted for their expertise and industry experience.