March 18, 2014 at 9:46 am
Hi Guys,
Really struggling today, but im trying to think of a more efficient way for the following script..any ideas?
--================================================
DECLARE @GroupId INT = NULL
DECLARE @PriceMethod VARCHAR(6) = NULL
SELECT
S.Supplier,
SL.StockLevel,
CASE WHEN @GroupId IS NULL
THEN (SELECT CASE WHEN @PriceMethod = 'Fitted' THEN PP.FittedPrice ELSE P.UnitPrice END FROM ProductPricing_tbl AS PP WHERE PP.ProductId = P.ProductId AND GroupId IS NULL)
ELSE
(
CASE WHEN EXISTS (SELECT 1FROM ProductPricing_tbl AS PP WHEREPP.ProductId = P.ProductId AND GroupId = @GroupId)
THEN (SELECT CASE WHEN @PriceMethod = 'Fitted' THEN PP.FittedPrice ELSE P.UnitPrice END FROM ProductPricing_tbl AS PP WHERE PP.ProductId = P.ProductId AND GroupId = @GroupId)
ELSE (SELECT CASE WHEN @PriceMethod = 'Fitted' THEN PP.FittedPrice ELSE P.UnitPrice END FROM ProductPricing_tbl AS PP WHERE PP.ProductId = P.ProductId AND GroupId IS NULL)
END
)
END AS UnitPrice,
CASE WHEN @GroupId IS NULL
THEN (SELECT CASE WHEN @PriceMethod = 'Fitted' THEN PP.WasFittedPrice ELSE P.WasUnitPrice END FROM ProductPricing_tbl AS PP WHERE PP.ProductId = P.ProductId AND GroupId IS NULL)
ELSE
(
CASE WHEN EXISTS (SELECT 1FROM ProductPricing_tbl AS PP WHEREPP.ProductId = P.ProductId AND GroupId = @GroupId)
THEN (SELECT CASE WHEN @PriceMethod = 'Fitted' THEN PP.WasFittedPrice ELSE P.WasUnitPrice END FROM ProductPricing_tbl AS PP WHERE PP.ProductId = P.ProductId AND GroupId = @GroupId)
ELSE (SELECT CASE WHEN @PriceMethod = 'Fitted' THEN PP.WasFittedPrice ELSE P.WasUnitPrice END FROM ProductPricing_tbl AS PP WHERE PP.ProductId = P.ProductId AND GroupId IS NULL)
END
)
END AS WasPrice
FROM
Products_tbl AS P
INNER JOIN StockLevel_tbl AS SL ON SL.ProductId = P.ProductId
INNER JOIN Suppliers_tbl AS S ON S.SupplierId = SL.SupplierId
--===============================================
Also if i was to add A SUM to say column 'SL.StockLevel' this wouldn't work as i would need to somehow group the other columns :unsure:
March 18, 2014 at 1:43 pm
I did this too quickly but maybe it will give you an idea. (Also it is not surgable).
SELECT
S.Supplier,
SL.StockLevel,
CASE WHEN @PriceMethod = 'Fitted' THEN PP.FittedPrice ELSE P.UnitPrice END AS UnitPrice,
CASE WHEN @PriceMethod = 'Fitted' THEN PP.WasFittedPrice ELSE P.WasUnitPrice END AS WasPrice
FROM Products_tbl AS P
INNER JOIN StockLevel_tbl AS SL ON SL.ProductId = P.ProductId
INNER JOIN Suppliers_tbl AS S ON S.SupplierId = SL.SupplierId
INNER JOIN ProductPricing_tbl AS PP ON P.ProductID = PP.ProductID AND ISNULL(PP.GroupID, '') = ISNULL(@GroupID, '')
March 19, 2014 at 4:02 am
Hi djj,
Thanks for replying, after looking at my code again this morning i've manage to trim it down. But you gave me an idea by using the ISNULL(PP.GroupID, '') = ISNULL(@GroupID, '') . So cheers for that and taking your time in helping me out. 🙂
March 19, 2014 at 4:35 am
First thing in the morning here so not a lot of time.
Remember that the ISNULL causes the query to be non-surgable. (index not used)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply