Getting a Little Farther

1

Below is some code that I have written. In the end I will be populating the first four pricing fields and I am currently working on #1 which is called FCMP. The code has no errors but it never finishes. If I take each individual select it runs pretty quickly. I must have some incorrect logic I am not seeing. I am currently trying to populate usable cost to calculate and set that FCMP Price. My problem is definitely something with my select inside my select:

Select u.UCost from dbo.Pricing_UsableCostData u (nolock) where pr.Usku = u.USku) as UsableCost

because if I just change the select to say 2 as UsableCost it runs fine.

TRUNCATE TABLE TD_REPORTS.dbo.Flynns_CalcPricing
INSERT INTO TD_REPORTS.dbo.Flynns_CalcPricing(USku,FCMP,AveRetPrice,LowestPrice,MaxGP,NewStore,FPrice6,FPrice7,FPrice8,FPrice9,FPrice10)
Select TD_SOURCE.dbo.Sku.SkuCode,0,0,0,0,0,0,0,0,0,0 from TD_SOURCE.dbo.Sku

-- Create and Update Flynns_CalcPricing from INV_SKU
-- CalcIt is USku, Manufacturer,Line,UsableCost,Multiplier

Update TD_REPORTS.dbo.Flynns_CalcPricing
SET FCMP =
CASE
WHEN CalcIt.Cman = 'Michelin' then ((UsableCost + 44) * CalcIt.Multiplier) -- Add All Mans and some Lines (Lines before just Mans)
WHEN CalcIt.UsableCost > 0 and CalcIt.UsableCost <= 40.99 then ((UsableCost + 32) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 41 and CalcIt.UsableCost <= 45.99 then ((UsableCost + 34) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 46 and CalcIt.UsableCost <= 50.99 then ((UsableCost + 36) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 51 and CalcIt.UsableCost <= 55.99 then ((UsableCost + 38) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 56 and CalcIt.UsableCost <= 60.99 then ((UsableCost + 40) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 61 and CalcIt.UsableCost <= 65.99 then ((UsableCost + 42) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 66 and CalcIt.UsableCost <= 70.99 then ((UsableCost + 44) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 71 and CalcIt.UsableCost <= 75.99 then ((UsableCost + 45) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 76 and CalcIt.UsableCost <= 80.99 then ((UsableCost + 46) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 81 and CalcIt.UsableCost <= 85.99 then ((UsableCost + 47) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 86 and CalcIt.UsableCost <= 90.99 then ((UsableCost + 48) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 91 and CalcIt.UsableCost <= 95.99 then ((UsableCost + 49) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 96 and CalcIt.UsableCost <= 100.99 then ((UsableCost + 50) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 101 and CalcIt.UsableCost <= 109.99 then ((UsableCost + 51) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 110 and CalcIt.UsableCost <= 119.99 then ((UsableCost + 52) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 120 and CalcIt.UsableCost <= 129.99 then ((UsableCost + 53) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 130 and CalcIt.UsableCost <= 139.99 then ((UsableCost + 54) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 140 and CalcIt.UsableCost <= 149.99 then ((UsableCost + 55) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 150 and CalcIt.UsableCost <= 159.99 then ((UsableCost + 56) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 160 and CalcIt.UsableCost <= 169.99 then ((UsableCost + 57) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 170 and CalcIt.UsableCost <= 179.99 then ((UsableCost + 58) * CalcIt.Multiplier)
WHEN CalcIt.UsableCost >= 180 then ((UsableCost + 59) * CalcIt.Multiplier) -- Truncate and add .98

   END,

AveRetPrice = 1,
LowestPrice = 2,
MaxGP = 3,
NewStore = 4

   From
   ( 
        SELECT 
            pr.USku,
	s.Manufacturer as Cman,
            (Select u.UCost from dbo.Pricing_UsableCostData u (nolock) where pr.Usku = u.USku)  as UsableCost,
            1  as multiplier

        FROM 
          TD_REPORTS.dbo.Flynns_CalcPricing pr (nolock)
          inner join 
          TD_SOURCE.dbo.SkuMaster s (nolock)
          ON pr.USku = s.SkuCode
   )CalcIt
2

The NOLOCKs are not going to do you any good.

Difficult to tell without DDL and test data but try something like:

TRUNCATE TABLE TD_REPORTS.dbo.Flynns_CalcPricing;

WITH FCMPCosts
AS
(
    SELECT CAST(Low AS money) AS Low, CAST(High AS money) As High, CAST(OffSet AS money) AS OffSet
    FROM 
    (VALUES
        (0, 41, 32),(41, 46, 34),(46, 51, 36),(51, 56, 38),(56, 61, 40),(61, 66, 42),(66, 71, 44),(71, 76, 45),(76, 81, 46)
        ,(81, 86, 47),(86, 91, 48),(91, 96, 49),(96, 101, 50),(101, 110, 51),(110, 120, 52),(120, 130, 53),(130, 140, 54)
        ,(140, 150, 55),(150, 160, 56),(150, 170, 57),(170, 180, 58),(180, 922337203685477.5807, 59)    
    ) V (Low, High, OffSet)
)
INSERT INTO TD_REPORTS.dbo.Flynns_CalcPricing(USku,FCMP,AveRetPrice,LowestPrice,MaxGP,NewStore,FPrice6,FPrice7,FPrice8,FPrice9,FPrice10)
SELECT S.USku
    ,CASE
        WHEN M.Manufacturer = 'Michelin'
        THEN (U.UCost + 44) * A.multiplier
        ELSE COALESCE((U.Cost + F.OffSet) * A.multiplier, 0)
    END AS FCMP
    ,AveRetPrice = 1
    ,LowestPrice = 2
    ,MaxGP = 3
    ,NewStore = 4
    ,0
    ,0
    ,0
    ,0
    ,0
FROM  TD_SOURCE.dbo.Sku S
    JOIN TD_SOURCE.dbo.SkuMaster M ON S.USku = M.SkuCode
    CROSS APPLY (SELECT 1) A (multiplier)
    LEFT JOIN dbo.Pricing_UsableCostData U ON S.USku = U.SkuCode
    LEFT JOIN FCMPCosts F ON U.UCost >= F.Low AND U.UCost < F.High