SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Master-detail reporting question


Master-detail reporting question

Author
Message
MissyDaisy
MissyDaisy
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 286
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
laurie-789651
laurie-789651
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1462 Visits: 1272
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.
MissyDaisy
MissyDaisy
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 286
That is cool! Thanks!
mtassin
mtassin
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7288 Visits: 72521

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





--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
MissyDaisy
MissyDaisy
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 286
Thank you! I intentionally posted to see improvements. Improvements I got.
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17909 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
MissyDaisy
MissyDaisy
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 286
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.
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17909 Visits: 6431
I do agree that less is usually better ... except of course when it's not. :-P


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search