Master-detail reporting question

  • 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

  • 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.

  • That is cool! Thanks!

  • 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[/url]
    For tips on how to post your problems[/url]

  • Thank you! I intentionally posted to see improvements. Improvements I got.

  • 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![/I]

    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

  • 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.

  • 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![/I]

    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