Thank you so very much, this helped tremendously. The issue I'm having now, is that our previous developer added a new table called 'TierPrice' (which actually has the correct pricing information per product). So I'm not getting the correct pricing without bringing in this table as well. Admittedly I'm not a db guy lol, Here is what the 'TierPrice' table looks like:
TierPrice: ( https://imgur.com/YXHbtkN -- Image isn't showing below)
Is there a way I can pull the 'Price' from 'TierPrice' Into your query?
(ie: SELECT * FROM product p WHERE (p.LimitedToStores = 0 OR EXISTS (
SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
WHERE [sm].EntityId = p.Id AND [sm].EntityName = 'Product' and [sm].StoreId='11')) )
Any help is GREATLY appreciated.
try with below query to get price into last column of result query
DECLARE @CurrentDate as DATETIME =SYSDATETIME()
DECLARE @CustomerRoleId int
SET @CustomerRoleId=1 -- this is your current login customer role id
SELECT * FROM product p
left join TierPrice t on p.Id = t.ProductId
and ((T.StartDateTimeUtc <= @CurrentDate OR T.StartDateTimeUtc IS NULL) AND (@CurrentDate <= T.EndDateTimeUtc OR T.EndDateTimeUtc IS NULL))
AND (t.CustomerRoleId =0 or t.CustomerRoleId = @CustomerRoleId OR t.CustomerRoleId is NULL )