Alright so I'm stuck with some pretty complex SQL.
I have a simple product and grouped product which may have multiple simple products
Each simple product has 3 discount fields and their startDate and endDate and their normal
Next part is hard to explain but I'll try my best through
Grouped product has 'n' simple products attached to it. From the 'n' simple products I need to find the lowest price among them which is also is in discount price duration period and if it isn't in any of them, return normal price
I've uploaded some sample data as well to better understand
According to sample data, Price that is supposed to be returned now is 20
my current code so far
(productTypeId = 5 is simple product). Sorting that is easy however i cannot think of a way to sort grouped product
Code:
INSERT INTO #DisplayOrderTmp ([ProductId], [MinPrice], [MaxPrice])
SELECT p.Id,
case
when p.ProductTypeId = 5
then
case
when SpecialPrice is not null and getutcdate() BETWEEN p.SpecialPriceStartDateTimeUtc AND p.SpecialPriceEndDateTimeUtc
then SpecialPrice
when SecondSpecialPrice is not null and getutcdate() BETWEEN p.SecondSpecialPriceStartDateTimeUtc AND p.SecondSpecialPriceEndDateTimeUtc
then SpecialPrice
when ThirdSpecialPrice is not null and getutcdate() BETWEEN p.ThirdSpecialPriceStartDateTimeUtc AND p.ThirdSpecialPriceEndDateTimeUtc
then ThirdSpecialPrice
else Price
end
else (Select min(Price) from Product where ParentGroupedProductId = p.id)
end as MinPrice,
case
when p.ProductTypeId = 5
then Price
else (Select max(Price) from Product where ParentGroupedProductId = p.id)
end as MaxPrice
FROM
Product p with (NOLOCK)'
...
SET @sql_orderby = ' MinPrice ASC'
ELSE IF @OrderBy = 6 /* Price: High to Low */
SET @sql_orderby = ' MaxPrice DESC'
SELECT
t.Id
, MIN(t.MinPrice)
, MAX(t.MaxPrice)
FROM (
SELECT
p.Id,
case
when p.ProductTypeId = 5
then
case
when SpecialPrice is not null and getutcdate() BETWEEN p.SpecialPriceStartDateTimeUtc AND p.SpecialPriceEndDateTimeUtc
then SpecialPrice
else Price
end
else (Select min(Price) from Product where ParentGroupedProductId = p.id)
end as MinPrice,
case
when p.ProductTypeId = 5
then Price
else (Select max(Price) from Product where ParentGroupedProductId = p.id)
end as MaxPrice
FROM
Product p with (NOLOCK)
UNION
SELECT
p.Id,
case
when p.ProductTypeId = 5
then
case
when SecondSpecialPrice is not null and getutcdate() BETWEEN p.SecondSpecialPriceStartDateTimeUtc AND p.SecondSpecialPriceEndDateTimeUtc
then SpecialPrice
else Price
end
else (Select min(Price) from Product where ParentGroupedProductId = p.id)
end as MinPrice,
case
when p.ProductTypeId = 5
then Price
else (Select max(Price) from Product where ParentGroupedProductId = p.id)
end as MaxPrice
FROM
Product p with (NOLOCK)
UNION
SELECT
p.Id,
case
when p.ProductTypeId = 5
then
case
when ThirdSpecialPrice is not null and getutcdate() BETWEEN p.ThirdSpecialPriceStartDateTimeUtc AND p.ThirdSpecialPriceEndDateTimeUtc
then ThirdSpecialPrice
else Price
end
else (Select min(Price) from Product where ParentGroupedProductId = p.id)
end as MinPrice,
case
when p.ProductTypeId = 5
then Price
else (Select max(Price) from Product where ParentGroupedProductId = p.id)
end as MaxPrice
FROM
Product p with (NOLOCK)) t
GROUP BY t.Id
or write 1 big case encapsulating the small ones if c<(if a<b then a else b) then c else (if a<b then a else b)
Signature/Avatar nuking: none (can be changed in your profile)
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
