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
JuanchoVzla

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

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros