Created
December 16, 2016 19:26
-
-
Save jeremysimmons/f9a88135b857c5ef875adce9514118f0 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH | |
| cte1 AS | |
| ( | |
| SELECT | |
| HeaderID | |
| ,An_ID | |
| ,Year | |
| ,YearSequence | |
| ,Value | |
| ,ValueType | |
| ,IsLoss | |
| ,Amounts | |
| ,TAmount | |
| ,TypeCodeID | |
| ,IsReviewed | |
| FROM schema.Header | |
| ) , | |
| cte6 AS | |
| ( | |
| SELECT | |
| a.HeaderID | |
| , a.An_ID | |
| , a.YearSequence | |
| , x.ELevel | |
| , x.EIsValid | |
| FROM schema.Header a | |
| JOIN schema.HeaderExcessive x | |
| ON a.An_ID = x.An_ID | |
| ) | |
| , cte10 AS | |
| ( | |
| SELECT | |
| a.HeaderID | |
| , a.An_ID | |
| , a.YearSequence | |
| , r.RvCustomerNet | |
| , r.RvAverage | |
| , r.RvCustomerShare | |
| , r.RvShareEquivalent | |
| , r.RSAdjustedRv | |
| , r.SkipRS | |
| FROM schema.Header a | |
| JOIN schema.HeaderRv r | |
| ON a.An_ID = r.An_ID | |
| ) | |
| , cte11 AS | |
| ( | |
| SELECT | |
| a.HeaderID | |
| , a.An_ID | |
| , a.YearSequence | |
| ,SkipsFactor | |
| ,SkipsFactorOther | |
| ,SkipsValue | |
| ,RowWidth | |
| ,SkipsCode | |
| ,SkipsPattern | |
| FROM schema.Header a | |
| JOIN schema.HeaderSkips s | |
| ON a.An_ID = s.An_ID | |
| ) | |
| , cte12 AS | |
| ( | |
| SELECT | |
| a.HeaderID | |
| , a.An_ID | |
| , a.YearSequence | |
| , y.YTAmount | |
| , y.YTAmount | |
| , y.YSkip | |
| , y.Y2Skip | |
| , y.YAdjust | |
| , y.RvValue | |
| FROM schema.Header a | |
| JOIN schema.HeaderValueAdj y | |
| ON a.An_ID = y.An_ID | |
| ) | |
| SELECT DISTINCT | |
| Header.HeaderID AS ID | |
| , Header.RecordID AS RecordID | |
| , ISNULL(ValueIndicator,'') AS ValueIndicator | |
| , TValue | |
| , FSAValue | |
| , ApprovedValue | |
| , PreviousValue | |
| , ISNULL(Year1,0) AS Year1 | |
| , ISNULL(ValueType1,'') AS ValueType1 | |
| , ISNULL(Value1,0) AS Value1 | |
| , ISNULL(Amounts1,0) AS Amounts1 | |
| , ISNULL(RvValue1,0) AS RvValue1 | |
| , ISNULL(Year2,0) AS Year2 | |
| , ISNULL(ValueType2,'') AS ValueType2 | |
| , ISNULL(Value2,0) AS Value2 | |
| , ISNULL(Amounts2,0) AS Amounts2 | |
| , ISNULL(RvValue2,0) AS RvValue2 | |
| , ISNULL(Year3,0) AS Year3 | |
| , ISNULL(ValueType3,'') AS ValueType3 | |
| , ISNULL(Value3,0) AS Value3 | |
| , ISNULL(Amounts3,0) AS Amounts3 | |
| , ISNULL(RvValue3,0) AS RvValue3 | |
| , ISNULL(Year4,0) AS Year4 | |
| , ISNULL(ValueType4,'') AS ValueType4 | |
| , ISNULL(Value4,0) AS Value4 | |
| , ISNULL(Amounts4,0) AS Amounts4 | |
| , ISNULL(RvValue4,0) AS RvValue4 | |
| , ISNULL(Year5,0) AS Year5 | |
| , ISNULL(ValueType5,'') AS ValueType5 | |
| , ISNULL(Value5,0) AS Value5 | |
| , ISNULL(Amounts5,0) AS Amounts5 | |
| , ISNULL(RvValue5,0) AS RvValue5 | |
| , ISNULL(Year6,0) AS Year6 | |
| , ISNULL(ValueType6,'') AS ValueType6 | |
| , ISNULL(Value6,0) AS Value6 | |
| , ISNULL(Amounts6,0) AS Amounts6 | |
| , ISNULL(RvValue6,0) AS RvValue6 | |
| , ISNULL(Year7,0) AS Year7 | |
| , ISNULL(ValueType7,'') AS ValueType7 | |
| , ISNULL(Value7,0) AS Value7 | |
| , ISNULL(Amounts7,0) AS Amounts7 | |
| , ISNULL(RvValue7,0) AS RvValue7 | |
| , ISNULL(Year8,0) AS Year8 | |
| , ISNULL(ValueType8,'') AS ValueType8 | |
| , ISNULL(Value8,0) AS Value8 | |
| , ISNULL(Amounts8,0) AS Amounts8 | |
| , ISNULL(RvValue8,0) AS RvValue8 | |
| , ISNULL(Year9,0) AS Year9 | |
| , ISNULL(ValueType9,'') AS ValueType9 | |
| , ISNULL(Value9,0) AS Value9 | |
| , ISNULL(Amounts9,0) AS Amounts9 | |
| , ISNULL(RvValue9,0) AS RvValue9 | |
| , ISNULL(Year10,0) AS Year10 | |
| , ISNULL(ValueType10,'') AS ValueType10 | |
| , ISNULL(Value10,0) AS Value10 | |
| , ISNULL(Amounts10,0) AS Amounts10 | |
| , ISNULL(RvValue10,0) AS RvValue10 | |
| , ISNULL(ValueLimitation,1) AS ValueLimitation | |
| , ExcessiveBypass | |
| , ISNULL(NumYears,0) AS NumYears | |
| , ISNULL(AverageValue,0) AS AverageValue | |
| , ISNULL(ValueIndex,0) AS ValueIndex | |
| , ISNULL(OptionCodes,'') AS OptionCodes | |
| , ISNULL(PrevValueLimitation,0) AS PrevValueLimitation | |
| , Updated | |
| , ISNULL(MapCode,'') AS MapCode | |
| , ISNULL(SkipsPattern1,'') AS SkipsPattern1 | |
| , ISNULL(SkipsCode1,0) AS SkipsCode1 | |
| , ISNULL(RowWidth1,0) AS RowWidth1 | |
| , ISNULL(SkipsPattern2,'') AS SkipsPattern2 | |
| , ISNULL(SkipsCode2,0) AS SkipsCode2 | |
| , ISNULL(RowWidth2,0) AS RowWidth2 | |
| , ISNULL(SkipsPattern3,'') AS SkipsPattern3 | |
| , ISNULL(SkipsCode3,0) AS SkipsCode3 | |
| , ISNULL(RowWidth3,0) AS RowWidth3 | |
| , ISNULL(SkipsPattern4,'') AS SkipsPattern4 | |
| , ISNULL(SkipsCode4,0) AS SkipsCode4 | |
| , ISNULL(RowWidth4,0) AS RowWidth4 | |
| , ISNULL(SkipsPattern5,'') AS SkipsPattern5 | |
| , ISNULL(SkipsCode5,0) AS SkipsCode5 | |
| , ISNULL(RowWidth5,0) AS RowWidth5 | |
| , ISNULL(SkipsPattern6,'') AS SkipsPattern6 | |
| , ISNULL(SkipsCode6,0) AS SkipsCode6 | |
| , ISNULL(RowWidth6,0) AS RowWidth6 | |
| , ISNULL(SkipsPattern7,'') AS SkipsPattern7 | |
| , ISNULL(SkipsCode7,0) AS SkipsCode7 | |
| , ISNULL(RowWidth7,0) AS RowWidth7 | |
| , ISNULL(SkipsPattern8,'') AS SkipsPattern8 | |
| , ISNULL(SkipsCode8,0) AS SkipsCode8 | |
| , ISNULL(RowWidth8,0) AS RowWidth8 | |
| , ISNULL(SkipsPattern9,'') AS SkipsPattern9 | |
| , ISNULL(SkipsCode9,0) AS SkipsCode9 | |
| , ISNULL(RowWidth9,0) AS RowWidth9 | |
| , ISNULL(SkipsPattern10,'') AS SkipsPattern10 | |
| , ISNULL(SkipsCode10,0) AS SkipsCode10 | |
| , ISNULL(RowWidth10,0) AS RowWidth10 | |
| , ISNULL(PHTS,0) AS PHTS | |
| , CONVERT(TINYINT, ISNULL(IsLoss1,0)) AS IsLoss1 | |
| , CONVERT(TINYINT, ISNULL(IsLoss2,0)) AS IsLoss2 | |
| , CONVERT(TINYINT, ISNULL(IsLoss3,0)) AS IsLoss3 | |
| , CONVERT(TINYINT, ISNULL(IsLoss4,0)) AS IsLoss4 | |
| , CONVERT(TINYINT, ISNULL(IsLoss5,0)) AS IsLoss5 | |
| , CONVERT(TINYINT, ISNULL(IsLoss6,0)) AS IsLoss6 | |
| , CONVERT(TINYINT, ISNULL(IsLoss7,0)) AS IsLoss7 | |
| , CONVERT(TINYINT, ISNULL(IsLoss8,0)) AS IsLoss8 | |
| , CONVERT(TINYINT, ISNULL(IsLoss9,0)) AS IsLoss9 | |
| , CONVERT(TINYINT, ISNULL(IsLoss10,0)) AS IsLoss10 | |
| , ISNULL(Code,0) AS Code | |
| , ISNULL(Type,0) AS Type | |
| , ISNULL(TAvgSimp,0) AS TAvgSimp | |
| , ISNULL(UpdatedBy,0) AS UpdatedBy | |
| , CONVERT(TINYINT, ISNULL(Deleted,0)) AS Deleted | |
| , ISNULL(TAct,0) AS TAct | |
| , CONVERT(TINYINT, ISNULL(YCNA,0)) AS YCNA | |
| , ISNULL(CountYears,0) AS CountYears | |
| , ISNULL(ApprovTemp,0) AS ApprovTemp | |
| , CONVERT(TINYINT, ISNULL(YAdjust1,0)) AS YAdjust1 | |
| , CONVERT(TINYINT, ISNULL(YAdjust2,0)) AS YAdjust2 | |
| , CONVERT(TINYINT, ISNULL(YAdjust3,0)) AS YAdjust3 | |
| , CONVERT(TINYINT, ISNULL(YAdjust4,0)) AS YAdjust4 | |
| , CONVERT(TINYINT, ISNULL(YAdjust5,0)) AS YAdjust5 | |
| , CONVERT(TINYINT, ISNULL(YAdjust6,0)) AS YAdjust6 | |
| , CONVERT(TINYINT, ISNULL(YAdjust7,0)) AS YAdjust7 | |
| , CONVERT(TINYINT, ISNULL(YAdjust8,0)) AS YAdjust8 | |
| , CONVERT(TINYINT, ISNULL(YAdjust9,0)) AS YAdjust9 | |
| , CONVERT(TINYINT, ISNULL(YAdjust10,0)) AS YAdjust10 | |
| , ISNULL(IsReviewed8,0) AS IsReviewed8 | |
| , ISNULL(IsReviewed9,0) AS IsReviewed9 | |
| , ISNULL(IsReviewed10,0) AS IsReviewed10 | |
| , ISNULL(IsReviewed1,0) AS IsReviewed1 | |
| , ISNULL(IsReviewed2,0) AS IsReviewed2 | |
| , ISNULL(IsReviewed3,0) AS IsReviewed3 | |
| , ISNULL(IsReviewed4,0) AS IsReviewed4 | |
| , ISNULL(IsReviewed5,0) AS IsReviewed5 | |
| , ISNULL(IsReviewed6,0) AS IsReviewed6 | |
| , ISNULL(IsReviewed7,0) AS IsReviewed7 | |
| , ISNULL(ApprovedValueOverride,0) AS ApprovedValueOverride | |
| , ISNULL(ApprovedValueOverrideLimitationFlag,0) AS ApprovedValueOverrideLimitationFlag | |
| , ISNULL(APHProceduralExceptionCode,'') AS APHProceduralExceptionCode | |
| , ISNULL(ELevel1,0) AS ELevel1 | |
| , ISNULL(ELevel2,0) AS ELevel2 | |
| , ISNULL(ELevel3,0) AS ELevel3 | |
| , ISNULL(ELevel4,0) AS ELevel4 | |
| , ISNULL(ELevel5,0) AS ELevel5 | |
| , ISNULL(ELevel6,0) AS ELevel6 | |
| , ISNULL(ELevel7,0) AS ELevel7 | |
| , ISNULL(ELevel8,0) AS ELevel8 | |
| , ISNULL(ELevel9,0) AS ELevel9 | |
| , ISNULL(ELevel10,0) AS ELevel10 | |
| , CONVERT(TINYINT, ISNULL(EIsValid1,0)) AS EIsValid1 | |
| , CONVERT(TINYINT, ISNULL(EIsValid2,0)) AS EIsValid2 | |
| , CONVERT(TINYINT, ISNULL(EIsValid3,0)) AS EIsValid3 | |
| , CONVERT(TINYINT, ISNULL(EIsValid4,0)) AS EIsValid4 | |
| , CONVERT(TINYINT, ISNULL(EIsValid5,0)) AS EIsValid5 | |
| , CONVERT(TINYINT, ISNULL(EIsValid6,0)) AS EIsValid6 | |
| , CONVERT(TINYINT, ISNULL(EIsValid7,0)) AS EIsValid7 | |
| , CONVERT(TINYINT, ISNULL(EIsValid8,0)) AS EIsValid8 | |
| , CONVERT(TINYINT, ISNULL(EIsValid9,0)) AS EIsValid9 | |
| , CONVERT(TINYINT, ISNULL(EIsValid10,0)) AS EIsValid10 | |
| , CONVERT(TINYINT, ISNULL(ExcludeFromSAT,0)) AS ExcludeFromSAT | |
| , ISNULL(TAmount1,0) AS TAmount1 | |
| , ISNULL(TAmount2,0) AS TAmount2 | |
| , ISNULL(TAmount3,0) AS TAmount3 | |
| , ISNULL(TAmount4,0) AS TAmount4 | |
| , ISNULL(TAmount5,0) AS TAmount5 | |
| , ISNULL(TAmount6,0) AS TAmount6 | |
| , ISNULL(TAmount7,0) AS TAmount7 | |
| , ISNULL(TAmount8,0) AS TAmount8 | |
| , ISNULL(TAmount9,0) AS TAmount9 | |
| , ISNULL(TAmount10,0) AS TAmount10 | |
| , ISNULL(TAmount,0) AS TAmount | |
| , ISNULL(TAmountDisplay,0) AS TAmountDisplay | |
| , ISNULL(TATrend,0) AS TATrend | |
| , ISNULL(TAFactor,0) AS TAFactor | |
| , ISNULL(YTAmount1,0) AS YTAmount1 | |
| , ISNULL(YTAmount2,0) AS YTAmount2 | |
| , ISNULL(YTAmount3,0) AS YTAmount3 | |
| , ISNULL(YTAmount4,0) AS YTAmount4 | |
| , ISNULL(YTAmount5,0) AS YTAmount5 | |
| , ISNULL(YTAmount6,0) AS YTAmount6 | |
| , ISNULL(YTAmount7,0) AS YTAmount7 | |
| , ISNULL(YTAmount8,0) AS YTAmount8 | |
| , ISNULL(YTAmount9,0) AS YTAmount9 | |
| , ISNULL(YTAmount10,0) AS YTAmount10 | |
| , ISNULL(YTAmount1,0) AS YTAmount1 | |
| , ISNULL(YTAmount2,0) AS YTAmount2 | |
| , ISNULL(YTAmount3,0) AS YTAmount3 | |
| , ISNULL(YTAmount4,0) AS YTAmount4 | |
| , ISNULL(YTAmount5,0) AS YTAmount5 | |
| , ISNULL(YTAmount6,0) AS YTAmount6 | |
| , ISNULL(YTAmount7,0) AS YTAmount7 | |
| , ISNULL(YTAmount8,0) AS YTAmount8 | |
| , ISNULL(YTAmount9,0) AS YTAmount9 | |
| , ISNULL(YTAmount10,0) AS YTAmount10 | |
| , ISNULL(TATAvgSimp,0) AS TATAvgSimp | |
| , ISNULL(ApprovedValueDisplay,0) AS ApprovedValueDisplay | |
| , ISNULL(ApprovedValueTARma,0) AS ApprovedValueTARma | |
| , CONVERT(TINYINT, ISNULL(YSkip,0)) AS YSkip | |
| , CONVERT(TINYINT, ISNULL(YSkip1,0)) AS YSkip1 | |
| , CONVERT(TINYINT, ISNULL(YSkip2,0)) AS YSkip2 | |
| , CONVERT(TINYINT, ISNULL(YSkip3,0)) AS YSkip3 | |
| , CONVERT(TINYINT, ISNULL(YSkip4,0)) AS YSkip4 | |
| , CONVERT(TINYINT, ISNULL(YSkip5,0)) AS YSkip5 | |
| , CONVERT(TINYINT, ISNULL(YSkip6,0)) AS YSkip6 | |
| , CONVERT(TINYINT, ISNULL(YSkip7,0)) AS YSkip7 | |
| , CONVERT(TINYINT, ISNULL(YSkip8,0)) AS YSkip8 | |
| , CONVERT(TINYINT, ISNULL(YSkip9,0)) AS YSkip9 | |
| , CONVERT(TINYINT, ISNULL(YSkip10,0)) AS YSkip10 | |
| , ISNULL(TValueChangePercent,0) AS TValueChangePercent | |
| , ISNULL(SkipsFactor1,0) AS SkipsFactor1 | |
| , ISNULL(SkipsFactor2,0) AS SkipsFactor2 | |
| , ISNULL(SkipsFactor3,0) AS SkipsFactor3 | |
| , ISNULL(SkipsFactor4,0) AS SkipsFactor4 | |
| , ISNULL(SkipsFactor5,0) AS SkipsFactor5 | |
| , ISNULL(SkipsFactor6,0) AS SkipsFactor6 | |
| , ISNULL(SkipsFactor7,0) AS SkipsFactor7 | |
| , ISNULL(SkipsFactor8,0) AS SkipsFactor8 | |
| , ISNULL(SkipsFactor9,0) AS SkipsFactor9 | |
| , ISNULL(SkipsFactor10,0) AS SkipsFactor10 | |
| , CONVERT(DECIMAL(10,2), ISNULL(SkipsValue1,0)) AS SkipsValue1 | |
| , CONVERT(DECIMAL(10,2), ISNULL(SkipsValue2,0)) AS SkipsValue2 | |
| , CONVERT(DECIMAL(10,2), ISNULL(SkipsValue3,0)) AS SkipsValue3 | |
| , CONVERT(DECIMAL(10,2), ISNULL(SkipsValue4,0)) AS SkipsValue4 | |
| , CONVERT(DECIMAL(10,2), ISNULL(SkipsValue5,0)) AS SkipsValue5 | |
| , CONVERT(DECIMAL(10,2), ISNULL(SkipsValue6,0)) AS SkipsValue6 | |
| , CONVERT(DECIMAL(10,2), ISNULL(SkipsValue7,0)) AS SkipsValue7 | |
| , CONVERT(DECIMAL(10,2), ISNULL(SkipsValue8,0)) AS SkipsValue8 | |
| , CONVERT(DECIMAL(10,2), ISNULL(SkipsValue9,0)) AS SkipsValue9 | |
| , CONVERT(DECIMAL(10,2), ISNULL(SkipsValue10,0)) AS SkipsValue10 | |
| , ISNULL(SkipsFactorOther1,0) AS SkipsFactorOther1 | |
| , ISNULL(SkipsFactorOther2,0) AS SkipsFactorOther2 | |
| , ISNULL(SkipsFactorOther3,0) AS SkipsFactorOther3 | |
| , ISNULL(SkipsFactorOther4,0) AS SkipsFactorOther4 | |
| , ISNULL(SkipsFactorOther5,0) AS SkipsFactorOther5 | |
| , ISNULL(SkipsFactorOther6,0) AS SkipsFactorOther6 | |
| , ISNULL(SkipsFactorOther7,0) AS SkipsFactorOther7 | |
| , ISNULL(SkipsFactorOther8,0) AS SkipsFactorOther8 | |
| , ISNULL(SkipsFactorOther9,0) AS SkipsFactorOther9 | |
| , ISNULL(SkipsFactorOther10,0) AS SkipsFactorOther10 | |
| , ISNULL(WithoutTAYLFlag,0) AS WithoutTAYLFlag | |
| , ISNULL(NBPercentOfT,1) AS NBPercentOfT | |
| , ISNULL(TypeCodeID1, 0) AS TypeCodeID1 | |
| , ISNULL(TypeCodeID2, 0) AS TypeCodeID2 | |
| , ISNULL(TypeCodeID3, 0) AS TypeCodeID3 | |
| , ISNULL(TypeCodeID4, 0) AS TypeCodeID4 | |
| , ISNULL(TypeCodeID5, 0) AS TypeCodeID5 | |
| , ISNULL(TypeCodeID6, 0) AS TypeCodeID6 | |
| , ISNULL(TypeCodeID7, 0) AS TypeCodeID7 | |
| , ISNULL(TypeCodeID8, 0) AS TypeCodeID8 | |
| , ISNULL(TypeCodeID9, 0) AS TypeCodeID9 | |
| , ISNULL(TypeCodeID10, 0) AS TypeCodeID10 | |
| , ISNULL(Y2Skip1,0) AS Y2Skip1 | |
| , ISNULL(Y2Skip2,0) AS Y2Skip2 | |
| , ISNULL(Y2Skip3,0) AS Y2Skip3 | |
| , ISNULL(Y2Skip4,0) AS Y2Skip4 | |
| , ISNULL(Y2Skip5,0) AS Y2Skip5 | |
| , ISNULL(Y2Skip6,0) AS Y2Skip6 | |
| , ISNULL(Y2Skip7,0) AS Y2Skip7 | |
| , ISNULL(Y2Skip8,0) AS Y2Skip8 | |
| , ISNULL(Y2Skip9,0) AS Y2Skip9 | |
| , ISNULL(Y2Skip10,0) AS Y2Skip10 | |
| , ISNULL(MaltingBarleyPQE, 0) AS MaltingBarleyPQE | |
| , HasValueExclusions | |
| , ValueFloorNA | |
| , ModifiedValue | |
| , ForceSimple | |
| , ISNULL(NewBreakingType, 0) AS NewBreakingType | |
| , ModifiedValueLimitation | |
| , ModifiedValueCup | |
| , InsurabilityCode | |
| , ISNULL(RvCustomerNet1,0) AS RvCustomerNet1 | |
| , ISNULL(RvAverage1,0) AS RvAverage1 | |
| , ISNULL(RvCustomerShare1,0) AS RvCustomerShare1 | |
| , ISNULL(RvShareEquivalent1,0) AS RvShareEquivalent1 | |
| , ISNULL(RvCustomerNet2,0) AS RvCustomerNet2 | |
| , ISNULL(RvAverage2,0) AS RvAverage2 | |
| , ISNULL(RvCustomerShare2,0) AS RvCustomerShare2 | |
| , ISNULL(RvShareEquivalent2,0) AS RvShareEquivalent2 | |
| , ISNULL(RvCustomerNet3,0) AS RvCustomerNet3 | |
| , ISNULL(RvAverage3,0) AS RvAverage3 | |
| , ISNULL(RvCustomerShare3,0) AS RvCustomerShare3 | |
| , ISNULL(RvShareEquivalent3,0) AS RvShareEquivalent3 | |
| , ISNULL(RvCustomerNet4,0) AS RvCustomerNet4 | |
| , ISNULL(RvAverage4,0) AS RvAverage4 | |
| , ISNULL(RvCustomerShare4,0) AS RvCustomerShare4 | |
| , ISNULL(RvShareEquivalent4,0) AS RvShareEquivalent4 | |
| , ISNULL(RvCustomerNet5,0) AS RvCustomerNet5 | |
| , ISNULL(RvAverage5,0) AS RvAverage5 | |
| , ISNULL(RvCustomerShare5,0) AS RvCustomerShare5 | |
| , ISNULL(RvShareEquivalent5,0) AS RvShareEquivalent5 | |
| , ISNULL(RvCustomerNet6,0) AS RvCustomerNet6 | |
| , ISNULL(RvAverage6,0) AS RvAverage6 | |
| , ISNULL(RvCustomerShare6,0) AS RvCustomerShare6 | |
| , ISNULL(RvShareEquivalent6,0) AS RvShareEquivalent6 | |
| , ISNULL(RvCustomerNet7,0) AS RvCustomerNet7 | |
| , ISNULL(RvAverage7,0) AS RvAverage7 | |
| , ISNULL(RvCustomerShare7,0) AS RvCustomerShare7 | |
| , ISNULL(RvShareEquivalent7,0) AS RvShareEquivalent7 | |
| , ISNULL(RvCustomerNet8,0) AS RvCustomerNet8 | |
| , ISNULL(RvAverage8,0) AS RvAverage8 | |
| , ISNULL(RvCustomerShare8,0) AS RvCustomerShare8 | |
| , ISNULL(RvShareEquivalent8,0) AS RvShareEquivalent8 | |
| , ISNULL(RvCustomerNet9,0) AS RvCustomerNet9 | |
| , ISNULL(RvAverage9,0) AS RvAverage9 | |
| , ISNULL(RvCustomerShare9,0) AS RvCustomerShare9 | |
| , ISNULL(RvShareEquivalent9,0) AS RvShareEquivalent9 | |
| , ISNULL(RvCustomerNet10,0) AS RvCustomerNet10 | |
| , ISNULL(RvAverage10,0) AS RvAverage10 | |
| , ISNULL(RvCustomerShare10,0) AS RvCustomerShare10 | |
| , ISNULL(RvShareEquivalent10,0) AS RvShareEquivalent10 | |
| , ISNULL(ModifiedRateValue, 0) AS ModifiedRateValue | |
| , ISNULL(RateRv,0) AS RateRv | |
| , ISNULL(AdjustedRv,0) AS AdjustedRv | |
| , ISNULL(ApprovedRv,0) AS ApprovedRv | |
| , ISNULL(TRv,0) AS TRv | |
| , ISNULL(PreviousRv,0) AS PreviousRv | |
| , ISNULL(RSAdjustedRv1,0) AS RSAdjustedRv1 | |
| , ISNULL(RSAdjustedRv2,0) AS RSAdjustedRv2 | |
| , ISNULL(RSAdjustedRv3,0) AS RSAdjustedRv3 | |
| , ISNULL(RSAdjustedRv4,0) AS RSAdjustedRv4 | |
| , ISNULL(RSAdjustedRv5,0) AS RSAdjustedRv5 | |
| , ISNULL(RSAdjustedRv6,0) AS RSAdjustedRv6 | |
| , ISNULL(RSAdjustedRv7,0) AS RSAdjustedRv7 | |
| , ISNULL(RSAdjustedRv8,0) AS RSAdjustedRv8 | |
| , ISNULL(RSAdjustedRv9,0) AS RSAdjustedRv9 | |
| , ISNULL(RSAdjustedRv10,0) AS RSAdjustedRv10 | |
| , ISNULL(SkipRS1,0) AS SkipRS1 | |
| , ISNULL(SkipRS2,0) AS SkipRS2 | |
| , ISNULL(SkipRS3,0) AS SkipRS3 | |
| , ISNULL(SkipRS4,0) AS SkipRS4 | |
| , ISNULL(SkipRS5,0) AS SkipRS5 | |
| , ISNULL(SkipRS6,0) AS SkipRS6 | |
| , ISNULL(SkipRS7,0) AS SkipRS7 | |
| , ISNULL(SkipRS8,0) AS SkipRS8 | |
| , ISNULL(SkipRS9,0) AS SkipRS9 | |
| , ISNULL(SkipRS10,0) AS SkipRS10 | |
| , ISNULL(SimpleAvgTRv,0) AS SimpleAvgTRv | |
| , ISNULL(ApprovedRvOverride,0) AS ApprovedRvOverride | |
| , ISNULL(NoLongerFarming,0) AS NoLongerFarming | |
| FROM | |
| ( | |
| SELECT | |
| h.HeaderID | |
| , h.RecordID | |
| , h.ValueIndicator AS ValueIndicator | |
| , h.TValue AS TValue | |
| , 0 AS FSAValue | |
| , h.ApprovedValue AS ApprovedValue | |
| , h.PreviousValue AS PreviousValue | |
| , h.ValueLimitation AS ValueLimitation | |
| , h.NumYears AS NumYears | |
| , h.AverageValue AS AverageValue | |
| , h.OptionCodes AS OptionCodes | |
| , h.PrevValueLimitation AS PrevValueLimitation | |
| , h.UpdatedOn AS Updated | |
| , h.MapCode AS MapCode | |
| , h.PHTS AS PHTS | |
| , h.CodeCode AS Code | |
| , h.TypeCode AS Type | |
| , h.TAvgSimp AS TAvgSimp | |
| , h.UpdatedBy AS UpdatedBy | |
| , h.IsDeleted AS Deleted | |
| , h.TAct AS TAct | |
| , h.YCNA AS YCNA | |
| , h.CountYears AS CountYears | |
| , h.ApprovTemp AS ApprovTemp | |
| , h.TAmount AS TAmount | |
| , h.TAmountDisplay AS TAmountDisplay | |
| , h.TATrend AS TATrend | |
| , h.TAFactor AS TAFactor | |
| , h.TATAvgSimp AS TATAvgSimp | |
| , h.ApprovedValueDisplay AS ApprovedValueDisplay | |
| , h.ApprovedValueTARma AS ApprovedValueTARma | |
| , h.TValueChangePercent AS TValueChangePercent | |
| , h.WithoutTAYLFlag AS WithoutTAYLFlag | |
| , h.NBPercentOfT AS NBPercentOfT | |
| , h.InsurabilityCode AS InsurabilityCode | |
| , h.ValueFloorNA AS ValueFloorNA | |
| , h.HasValueExclusions AS HasValueExclusions | |
| , h.ModifiedValue AS ModifiedValue | |
| , h.ForceSimple AS ForceSimple | |
| , h.ModifiedValueLimitation AS ModifiedValueLimitation | |
| , h.ModifiedValueCup AS ModifiedValueCup | |
| , h.ModifiedRateValue AS ModifiedRateValue | |
| , h.NoLongerFarming AS NoLongerFarming | |
| , e.ValueIndex AS ValueIndex | |
| , CONVERT(CHAR(1), ISNULL(e.ExcessiveBypass,0)) AS ExcessiveBypass | |
| , e.ExcludeFromSAT AS ExcludeFromSAT | |
| , e.YSkip AS YSkip | |
| , e.ApprovedValueOverride AS ApprovedValueOverride | |
| , e.ApprovedValueOverrideLimitationFlag AS ApprovedValueOverrideLimitationFlag | |
| , e.APHProceduralExceptionCode AS APHProceduralExceptionCode | |
| , e.MaltingBarleyPQE AS MaltingBarleyPQE | |
| , e.NewBreakingType AS NewBreakingType | |
| , r.ApprovedRv AS ApprovedRv | |
| , r.ApprovedRvOverride AS ApprovedRvOverride | |
| , r.PreviousRv AS PreviousRv | |
| , r.RateRv AS RateRv | |
| , r.SimpleAvgTRv AS SimpleAvgTRv | |
| , r.TRv AS TRv | |
| , r.AdjustedRv AS AdjustedRv | |
| FROM | |
| schema.APHHeader h | |
| LEFT OUTER JOIN | |
| schema.APHHeaderException e | |
| ON e.HeaderID = h.HeaderID | |
| LEFT OUTER JOIN | |
| schema.APHHeaderRv r | |
| ON r.HeaderID = h.HeaderID | |
| ) Header | |
| LEFT JOIN | |
| ( | |
| SELECT | |
| cteDerrived4.HeaderID | |
| , cteDerrived4.An_ID | |
| , Year1, ValueType1, Value1, Amounts1, IsLoss1, TAmount1, IsReviewed1, TypeCodeID1 | |
| , Year2, ValueType2, Value2, Amounts2, IsLoss2, TAmount2, IsReviewed2, TypeCodeID2 | |
| , Year3, ValueType3, Value3, Amounts3, IsLoss3, TAmount3, IsReviewed3, TypeCodeID3 | |
| , Year4, ValueType4, Value4, Amounts4, IsLoss4, TAmount4, IsReviewed4, TypeCodeID4 | |
| , Year5, ValueType5, Value5, Amounts5, IsLoss5, TAmount5, IsReviewed5, TypeCodeID5 | |
| , Year6, ValueType6, Value6, Amounts6, IsLoss6, TAmount6, IsReviewed6, TypeCodeID6 | |
| , Year7, ValueType7, Value7, Amounts7, IsLoss7, TAmount7, IsReviewed7, TypeCodeID7 | |
| , Year8, ValueType8, Value8, Amounts8, IsLoss8, TAmount8, IsReviewed8, TypeCodeID8 | |
| , Year9, ValueType9, Value9, Amounts9, IsLoss9, TAmount9, IsReviewed9, TypeCodeID9 | |
| , Year10, ValueType10, Value10, Amounts10, IsLoss10, TAmount10, IsReviewed10, TypeCodeID10 | |
| , RvCustomerNet1, RvAverage1, RvCustomerShare1, RvShareEquivalent1, RSAdjustedRv1, SkipRS1 | |
| , RvCustomerNet2, RvAverage2, RvCustomerShare2, RvShareEquivalent2, RSAdjustedRv2, SkipRS2 | |
| , RvCustomerNet3, RvAverage3, RvCustomerShare3, RvShareEquivalent3, RSAdjustedRv3, SkipRS3 | |
| , RvCustomerNet4, RvAverage4, RvCustomerShare4, RvShareEquivalent4, RSAdjustedRv4, SkipRS4 | |
| , RvCustomerNet5, RvAverage5, RvCustomerShare5, RvShareEquivalent5, RSAdjustedRv5, SkipRS5 | |
| , RvCustomerNet6, RvAverage6, RvCustomerShare6, RvShareEquivalent6, RSAdjustedRv6, SkipRS6 | |
| , RvCustomerNet7, RvAverage7, RvCustomerShare7, RvShareEquivalent7, RSAdjustedRv7, SkipRS7 | |
| , RvCustomerNet8, RvAverage8, RvCustomerShare8, RvShareEquivalent8, RSAdjustedRv8, SkipRS8 | |
| , RvCustomerNet9, RvAverage9, RvCustomerShare9, RvShareEquivalent9, RSAdjustedRv9, SkipRS9 | |
| , RvCustomerNet10, RvAverage10, RvCustomerShare10, RvShareEquivalent10, RSAdjustedRv10, SkipRS10 | |
| , SkipsFactor1, SkipsFactorOther1, SkipsValue1, RowWidth1, SkipsCode1, SkipsPattern1 | |
| , SkipsFactor2, SkipsFactorOther2, SkipsValue2, RowWidth2, SkipsCode2, SkipsPattern2 | |
| , SkipsFactor3, SkipsFactorOther3, SkipsValue3, RowWidth3, SkipsCode3, SkipsPattern3 | |
| , SkipsFactor4, SkipsFactorOther4, SkipsValue4, RowWidth4, SkipsCode4, SkipsPattern4 | |
| , SkipsFactor5, SkipsFactorOther5, SkipsValue5, RowWidth5, SkipsCode5, SkipsPattern5 | |
| , SkipsFactor6, SkipsFactorOther6, SkipsValue6, RowWidth6, SkipsCode6, SkipsPattern6 | |
| , SkipsFactor7, SkipsFactorOther7, SkipsValue7, RowWidth7, SkipsCode7, SkipsPattern7 | |
| , SkipsFactor8, SkipsFactorOther8, SkipsValue8, RowWidth8, SkipsCode8, SkipsPattern8 | |
| , SkipsFactor9, SkipsFactorOther9, SkipsValue9, RowWidth9, SkipsCode9, SkipsPattern9 | |
| , SkipsFactor10, SkipsFactorOther10, SkipsValue10, RowWidth10, SkipsCode10, SkipsPattern10 | |
| , YTAmount1, YTAmount1, YSkip1, Y2Skip1, YAdjust1, RvValue1 | |
| , YTAmount2, YTAmount2, YSkip2, Y2Skip2, YAdjust2, RvValue2 | |
| , YTAmount3, YTAmount3, YSkip3, Y2Skip3, YAdjust3, RvValue3 | |
| , YTAmount4, YTAmount4, YSkip4, Y2Skip4, YAdjust4, RvValue4 | |
| , YTAmount5, YTAmount5, YSkip5, Y2Skip5, YAdjust5, RvValue5 | |
| , YTAmount6, YTAmount6, YSkip6, Y2Skip6, YAdjust6, RvValue6 | |
| , YTAmount7, YTAmount7, YSkip7, Y2Skip7, YAdjust7, RvValue7 | |
| , YTAmount8, YTAmount8, YSkip8, Y2Skip8, YAdjust8, RvValue8 | |
| , YTAmount9, YTAmount9, YSkip9, Y2Skip9, YAdjust9, RvValue9 | |
| , YTAmount10,YTAmount10,YSkip10,Y2Skip10,YAdjust10,RvValue10 | |
| , ELevel1, ELevel2, ELevel3, ELevel4, ELevel5 | |
| , ELevel6, ELevel7, ELevel8, ELevel9, ELevel10 | |
| , EIsValid1, EIsValid2, EIsValid3, EIsValid4, EIsValid5 | |
| , EIsValid6, EIsValid7, EIsValid8, EIsValid9, EIsValid10 | |
| FROM | |
| ( | |
| SELECT | |
| pop.An_ID | |
| , pop.HeaderID | |
| , Year1, ValueType1, Value1, Amounts1, IsLoss1, TAmount1, IsReviewed1, TypeCodeID1 | |
| , Year2, ValueType2, Value2, Amounts2, IsLoss2, TAmount2, IsReviewed2, TypeCodeID2 | |
| , Year3, ValueType3, Value3, Amounts3, IsLoss3, TAmount3, IsReviewed3, TypeCodeID3 | |
| , Year4, ValueType4, Value4, Amounts4, IsLoss4, TAmount4, IsReviewed4, TypeCodeID4 | |
| , Year5, ValueType5, Value5, Amounts5, IsLoss5, TAmount5, IsReviewed5, TypeCodeID5 | |
| , Year6, ValueType6, Value6, Amounts6, IsLoss6, TAmount6, IsReviewed6, TypeCodeID6 | |
| , Year7, ValueType7, Value7, Amounts7, IsLoss7, TAmount7, IsReviewed7, TypeCodeID7 | |
| , Year8, ValueType8, Value8, Amounts8, IsLoss8, TAmount8, IsReviewed8, TypeCodeID8 | |
| , Year9, ValueType9, Value9, Amounts9, IsLoss9, TAmount9, IsReviewed9, TypeCodeID9 | |
| , Year10, ValueType10, Value10, Amounts10, IsLoss10, TAmount10, IsReviewed10, TypeCodeID10 | |
| FROM | |
| (SELECT DISTINCT An_ID, HeaderID FROM schema.Header) pop | |
| LEFT JOIN | |
| (SELECT An_ID, HeaderID, Year AS Year1, Value AS Value1, ValueType AS ValueType1, IsLoss AS IsLoss1, Amounts AS Amounts1 | |
| , TAmount AS TAmount1, IsReviewed AS IsReviewed1, TypeCodeID AS TypeCodeID1 FROM cte1 WHERE YearSequence = 1) aph1 | |
| ON aph1.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT An_ID, HeaderID, Year AS Year2, Value AS Value2, ValueType AS ValueType2, IsLoss AS IsLoss2, Amounts AS Amounts2 | |
| , TAmount AS TAmount2, IsReviewed AS IsReviewed2, TypeCodeID AS TypeCodeID2 FROM cte1 WHERE YearSequence = 2) aph2 | |
| ON aph2.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT An_ID, HeaderID, Year AS Year3, Value AS Value3, ValueType AS ValueType3, IsLoss AS IsLoss3, Amounts AS Amounts3 | |
| , TAmount AS TAmount3, IsReviewed AS IsReviewed3, TypeCodeID AS TypeCodeID3 FROM cte1 WHERE YearSequence = 3) aph3 | |
| ON aph3.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT An_ID, HeaderID, Year AS Year4, Value AS Value4, ValueType AS ValueType4, IsLoss AS IsLoss4, Amounts AS Amounts4 | |
| , TAmount AS TAmount4, IsReviewed AS IsReviewed4, TypeCodeID AS TypeCodeID4 FROM cte1 WHERE YearSequence = 4) aph4 | |
| ON aph4.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT An_ID, HeaderID, Year AS Year5, Value AS Value5, ValueType AS ValueType5, IsLoss AS IsLoss5, Amounts AS Amounts5 | |
| , TAmount AS TAmount5, IsReviewed AS IsReviewed5, TypeCodeID AS TypeCodeID5 FROM cte1 WHERE YearSequence = 5) aph5 | |
| ON aph5.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT An_ID, HeaderID, Year AS Year6, Value AS Value6, ValueType AS ValueType6, IsLoss AS IsLoss6, Amounts AS Amounts6 | |
| , TAmount AS TAmount6, IsReviewed AS IsReviewed6, TypeCodeID AS TypeCodeID6 FROM cte1 WHERE YearSequence = 6) aph6 | |
| ON aph6.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT An_ID, HeaderID, Year AS Year7, Value AS Value7, ValueType AS ValueType7, IsLoss AS IsLoss7, Amounts AS Amounts7 | |
| , TAmount AS TAmount7, IsReviewed AS IsReviewed7, TypeCodeID AS TypeCodeID7 FROM cte1 WHERE YearSequence = 7) aph7 | |
| ON aph7.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT An_ID, HeaderID, Year AS Year8, Value AS Value8, ValueType AS ValueType8, IsLoss AS IsLoss8, Amounts AS Amounts8 | |
| , TAmount AS TAmount8, IsReviewed AS IsReviewed8, TypeCodeID AS TypeCodeID8 FROM cte1 WHERE YearSequence = 8) aph8 | |
| ON aph8.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT An_ID, HeaderID, Year AS Year9, Value AS Value9, ValueType AS ValueType9, IsLoss AS IsLoss9, Amounts AS Amounts9 | |
| , TAmount AS TAmount9, IsReviewed AS IsReviewed9, TypeCodeID AS TypeCodeID9 FROM cte1 WHERE YearSequence = 9) aph9 | |
| ON aph9.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT An_ID, HeaderID, Year AS Year10,Value AS Value10,ValueType AS ValueType10,IsLoss AS IsLoss10,Amounts AS Amounts10 | |
| ,TAmount AS TAmount10,IsReviewed AS IsReviewed10, TypeCodeID AS TypeCodeID10 FROM cte1 WHERE YearSequence = 10) aph10 | |
| ON aph10.HeaderID = pop.HeaderID | |
| ) cteDerrived4 | |
| LEFT JOIN | |
| ( | |
| SELECT | |
| pop.HeaderID | |
| , RvCustomerNet1, RvAverage1, RvCustomerShare1, RvShareEquivalent1, RSAdjustedRv1, SkipRS1 | |
| , RvCustomerNet2, RvAverage2, RvCustomerShare2, RvShareEquivalent2, RSAdjustedRv2, SkipRS2 | |
| , RvCustomerNet3, RvAverage3, RvCustomerShare3, RvShareEquivalent3, RSAdjustedRv3, SkipRS3 | |
| , RvCustomerNet4, RvAverage4, RvCustomerShare4, RvShareEquivalent4, RSAdjustedRv4, SkipRS4 | |
| , RvCustomerNet5, RvAverage5, RvCustomerShare5, RvShareEquivalent5, RSAdjustedRv5, SkipRS5 | |
| , RvCustomerNet6, RvAverage6, RvCustomerShare6, RvShareEquivalent6, RSAdjustedRv6, SkipRS6 | |
| , RvCustomerNet7, RvAverage7, RvCustomerShare7, RvShareEquivalent7, RSAdjustedRv7, SkipRS7 | |
| , RvCustomerNet8, RvAverage8, RvCustomerShare8, RvShareEquivalent8, RSAdjustedRv8, SkipRS8 | |
| , RvCustomerNet9, RvAverage9, RvCustomerShare9, RvShareEquivalent9, RSAdjustedRv9, SkipRS9 | |
| , RvCustomerNet10, RvAverage10, RvCustomerShare10, RvShareEquivalent10, RSAdjustedRv10, SkipRS10 | |
| FROM | |
| (SELECT DISTINCT HeaderID FROM cte10) pop | |
| LEFT JOIN | |
| (SELECT HeaderID, RvCustomerNet AS RvCustomerNet1,RvAverage AS RvAverage1,RvCustomerShare AS RvCustomerShare1 | |
| ,RvShareEquivalent AS RvShareEquivalent1,RSAdjustedRv AS RSAdjustedRv1,SkipRS AS SkipRS1 FROM cte10 WHERE YearSequence = 1) aphr1 | |
| ON aphr1.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,RvCustomerNet AS RvCustomerNet2,RvAverage AS RvAverage2,RvCustomerShare AS RvCustomerShare2 | |
| ,RvShareEquivalent AS RvShareEquivalent2,RSAdjustedRv AS RSAdjustedRv2,SkipRS AS SkipRS2 FROM cte10 WHERE YearSequence = 2) aphr2 | |
| ON aphr2.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,RvCustomerNet AS RvCustomerNet3,RvAverage AS RvAverage3,RvCustomerShare AS RvCustomerShare3 | |
| ,RvShareEquivalent AS RvShareEquivalent3,RSAdjustedRv AS RSAdjustedRv3,SkipRS AS SkipRS3 FROM cte10 WHERE YearSequence = 3) aphr3 | |
| ON aphr3.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,RvCustomerNet AS RvCustomerNet4,RvAverage AS RvAverage4,RvCustomerShare AS RvCustomerShare4 | |
| ,RvShareEquivalent AS RvShareEquivalent4,RSAdjustedRv AS RSAdjustedRv4,SkipRS AS SkipRS4 FROM cte10 WHERE YearSequence = 4) aphr4 | |
| ON aphr4.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,RvCustomerNet AS RvCustomerNet5,RvAverage AS RvAverage5,RvCustomerShare AS RvCustomerShare5 | |
| ,RvShareEquivalent AS RvShareEquivalent5,RSAdjustedRv AS RSAdjustedRv5,SkipRS AS SkipRS5 FROM cte10 WHERE YearSequence = 5) aphr5 | |
| ON aphr5.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,RvCustomerNet AS RvCustomerNet6,RvAverage AS RvAverage6,RvCustomerShare AS RvCustomerShare6 | |
| ,RvShareEquivalent AS RvShareEquivalent6,RSAdjustedRv AS RSAdjustedRv6,SkipRS AS SkipRS6 FROM cte10 WHERE YearSequence = 6) aphr6 | |
| ON aphr6.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,RvCustomerNet AS RvCustomerNet7,RvAverage AS RvAverage7,RvCustomerShare AS RvCustomerShare7 | |
| ,RvShareEquivalent AS RvShareEquivalent7,RSAdjustedRv AS RSAdjustedRv7,SkipRS AS SkipRS7 FROM cte10 WHERE YearSequence = 7) aphr7 | |
| ON aphr7.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,RvCustomerNet AS RvCustomerNet8,RvAverage AS RvAverage8,RvCustomerShare AS RvCustomerShare8 | |
| ,RvShareEquivalent AS RvShareEquivalent8,RSAdjustedRv AS RSAdjustedRv8,SkipRS AS SkipRS8 FROM cte10 WHERE YearSequence = 8) aphr8 | |
| ON aphr8.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,RvCustomerNet AS RvCustomerNet9,RvAverage AS RvAverage9,RvCustomerShare AS RvCustomerShare9 | |
| ,RvShareEquivalent AS RvShareEquivalent9,RSAdjustedRv AS RSAdjustedRv9,SkipRS AS SkipRS9 FROM cte10 WHERE YearSequence = 9) aphr9 | |
| ON aphr9.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,RvCustomerNet AS RvCustomerNet10,RvAverage AS RvAverage10,RvCustomerShare AS RvCustomerShare10 | |
| ,RvShareEquivalent AS RvShareEquivalent10,RSAdjustedRv AS RSAdjustedRv10,SkipRS AS SkipRS10 FROM cte10 WHERE YearSequence = 10) aphr10 | |
| ON aphr10.HeaderID = pop.HeaderID | |
| ) cteDerrived5 | |
| ON cteDerrived5.HeaderID = cteDerrived4.HeaderID | |
| LEFT JOIN | |
| ( | |
| SELECT | |
| pop.HeaderID | |
| , SkipsFactor1, SkipsFactorOther1, SkipsValue1, RowWidth1, SkipsCode1, SkipsPattern1 | |
| , SkipsFactor2, SkipsFactorOther2, SkipsValue2, RowWidth2, SkipsCode2, SkipsPattern2 | |
| , SkipsFactor3, SkipsFactorOther3, SkipsValue3, RowWidth3, SkipsCode3, SkipsPattern3 | |
| , SkipsFactor4, SkipsFactorOther4, SkipsValue4, RowWidth4, SkipsCode4, SkipsPattern4 | |
| , SkipsFactor5, SkipsFactorOther5, SkipsValue5, RowWidth5, SkipsCode5, SkipsPattern5 | |
| , SkipsFactor6, SkipsFactorOther6, SkipsValue6, RowWidth6, SkipsCode6, SkipsPattern6 | |
| , SkipsFactor7, SkipsFactorOther7, SkipsValue7, RowWidth7, SkipsCode7, SkipsPattern7 | |
| , SkipsFactor8, SkipsFactorOther8, SkipsValue8, RowWidth8, SkipsCode8, SkipsPattern8 | |
| , SkipsFactor9, SkipsFactorOther9, SkipsValue9, RowWidth9, SkipsCode9, SkipsPattern9 | |
| , SkipsFactor10, SkipsFactorOther10, SkipsValue10, RowWidth10, SkipsCode10, SkipsPattern10 | |
| FROM | |
| (SELECT DISTINCT HeaderID FROM cte11) pop | |
| LEFT JOIN | |
| (SELECT HeaderID,SkipsFactor AS SkipsFactor1,SkipsFactorOther AS SkipsFactorOther1,SkipsValue AS SkipsValue1 | |
| ,RowWidth AS RowWidth1,SkipsCode AS SkipsCode1,SkipsPattern AS SkipsPattern1 FROM cte11 WHERE YearSequence = 1) details1 | |
| ON details1.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,SkipsFactor AS SkipsFactor2,SkipsFactorOther AS SkipsFactorOther2,SkipsValue AS SkipsValue2 | |
| ,RowWidth AS RowWidth2,SkipsCode AS SkipsCode2,SkipsPattern AS SkipsPattern2 FROM cte11 WHERE YearSequence = 2) details2 | |
| ON details2.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,SkipsFactor AS SkipsFactor3,SkipsFactorOther AS SkipsFactorOther3,SkipsValue AS SkipsValue3 | |
| ,RowWidth AS RowWidth3,SkipsCode AS SkipsCode3,SkipsPattern AS SkipsPattern3 FROM cte11 WHERE YearSequence = 3) details3 | |
| ON details3.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,SkipsFactor AS SkipsFactor4,SkipsFactorOther AS SkipsFactorOther4,SkipsValue AS SkipsValue4 | |
| ,RowWidth AS RowWidth4,SkipsCode AS SkipsCode4,SkipsPattern AS SkipsPattern4 FROM cte11 WHERE YearSequence = 4) details4 | |
| ON details4.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,SkipsFactor AS SkipsFactor5,SkipsFactorOther AS SkipsFactorOther5,SkipsValue AS SkipsValue5 | |
| ,RowWidth AS RowWidth5,SkipsCode AS SkipsCode5,SkipsPattern AS SkipsPattern5 FROM cte11 WHERE YearSequence = 5) details5 | |
| ON details5.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,SkipsFactor AS SkipsFactor6,SkipsFactorOther AS SkipsFactorOther6,SkipsValue AS SkipsValue6 | |
| ,RowWidth AS RowWidth6,SkipsCode AS SkipsCode6,SkipsPattern AS SkipsPattern6 FROM cte11 WHERE YearSequence = 6) details6 | |
| ON details6.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,SkipsFactor AS SkipsFactor7,SkipsFactorOther AS SkipsFactorOther7,SkipsValue AS SkipsValue7 | |
| ,RowWidth AS RowWidth7,SkipsCode AS SkipsCode7,SkipsPattern AS SkipsPattern7 FROM cte11 WHERE YearSequence = 7) details7 | |
| ON details7.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,SkipsFactor AS SkipsFactor8,SkipsFactorOther AS SkipsFactorOther8,SkipsValue AS SkipsValue8 | |
| ,RowWidth AS RowWidth8,SkipsCode AS SkipsCode8,SkipsPattern AS SkipsPattern8 FROM cte11 WHERE YearSequence = 8) details8 | |
| ON details8.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,SkipsFactor AS SkipsFactor9,SkipsFactorOther AS SkipsFactorOther9,SkipsValue AS SkipsValue9 | |
| ,RowWidth AS RowWidth9,SkipsCode AS SkipsCode9,SkipsPattern AS SkipsPattern9 FROM cte11 WHERE YearSequence = 9) details9 | |
| ON details9.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID,SkipsFactor AS SkipsFactor10,SkipsFactorOther AS SkipsFactorOther10,SkipsValue AS SkipsValue10 | |
| ,RowWidth AS RowWidth10,SkipsCode AS SkipsCode10,SkipsPattern AS SkipsPattern10 FROM cte11 WHERE YearSequence = 10) details10 | |
| ON details10.HeaderID = pop.HeaderID | |
| ) cteDerrived3 | |
| ON cteDerrived3.HeaderID = cteDerrived4.HeaderID | |
| LEFT JOIN | |
| ( | |
| SELECT | |
| pop.HeaderID | |
| , YTAmount1, YTAmount1, YSkip1, Y2Skip1, YAdjust1, RvValue1 | |
| , YTAmount2, YTAmount2, YSkip2, Y2Skip2, YAdjust2, RvValue2 | |
| , YTAmount3, YTAmount3, YSkip3, Y2Skip3, YAdjust3, RvValue3 | |
| , YTAmount4, YTAmount4, YSkip4, Y2Skip4, YAdjust4, RvValue4 | |
| , YTAmount5, YTAmount5, YSkip5, Y2Skip5, YAdjust5, RvValue5 | |
| , YTAmount6, YTAmount6, YSkip6, Y2Skip6, YAdjust6, RvValue6 | |
| , YTAmount7, YTAmount7, YSkip7, Y2Skip7, YAdjust7, RvValue7 | |
| , YTAmount8, YTAmount8, YSkip8, Y2Skip8, YAdjust8, RvValue8 | |
| , YTAmount9, YTAmount9, YSkip9, Y2Skip9, YAdjust9, RvValue9 | |
| , YTAmount10,YTAmount10,YSkip10,Y2Skip10,YAdjust10,RvValue10 | |
| FROM | |
| (SELECT DISTINCT HeaderID FROM cte12) pop | |
| LEFT JOIN | |
| (SELECT HeaderID ,YTAmount AS YTAmount1,YTAmount AS YTAmount1,YSkip AS YSkip1,Y2Skip AS Y2Skip1, | |
| YAdjust AS YAdjust1,RvValue AS RvValue1 FROM cte12 WHERE YearSequence = 1) details1 ON details1.HeaderID = pop.HeaderID | |
| LEFT JOIN | |
| (SELECT HeaderID ,YTAmount AS YTAmount2,YTAmount AS YTAmount2,YSkip AS YSkip2,Y2Skip AS Y2Skip2, | |
| YAdjust AS YAdjust2,RvValue AS RvValue2 FROM cte12 WHERE YearSequence = 2) details2 ON details2.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID ,YTAmount AS YTAmount3,YTAmount AS YTAmount3,YSkip AS YSkip3,Y2Skip AS Y2Skip3, | |
| YAdjust AS YAdjust3,RvValue AS RvValue3 FROM cte12 WHERE YearSequence = 3) details3 ON details3.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID ,YTAmount AS YTAmount4,YTAmount AS YTAmount4,YSkip AS YSkip4,Y2Skip AS Y2Skip4, | |
| YAdjust AS YAdjust4,RvValue AS RvValue4 FROM cte12 WHERE YearSequence = 4) details4 ON details4.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID ,YTAmount AS YTAmount5,YTAmount AS YTAmount5,YSkip AS YSkip5,Y2Skip AS Y2Skip5, | |
| YAdjust AS YAdjust5,RvValue AS RvValue5 FROM cte12 WHERE YearSequence = 5) details5 ON details5.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID ,YTAmount AS YTAmount6,YTAmount AS YTAmount6,YSkip AS YSkip6,Y2Skip AS Y2Skip6, | |
| YAdjust AS YAdjust6,RvValue AS RvValue6 FROM cte12 WHERE YearSequence = 6) details6 ON details6.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID ,YTAmount AS YTAmount7,YTAmount AS YTAmount7,YSkip AS YSkip7,Y2Skip AS Y2Skip7, | |
| YAdjust AS YAdjust7,RvValue AS RvValue7 FROM cte12 WHERE YearSequence = 7) details7 ON details7.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID ,YTAmount AS YTAmount8,YTAmount AS YTAmount8,YSkip AS YSkip8,Y2Skip AS Y2Skip8, | |
| YAdjust AS YAdjust8,RvValue AS RvValue8 FROM cte12 WHERE YearSequence = 8) details8 ON details8.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID ,YTAmount AS YTAmount9,YTAmount AS YTAmount9,YSkip AS YSkip9,Y2Skip AS Y2Skip9, | |
| YAdjust AS YAdjust9,RvValue AS RvValue9 FROM cte12 WHERE YearSequence = 9) details9 ON details9.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID ,YTAmount AS YTAmount10,YTAmount AS YTAmount10,YSkip AS YSkip10,Y2Skip AS Y2Skip10, | |
| YAdjust AS YAdjust10,RvValue AS RvValue10 FROM cte12 WHERE YearSequence = 10) details10 ON details10.HeaderID = pop.HeaderID | |
| ) cteDervied1 | |
| ON cteDervied1.HeaderID = cteDerrived4.HeaderID | |
| LEFT JOIN | |
| ( | |
| SELECT | |
| pop.HeaderID | |
| , ELevel1, ELevel2, ELevel3, ELevel4, ELevel5 | |
| , ELevel6, ELevel7, ELevel8, ELevel9, ELevel10 | |
| , EIsValid1, EIsValid2, EIsValid3, EIsValid4, EIsValid5 | |
| , EIsValid6, EIsValid7, EIsValid8, EIsValid9, EIsValid10 | |
| FROM | |
| (SELECT DISTINCT HeaderID FROM cte6) pop | |
| LEFT JOIN | |
| (SELECT HeaderID,ELevel AS ELevel1, EIsValid AS EIsValid1 FROM cte6 WHERE YearSequence = 1) details1 ON details1.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID,ELevel AS ELevel2, EIsValid AS EIsValid2 FROM cte6 WHERE YearSequence = 2) details2 ON details2.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID,ELevel AS ELevel3, EIsValid AS EIsValid3 FROM cte6 WHERE YearSequence = 3) details3 ON details3.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID,ELevel AS ELevel4, EIsValid AS EIsValid4 FROM cte6 WHERE YearSequence = 4) details4 ON details4.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID,ELevel AS ELevel5, EIsValid AS EIsValid5 FROM cte6 WHERE YearSequence = 5) details5 ON details5.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID,ELevel AS ELevel6, EIsValid AS EIsValid6 FROM cte6 WHERE YearSequence = 6) details6 ON details6.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID,ELevel AS ELevel7, EIsValid AS EIsValid7 FROM cte6 WHERE YearSequence = 7) details7 ON details7.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID,ELevel AS ELevel8, EIsValid AS EIsValid8 FROM cte6 WHERE YearSequence = 8) details8 ON details8.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID,ELevel AS ELevel9, EIsValid AS EIsValid9 FROM cte6 WHERE YearSequence = 9) details9 ON details9.HeaderID = pop.HeaderID LEFT JOIN | |
| (SELECT HeaderID,ELevel AS ELevel10,EIsValid AS EIsValid10 FROM cte6 WHERE YearSequence = 10) details10 ON details10.HeaderID = pop.HeaderID | |
| ) cteDervied2 | |
| ON cteDervied2.HeaderID = cteDerrived4.HeaderID | |
| ) ChildPart | |
| ON ChildPart.HeaderID = Header.HeaderID |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment