T-SQL to sum total value instead of rejoining table multiple times

Question

I've looked for an example question like this, I ask for grace if it's been answered (I thought it would have been but have a hard time finding meaningful results with the terms I searched.)

I work at a manufacturing plant where at ever manufacturing operation a part is issued a new serial number. The database table I have to work with has the serial number recorded in the Container field and the previous serial number the part had recorded in the From_Container field.

I'm trying to SUM the Extended_Cost column on parts we've had to re-do operations on.

Here's a sample of data from tbl_Container:

Container   From_Container  Extended_Cost   Part_Key Operation
10                       9  10               PN_100  60
9                        8  10               PN_100  50
8                        7  10               PN_100  40
7                        6  10               PN_100  30
6                        5  10               PN_100  20
5                        4  10               PN_100  50
4                        3  10               PN_100  40
3                        2  10               PN_100  30
2                        1  10               PN_100  20
1                      100  10               PN_100  10

In this example the SUM I would expect returned is 40, because operations 20, 30, 40 and 50 were all re-done and cost $10 each.

So far I've been able to do this by rejoining the table to itself 10 times using aliases in the following fashion:

      LEFT OUTER JOIN   tbl_Container  AS  FCP_1
                  ON    tbl_Container.From_Container = FCP_1.Container
                  AND   FCP_1.Operation       <= tbl_Container.Operation
                  AND   tbl_Container.Part_Key       = FCP_1.Part_Key

And then using SUM to add the Extended_Cost fields together. However, I'm violating the DRY principle and there has got to be a better way.

Thank you in advance for your help,

Me

Answer 1

You can try this query.

;WITH CTE AS
(
    SELECT TOP 1 *, I = 0 FROM tbl_Container C ORDER BY Container 
    UNION ALL
    SELECT T.*, I = I + 1 FROM CTE 
        INNER JOIN tbl_Container T 
            ON CTE.Container = T.From_Container 
            AND CTE.Part_Key = T.Part_Key
)
SELECT Part_Key, SUM(T1.Extended_Cost) Sum_Extended_Cost FROM CTE T1
WHERE 
    EXISTS( SELECT * FROM 
            CTE T2 WHERE 
            T1.Operation = T2.Operation 
            AND T1.I > T2.I ) 
GROUP BY Part_Key

Result:

Part_Key   Sum_Extended_Cost
---------- -----------------
PN_100     40