# SQL recursive to subtract total from rows until depleted

Page 1 of 1

## 2 Replies - 411 Views - Last Post: 11 May 2017 - 05:43 PM

### #1 clee06

Reputation: 0
• Posts: 68
• Joined: 01-June 10

# SQL recursive to subtract total from rows until depleted

Posted 08 May 2017 - 07:51 PM

Hi all,

I'm trying to learn and understand how to use a recursive to loop through and subtract the total in table X from table Y rows. For example Table X Material_ID (1) has a total of 200 quantity, and Table Y has 5 rows that will use Material_ID (1) quantity to create new parts.

Example:

```Table X

Material_ID | Quantity
-----------------------
1           | 200
2           | 100

Table Y

Part_ID | Material_ID | Material_Required | Quantity_loaded
------------------------------------------------------------
10      | 1           | 20                |
20      | 1           | 50                |
30      | 1           | 50                |
40      | 1           | 90                |
50      | 1           | 30                |
60      | 2           | 110               |
70      | 2           | 20                |

This is what I'm trying to get. In Table X, Material_ID (1) total is 200, and Table Y, the total material_required for all the Part_IDs with Material_ID (1) is 240. I know how I can return the Quantity_Needed from each rows as shown below.

Part_ID | Material_ID | Material_Required | Quantity_Needed
------------------------------------------------------------
10      | 1           | 20                | 0
20      | 1           | 50                | 0
30      | 1           | 50                | 0
40      | 1           | 90                | 10
50      | 1           | 30                | 30
60      | 2           | 110               | 10
70      | 2           | 20                | 20

```

From what I have read and understand, recursive can be done in a couple of ways. Using CTE or multiple CROSS APPLY.

This is as far as I got with understanding CTE syntax. My code error on execution. What did I do wrong?

```;with Material_CTE
(
Part_ID,
Material_ID,
Material_Quantity,
Quantity_Needed
)
as
(
select
Y.Part_ID,
Y.Material_ID,
X.Quantity,
null
from tableY as Y
join tableX as X
on X.Material_ID = Y.Material_ID

union all

select
case
when Material_CTE.Quantity >= Y2.Material_Required then Material_CTE.Quantity - Y2.Material_Required
when Material_CTE.Quantity < Y2.Material_Required then Y2.Material_Required - Material_CTE.Quantity
end as Quantity_Needed
from tableY as Y2
)
select
M.Part_ID,
M.Material_ID,
M.Material_Quantity,
M.Quantity_Needed
from Material_CTE as M
where M.Quantity not null

```

I would like to know and learn other methods too, besides CTE. Partition was something I came across but it looks confusing. A friend said that I could try to do it without CTE and use cross apply, but my result was subtracting from the full total everything.

This post has been edited by clee06: 08 May 2017 - 07:55 PM

Is This A Good Question/Topic? 0

## Replies To: SQL recursive to subtract total from rows until depleted

### #2 baavgai

• Dreaming Coder

Reputation: 6996
• Posts: 14,635
• Joined: 16-October 07

## Re: SQL recursive to subtract total from rows until depleted

Posted 09 May 2017 - 04:15 AM

clee06, on 08 May 2017 - 09:51 PM, said:

I'm trying to learn and understand how to use a recursive to loop through

Forgive me, but this doesn't scan at all. There is really no recursion in SQL. And loops are generally a bad idea.

I'm a DBA, been doing it for decades, and have never used CTE. Seriously, never.

Alas, I couldn't get yours to work. You seem to be calling Material_CTE before you've created it, e.g. when Material_CTE.Quantity >= Y2.Material_Required then Material_CTE.Quantity - Y2.Material_Required

I'm afraid I'm not real sure what you're after with this. I made a couple of temp tables so I could play along.
```create table #x ( Material_ID int, Quantity int)

create table #y ( Part_ID int, Material_ID int, Material_Required int, Quantity_loaded int )

insert into #x values(1, 200), (2, 100)
(2 row(s) affected)

insert into #y(Part_ID, Material_ID, Material_Required) values(10, 1, 20),
(20, 1, 50),(30, 1, 50),(40, 1, 90),(50, 1, 30),(60, 2, 110),(70, 2, 20)
(7 row(s) affected)

select * from #x
Material_ID Quantity
----------- -----------
1           200
2           100
(2 row(s) affected)

select * from #y
----------- ----------- ----------------- ---------------
10          1           20                NULL
20          1           50                NULL
30          1           50                NULL
40          1           90                NULL
50          1           30                NULL
60          2           110               NULL
70          2           20                NULL

(7 row(s) affected)

```

So, if you wanted the total quantity of materials for Material_ID, you do something like:
```select Material_ID, sum(Material_Required) as Material_Required
from #y
group by Material_ID

Material_ID Material_Required
----------- -----------------
1           240
2           130

(2 row(s) affected)

```

Note the group by: you should know this cold.

If you wanted to roll that into your other table, you could do:
```select a.*, b.Material_Required
from #x a
join (
select Material_ID, sum(Material_Required) as Material_Required
from #y
group by Material_ID
) b on a.Material_ID=b.Material_ID

Material_ID Quantity    Material_Required
----------- ----------- -----------------
1           200         240
2           100         130

(2 row(s) affected)

```

Note the sub query here. A CTE is essentially an attempt to avoid a temp table or a subquery. You should know sub queries. You needn't know CTEs.

If you want to do what the CTE seems to be attempting to do, I suppose it would look like:
```select
Y.Part_ID,
Y.Material_ID,
X.Quantity,
case
when X.Quantity >= Y.Material_Required then X.Quantity - Y.Material_Required
when X.Quantity < Y.Material_Required then Y.Material_Required - X.Quantity
end as Quantity_Needed
from #y as Y
join #x as X
on X.Material_ID = Y.Material_ID

Part_ID     Material_ID Quantity    Quantity_Needed
----------- ----------- ----------- ---------------
10          1           200         180
20          1           200         150
30          1           200         150
40          1           200         110
50          1           200         170
60          2           100         10
70          2           100         80

(7 row(s) affected)

```

Again, the logic of what you're trying to do escapes me. Sorry.

Hope this helps.

### #3 clee06

Reputation: 0
• Posts: 68
• Joined: 01-June 10

## Re: SQL recursive to subtract total from rows until depleted

Posted 11 May 2017 - 05:43 PM

Thank you. I'll try to work it out.