Performing Recursive SQL Query (SQL Server 2005)

Question

I have this table:

ID_Articulo ID_Componente   Tipo    Cantidad
1           5               2       1.5
5           3               1       3
1           6               1       6
2           3               1       3.5
6           8               2       4.2
8           9               1       2

I need to find the SUM of Cantidad field for a given ID_Articulo and Tipo=1. For instance, for ID_Articulo=1 should be 1.5*3 + 6 + 6*4.2*2 = 60.9.

The number to level (deep) are variable for each ID_Articulo.

Can this be donw with a SQL query? My database is SQL Server 2005.

Aditional info

Fields ID_Articulo AND ID_Compenente are related to the same table Articulos. Thats why the data is recursive. So, for the given example I have:

Rec #1 is not Tipo=1, but relates ID 1 with ID 5
Rec #2 relates ID 5 with ID 3 and Tipo=1 So, I have 1.5 (of Rec #1) * 3
Rec #3 relates ID 1 with ID 6 and Tipo=1, so i have 6
Rec #4 is from another ID
Rec #5 relates ID 6 with ID 8 but Tipo!=1 so i don't sum
Rec #6 relates ID 8 and ID 9 and Tipo=1, so I have 6 * 4.2 * 2

Final solution

This is the final code (Some field names are different):

;WITH CTE AS (
SELECT b.ID_Articulo, ID_Componente, Tipo, Cantidad,   
      CAST(Cantidad AS DECIMAL(6,2)) AS partialSum
FROM Arbol b inner join articulos a on a.ID_Articulo=b.ID_Componente
WHERE b.ID_Articulo = 2716

UNION ALL

SELECT t.ID_Articulo, t.ID_Componente, t.Tipo, t.Cantidad,
       CAST(c.partialSum * t.Cantidad AS DECIMAL(6,2)) AS partialSum
FROM (SELECT b.ID_Articulo, ID_Componente, A.Tipo, Cantidad FROM Arbol b inner join articulos a on a.ID_Articulo=b.ID_Componente inner join Articulos a2 on a2.ID_Articulo=b.ID_Articulo where a2.Tipo<>'I') as t
INNER JOIN CTE AS c ON c.ID_Componente = t.ID_Articulo
)
SELECT SUM(partialSum)
FROM CTE   
WHERE Tipo = 'I'

Thanks @giorgos-betsos for final solution

I don't see how you get this sum for ID_Articulo = 1 and the sample table you have provided. I also don't see why this is recursive? — Paddy
– Paddy, Commented Dec 2, 2015 at 14:00
Yes, why is 1.5 * 3 part of your equation? The only 1.5 is in the first row, where tipo is 2, so according to the description, it shouldn't be included. Your example only makes your question less clear. — Tab Alleman
– Tab Alleman, Commented Dec 2, 2015 at 14:05
As posted your question makes no sense. It is clear to you but to anybody else it is just gibberish. Your rows appear to be somehow related to each other but it is through smoke and mirrors because there is nothing that clearly defines it. In your sample data you don't even have anything that could be a primary key to identify a given row. — Sean Lange
– Sean Lange, Commented Dec 2, 2015 at 14:31

Giorgos Betsos · Accepted Answer · 2015-12-02 14:46:34Z

You can use the following recursive CTE to get expected result:

;WITH CTE AS (
   SELECT ID_Articulo, ID_Componente, Tipo, Cantidad,   
          CAST(Cantidad AS DECIMAL(6,1)) AS partialSum
   FROM mytable 
   WHERE ID_Articulo = 1

   UNION ALL

   SELECT t.ID_Articulo, t.ID_Componente, t.Tipo, t.Cantidad,
          CAST(c.partialSum * t.Cantidad AS DECIMAL(6,1)) AS partialSum
   FROM mytable AS t
   INNER JOIN CTE AS c ON t.ID_Articulo = c.ID_Componente
)
SELECT SUM(partialSum)
FROM CTE   
WHERE Tipo = 1

What the above recursion does, is that it returns all branches emanating from ID_Articulo = 1, along with the cumulative product of the multiplication of Cantidad values:

 ID_Articulo    ID_Componente   Tipo    Cantidad    partialSum
------------------------------------------------------------------
 1              5               2       1.5         1.5
 1              6               1       6.0         6.0
 6              8               2       4.2         25.2
 8              9               1       2.0         50.4
 5              3               1       3.0         4.5

The result is calculated using the SUM on partialSum only for those rows where Tipo = 1.

Demo here

You are the man! How is it possible to guess what was required??
@GiorgiNakeuri I'm just taking a wild guess. Only the OP can verify if the above solution is what his/her true intention is!
Very close! I evaluate this with real data and tell you how it goes!
Yes! After some adapting, it Works great! thanks! I'll post the final result

Collectives™ on Stack Overflow

Performing Recursive SQL Query (SQL Server 2005)

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related