Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Master-detail reporting question Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 8:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 18, 2013 1:23 PM
Points: 90, 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
Post #1340644
Posted Tuesday, August 7, 2012 6:23 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
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.
Post #1341189
Posted Tuesday, August 7, 2012 9:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 18, 2013 1:23 PM
Points: 90, Visits: 286
That is cool! Thanks!
Post #1341348
Posted Tuesday, August 7, 2012 9:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
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
Post #1341359
Posted Tuesday, August 7, 2012 2:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 18, 2013 1:23 PM
Points: 90, Visits: 286
Thank you! I intentionally posted to see improvements. Improvements I got.
Post #1341554
Posted Tuesday, August 7, 2012 6:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:04 AM
Points: 3,614, Visits: 5,228
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!
Post #1341638
Posted Wednesday, August 8, 2012 12:41 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 18, 2013 1:23 PM
Points: 90, 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.
Post #1342128
Posted Wednesday, August 8, 2012 8:30 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:04 AM
Points: 3,614, Visits: 5,228
I do agree that less is usually better ... except of course when it's not.


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!
Post #1342352
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse