August 6, 2012 at 8:13 am
Requirement:
A product line can have 1 or many products. When a product line has all its products out of stock, report it.
My implementation: use subqueries.
My question: Are there alternatives to achieve this requirement that are better than my implementation? Thanks
-----------------------------------------------------------------------
test data:
declare @ProductLine table
(ProductlineID int)
declare @Products table
(ProductLineID int,
ProductID int,
Status int)
insert into @ProductLine values (1)
Insert into @ProductLine values (2)
Insert into @ProductLine values (3)
Insert into @ProductLine values (4)
Insert into @Products values (1,100, 1)
insert into @Products values (1,200, 2)
Insert into @Products values (1,300, 1)
insert into @Products values (1,400, 2)
Insert into @Products values (2,1000, 1)
insert into @Products values (2,2000, 1)
Insert into @Products values (2,3000, 1)
insert into @Products values (3,10, 2)
insert into @Products values (4,1, 2)
insert into @Products values (4,2, 2)
-- find all product lines with all products out of stock. A product is out of stock has status=2
-- From input above, product lines 3 and 4 will be reported.
select B.productlineid, B.NumberofProductsOutofStock
from
(
select distinct productlineID, COUNT(*) as NumberofProducts from @Products
group by productlineID) A
inner join
(
select distinct productlineID, COUNT(*) as NumberofProductsOutofStock from @Products where Status=2
group by productlineID) B
on A.ProductLineID = B.ProductLineID
where A.NumberofProducts = B.NumberofProductsOutofStock
August 7, 2012 at 6:23 am
Here are a couple of ideas:
This uses cross apply to get the data in a single query - but the 'distinct' is a bit clunky.
select distinct
P.productlineid,
OOS.NumberofProductsOutofStock
from
@Products P
cross apply (select count(*) from @Products P1 where P1.productlineid=P.productlineid) NOP (NumberofProducts)
cross apply (select count(*) from @Products P2 where P2.productlineid=P.productlineid
and P2.Status = 2) OOS (NumberofProductsOutofStock)
where NOP.NumberofProducts = OOS.NumberofProductsOutofStock;
I separated out the 'distinct' into a subquery as shown below:
select
P.productlineid,
OOS.NumberofProductsOutofStock
from
(
select distinct
productlineid
from
@Products
) P
cross apply (select count(*) from @Products P1 where P1.productlineid=P.productlineid) NOP (NumberofProducts)
cross apply (select count(*) from @Products P2 where P2.productlineid=P.productlineid
and P2.Status = 2) OOS (NumberofProductsOutofStock)
where NOP.NumberofProducts = OOS.NumberofProductsOutofStock;
According to the actual execution plans, both of these are a bit more efficient than the original, at the ratio of about 40% of the batch compared to 60% for the original.
August 7, 2012 at 9:18 am
That is cool! Thanks!
August 7, 2012 at 9:37 am
declare @ProductLine table
(ProductlineID int)
declare @Products table
(ProductLineID int,
ProductID int,
Status int)
insert into @ProductLine values (1)
Insert into @ProductLine values (2)
Insert into @ProductLine values (3)
Insert into @ProductLine values (4)
Insert into @Products values (1,100, 1)
insert into @Products values (1,200, 2)
Insert into @Products values (1,300, 1)
insert into @Products values (1,400, 2)
Insert into @Products values (2,1000, 1)
insert into @Products values (2,2000, 1)
Insert into @Products values (2,3000, 1)
insert into @Products values (3,10, 2)
insert into @Products values (4,1, 2)
insert into @Products values (4,2, 2)
-- find all product lines with all products out of stock. A product is out of stock has status=2
-- From input above, product lines 3 and 4 will be reported.
SELECT
a.ProductLineID,
Productsoutofstock = SUM(CASE b.Status WHEN 2 THEN 1 ELSE 0 END)
FROM
@ProductLine a JOIN @Products b ON
a.ProductlineID = b.ProductLineID
WHERE
NOT EXISTS (SELECT 1 FROM @Products c WHERE a.ProductlineID = c.ProductLineID AND c.status != 2)
GROUP BY
a.ProductlIneid
August 7, 2012 at 2:44 pm
Thank you! I intentionally posted to see improvements. Improvements I got.
August 7, 2012 at 6:48 pm
This seems a bit more succinct:
SELECT productlineid, NumberofProductsOutofStock=COUNT(productlineid)
FROM @Products
GROUP BY productlineid
HAVING COUNT(CASE Status WHEN 2 THEN 1 ELSE NULL END) = COUNT(productlineid)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 8, 2012 at 12:41 pm
Leonardo Da Vinci said "simplicity is the ultimate sophistication." I did make things more complicated than they are, didn't I? Still have to learn to look at things as they are no more no less... Thank you very much for the solution and as well as the lesson.
August 8, 2012 at 8:30 pm
I do agree that less is usually better ... except of course when it's not. 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply