I am writing a complex SQL query that will calculate a variety of price and tax calculations in one shot. This may not be the best approach so I'm open to suggestions. The input for this query is a derived from a Master table and a series of associated lookup tables that are INNER JOINED in the query. The DB is normalized.
The major problem I'm having is many of the price and tax calculations are built based on other subqueries which result in calculated fields. I need to reference these calculated fields as I move deeper and deeper into the query writing, its gets exponentially more complex because SQL 2005 does not let me reference "calculated fields from previous subqueries in another subquery.
I've attached an example. Any help would be appreciated as I seem to get stuck on this concept fairly often.
I.VendorPartNum as 'VendorPartNum1',
I.StdLeadTime as 'StdLeadTime1',
I.PurchaseCost as 'StandardCost',
V.Vendor as 'Vendor1',
VF.VendorFactory as 'VendorFactory1',
CT.Country as 'Country1',
H.DutyRate as 'BaseHTSDuty1',
-- Vendor1 HTS Duty Calculation
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
End As CfmHTSDuty1,
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,
--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.
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,
'XFactoryReplacementCost' * .1
-- 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
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