|
|
| Page 1 of 1 |
|
|
 Posted: Wed, 10th Feb 2016 10:36 Post subject: I pray to thee sql masters |
|
 |
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
http://s000.tinyupload.com/?file_id=68280108594975177728
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'
|
|
|
| Back to top |
|
|
|
|
| Posted: Wed, 10th Feb 2016 13:09 Post subject: |
|
|
Can you upload the data elsewhere?
"The web page is on the list of websites with potentially dangerous content."
Does it need to be in one query? I do not really understand what you want to return either, do you want the cheapest grouped product or the cheapest simple product?
PC: Yes. Console: No.
|
|
| Back to top |
|
|
|
|
| Posted: Wed, 10th Feb 2016 13:22 Post subject: |
|
|
cheapest simple product in grouped product
hopefully this is better link
Table.xlsx
|
|
| Back to top |
|
|
|
|
|
| Back to top |
|
|
NFOAC
Posts: 6015
Location: India
|
| Posted: Mon, 15th Feb 2016 05:30 Post subject: |
|
|
Can you post the data here and expected output
|
|
| Back to top |
|
|
|
|
| Posted: Thu, 25th Feb 2016 15:32 Post subject: |
|
|
so you have those 3 prices and want the cheapest one?
| Lutzifer wrote: | | and yes, mine is only average |
|
|
| Back to top |
|
|
|
|
| Posted: Thu, 25th Feb 2016 15:49 Post subject: |
|
|
you can use unions like this
| Quote: | 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)
| Lutzifer wrote: | | and yes, mine is only average |
|
|
| Back to top |
|
|
| Page 1 of 1 |
All times are GMT + 1 Hour |
|
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
|
Powered by phpBB 2.0.8 © 2001, 2002 phpBB Group
|
|
 |
|
