Results 1 to 7 of 7

Thread: [RESOLVED] SQL Server Query - SUM based on child rows and another column's value

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Resolved [RESOLVED] SQL Server Query - SUM based on child rows and another column's value

    Here's another crazy query I am working on.

    I need to examine the resultset below that I've attached and sum the extended_price based on the logic I will describe next.

    You can see the parent child (p/c) relationship. The 3rd row is a child of the 2nd row. There are no other children.
    Because the child's disposition = S, I want to exclude that row's extended price *and* the extended_price of its parent, and the total sum is simply the one non-zero row of 872.31. This will expand to other dispositions as well: C, B and H.
    If the child was disposition = A, I would exclude it but include its parent.
    If no child rows, add all parents based on disposition (I got that part at least, see query so far).

    So here's what I am starting out with but need to examine p/c relationships.

    Code:
    select	sum (CASE 
    			WHEN LINE.disposition != 'C' 
    				AND LINE.disposition != 'B' 
    				AND LINE.disposition!= 'S' 
    				AND LINE.disposition != 'H' 
    			THEN LINE.extended_price 
    			ELSE 0 
    			END) as [Allocated Amount] 
    from p21_view_oe_line as LINE
    where order_no = '1068629'
    and LINE.customer_part_number != 'PRJT_APPLY'
    I am also continuing to work on this so I'll post back any progress and thank you if you have time to take a look at it.
    Attached Images Attached Images  
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query - SUM based on child rows and another column's value

    Code:
    WITH CT1 AS (SELECT oe_line_uid, extended_price FROM p21_view_oe_line WHERE disposition='S'),
         CT2 AS (SELECT P1.oe_line_uid, P1.extended_price FROM p21_view_oe_line AS P1 
                INNER JOIN p21_view_oe_line AS P2 ON P2.parent_oe_line_uid=P1.oe_line_uid)
    SELECT SUM(CASE WHEN ct1.oe_line_uid is null AND ct2.oe_line_uid is null 
               THEN LINE.extended_price ELSE 0 END) As TotalAmount
    from p21_view_oe_line as LINE 
    LEFT JOIN CT1 
    ON CT1.oe_line_uid=LINE.oe_line_uid
    LEFT JOIN CT2 
    ON CT2.oe_line_uid=LINE.oe_line_uid
    where LINE.order_no = '1068629'
    and LINE.customer_part_number <> 'PRJT_APPLY'
    EDIT: In your own Query you have to check for NULL
    cleaned up
    Code:
    select    sum (CASE WHEN LINE.disposition NOT IN ('C', 'B', 'S', 'H')
                OR LINE.disposition IS NULL            
                THEN LINE.extended_price 
                ELSE 0 
                END) as [Allocated Amount] 
    from p21_view_oe_line as LINE
    where order_no = '1068629'
    and LINE.customer_part_number != 'PRJT_APPLY'
    Last edited by Zvoni; Feb 23rd, 2023 at 03:39 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - SUM based on child rows and another column's value

    Yes, thank you, I do know about checking for nulls. I temporarily took it out to make the code less cluttered for my tired eyes.
    I will run this later today or tomorrow. I'm working on something else that nudge this aside.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Server Query - SUM based on child rows and another column's value

    Quote Originally Posted by MMock View Post
    Yes, thank you, I do know about checking for nulls. I temporarily took it out to make the code less cluttered for my tired eyes.
    I will run this later today or tomorrow. I'm working on something else that nudge this aside.
    Beware: I've only gone for this. Also didn't have the time for the "rest" (and honestly, i didn't understood it entirely)
    You can see the parent child (p/c) relationship. The 3rd row is a child of the 2nd row. There are no other children.
    Because the child's disposition = S, I want to exclude that row's extended price *and* the extended_price of its parent, and the total sum is simply the one non-zero row of 872.31.
    EDIT
    and the total sum is simply the one non-zero row of 872.31.
    It should actually read the "two rows, consisting of 872.31 and 0.00" --> oe_line_uid = 959905 und 959908
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - SUM based on child rows and another column's value

    Yes, I will digest and analyze this of course to know if it's correct. SOmetimes I just need to get started and can take it from there. Will let you know. Thanks.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Query - SUM based on child rows and another column's value

    OK, so I've finished my query. It is super long and complicated and it works. I am going to print it and hang it on my refrigerator at home :-) (Is that just a thing we do in the USA, hang our children's school work on the fridge???)
    Anyway, it's complicated so I'd rather not explain it all I just want to say thank you, you've helped me conquer my fear / improve my appreciation of CTE's. It wasn't until I needed to do this that I really delved into them. You guys (ZVoni here and tg at other times) helped point me in the right direction.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: [RESOLVED] SQL Server Query - SUM based on child rows and another column's value

    Think of CTE‘s like temp. Views, with the same downside, that you can‘t use parameters or variables inside.

    I use CTE‘s extensively if i have „hard“filters, just to reduce the amount of basedata for that particular criteria
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width