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

#1 clee06  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • 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  Icon User is offline

  • Dreaming Coder
  • member icon


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

Re: SQL recursive to subtract total from rows until depleted

Posted 09 May 2017 - 04:15 AM

View Postclee06, 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
Part_ID     Material_ID Material_Required Quantity_loaded
----------- ----------- ----------------- ---------------
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.
Was This Post Helpful? 0
  • +
  • -

#3 clee06  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • 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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1