The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
Select I.ItemID, I.ItemNum, I.LeadPPM, I.VendorPartNum as 'VendorPartNum1', I.StdLeadTime as 'StdLeadTime1', I.PurchaseCost as 'StandardCost', I.PurchaseQty, C.Category, V.Vendor as 'Vendor1', VF.VendorFactory as 'VendorFactory1', CT.Country as 'Country1', H.DutyRate as 'BaseHTSDuty1', M.Material, -- Vendor1 HTS Duty Calculation Case When (V.CountryID=1) or (I.HTSAFLAG=1) then 0 When (I.HTSCodeID = null) then '' When ((V.CountryID = 9) and (M.MaterialID = 35) and ('XFactoryReplacementCost' > 1.5)) then 0.05 When (V.CountryID = 9) then 0 Else H.DutyRate End As CfmHTSDuty1, --MaxReplacementCostDate Case When (SELECT Max(CH.ReplacementCostDate) FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID)=Null then '' Else (SELECT Max(CH.ReplacementCostDate) FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID) End As MaxReplacementCostDate, --XFactoryReplacementCost --Problem 1 - Looks like SQL 2005 let me reference calculated field 'XFactoryReplacementCost' in Vendor 1 HTS Duty Calc above, so I tried referencing MaxReplacementCostDate in subquery below --It didn't like me referencing it directly so I tried convertig to datetime. --It will work if I put in entire calculating query used to derive MaxReplacementCostDate, but I'd rather use calculated field alias. Case When (SELECT CH.ReplacementCost FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID and CH.ReplacementCostDate=convert(datetime, 'MaxReplacementCostDate'))=Null then '' Else (SELECT CH.ReplacementCost FROM tRefCostHistory CH WHERE CH.ItemID=I.ItemID and CH.VendorID=I.VendorID and CH.ReplacementCostDate=convert(datetime, 'MaxReplacementCostDate')) End As XFactoryReplacementCost, --Handling Fee 'XFactoryReplacementCost' * .1 As HandlingFee, --COO Duty -- This is where it gets complicated because this calculation is based on the data derived from 4 calculated fields above -- They are CfmHTSDuty1, MaxReplacementCostDate, XFactoryReplacementCost, HandlingFee -- After this field, I have a few more that will use all calculated fields above. You can see how this is getting to be very complex -- with SQL 2005 not allowing me to directly reference the calculated fields in the subsequent subqueries I.CreatedDate, I.CreatedBy, I.ModifiedDate, I.ModifiedBy from tItemMaster I Left Join tRefCategory C on C.CategoryID = I.CategoryID Left Join tRefVendors V on V.VendorID = I.VendorID Left Join tRefVendorFactory VF on VF.VendorFactoryiD = I.VendorFactoryID Left Join tRefCountry CT on CT.CountryID = V.CountryID Left Join tRefHTSCode H on H.HTSCodeID = I.HTSCodeID Left Join tRefMaterial M on M.MaterialID = I.MaterialID where I.ItemID = 2