Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Looping in SQL

Avatar of rhorton78
rhorton78 asked on
Microsoft SQL Server 2008
5 Comments1 Solution426 ViewsLast Modified:
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))
 

                       



ASKER CERTIFIED SOLUTION
Avatar of JuanchoVzla
JuanchoVzlaFlag of Venezuela, Bolivarian Republic of image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answers