· 9 years ago · Oct 03, 2016, 05:22 PM
1 /*---------------------------------------------------
2 #Exceptions table is a lookup table for determining special behavior
3 AlternateCalculations = 1 means we should bypass normal calcs and use SR
4 ---------------------------------------------------*/
5 create table #Exceptions (PKID bigint, AlternateCalculations BIT)
6
7 insert #Exceptions
8 select ut.pkid,
9 case
10 when ((utp.Reviewer IS NOT NULL AND utp.Reviewer <> '')
11 AND (( utp.OverrideStreetRate is null or utp.OverrideStreetRate = 0)
12 AND (ut.SystemRecommendedStreetRate is not null and ut.SystemRecommendedStreetRate <> 0)))
13 then 1 -- if exists Reviewer AND (no OSR AND exists SRSR), then bypass normal calcs
14 else 0
15 end
16 from [ARCGUI].[tblUnitType] ut
17 join [ARCGUI].[tblUnitTypePersist] utp
18 on ut.FacNumber = utp.FacNumber
19 and ut.Dimensions = utp.Dimensions
20 and ut.Attributes = utp.Attributes
21
22 /*---------------------------------------------------
23 do calcs for bypassed unit types - use SR rates
24 ---------------------------------------------------*/
25 update ARCGUI.tblUnitType
26 SET FinalStreetRate = ROUND(ut.SystemRecommendedStreetRate,0),
27 FinalNetEffectiveRate = ut.SystemRecommendedNetEffectiveRate,
28 [FinalNetEffectiveRate/SquareFeet] = case when ut.SquareFootPerUnit = 0 then 0 else ut.SystemRecommendedNetEffectiveRate / ut.SquareFootPerUnit end,
29 FinalGPI = ut.SystemRecommendedStreetRate * ut.TotalUnits,
30 FinalNetEffectiveRatePercentChange = ut.SystemRecommendedPercentChange,
31 [NewPremium/DiscountToCompetitor] = case when ut.CompetitorAverageRate = 0 then 0 else (ut.SystemRecommendedStreetRate - ut.CompetitorAverageRate) / ut.CompetitorAverageRate END,
32 FinalStreetRatePerSquareFoot = case when ut.SquareFootPerUnit = 0 then ut.SystemRecommendedStreetRate else ut.SystemRecommendedStreetRate / ut.SquareFootPerUnit end
33 from [ARCGUI].[tblUnitType] ut
34 join [ARCGUI].[tblUnitTypePersist] utp
35 on ut.FacNumber = utp.FacNumber
36 and ut.Dimensions = utp.Dimensions
37 and ut.Attributes = utp.Attributes
38 join #Exceptions e
39 on ut.pkid = e.PKID
40 AND e.AlternateCalculations = 1
41
42 /*---------------------------------------------------
43 update tblUnitType - FinalStreetRate
44 ---------------------------------------------------*/
45 update ARCGUI.tblUnitType
46 set FinalStreetRate =
47 case -- evaluates top to bottom, first TRUE wins
48 when (utp.Reviewer is NULL OR utp.Reviewer = '')
49 then ROUND(ut.CurrentStreetRate,0) -- if no Reviewer, then CSR
50
51 when (utp.OverrideStreetRate is not null and utp.OverrideStreetRate <> 0)
52 then ROUND(utp.OverrideStreetRate,0) -- if exists Reviewer AND exists OSR, then OSR
53
54 when (utp.OverrideStreetRate is null or utp.OverrideStreetRate = 0)
55 and (ut.SystemRecommendedStreetRate is not null and ut.SystemRecommendedStreetRate <> 0)
56 then ROUND(ut.SystemRecommendedStreetRate,0) -- if exists Reviewer AND (no OSR AND exists SRSR), then SRSR
57
58 when (utp.OverrideStreetRate is null or utp.OverrideStreetRate = 0)
59 and (ut.SystemRecommendedStreetRate is null or ut.SystemRecommendedStreetRate = 0)
60 then ROUND(ut.CurrentStreetRate,0) -- if exists Reviewer AND (no OSR AND no SRSR), then CSR
61
62 else null -- otherwise, null
63 end
64 from [ARCGUI].[tblUnitType] ut
65 join [ARCGUI].[tblUnitTypePersist] utp
66 on ut.FacNumber = utp.FacNumber
67 and ut.Dimensions = utp.Dimensions
68 and ut.Attributes = utp.Attributes
69 join #Exceptions e
70 on ut.pkid = e.PKID
71 AND e.AlternateCalculations = 0
72
73 /*---------------------------------------------------
74 update tblUnitType - FinalNetEffectiveRate
75 depends on FinalStreetRate calc being done first
76 ---------------------------------------------------*/
77 update ARCGUI.tblUnitType
78 set FinalNetEffectiveRate =
79 case when ut.CurrentStreetRate = 0 then 0
80 else (ut.FinalStreetRate / ut.CurrentStreetRate) * ut.CurrentNetEffectiveRate end
81 from [ARCGUI].[tblUnitType] ut
82 join [ARCGUI].[tblUnitTypePersist] utp
83 on ut.FacNumber = utp.FacNumber
84 and ut.Dimensions = utp.Dimensions
85 and ut.Attributes = utp.Attributes
86 join #Exceptions e
87 on ut.pkid = e.PKID
88 AND e.AlternateCalculations = 0