-
Feb 22nd, 2023, 12:20 PM
#1
Thread Starter
PowerPoster
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Feb 23rd, 2023, 03:34 AM
#2
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
-
Feb 23rd, 2023, 06:25 AM
#3
Thread Starter
PowerPoster
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.
-
Feb 23rd, 2023, 06:27 AM
#4
Re: SQL Server Query - SUM based on child rows and another column's value
Originally Posted by MMock
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
-
Feb 23rd, 2023, 03:07 PM
#5
Thread Starter
PowerPoster
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.
-
Mar 3rd, 2023, 01:04 PM
#6
Thread Starter
PowerPoster
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.
-
Mar 4th, 2023, 03:25 AM
#7
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|