how to get query value from 1 row to use to another row?

Question

This is example query:

payment_Type    payment_value       cost_type      cost value
Cost I          100                 Registration       40
Cost I          100                 books              40
Cost I          100                 Lab                40

The COST I has 3 elements Cost_Type that have their own Cost_value.

I want to manipulate like this:

payment_Type    payment_value       cost_type      cost value     Payment_by_cost_type
Cost I          100                 Registration       40              40
Cost I          100                 books              40              40
Cost I          100                 Lab                40              20

The point is the I want to divided the payment_value into each cost_value. In the example the payment_by_cost becomes 40, 40, 20 = 100.

The lab cost_value is 40 but it can assign value is 20 because remains from the divided 2 cost type above.

Is it possible that I can use the value from Payment_by_cost_type in the next row record? I have been trying to insert the value Payment_by_cost_type to a temporary table but select cannot have insert statement.

Does anyone have any ideas on how to solve this? I've been consulting to DWH he said it must using Store procedure it cannot done by query.

Answer 1

I guess your table contains not only "Cost I" but other values so here is a query to output results for all groups (by Payment_type) in the table:

;with table1 as
(select 
t.*,
row_number() 
 OVER 
(PARTITION BY payment_Type order by cost_type) rn
from t
)
,table2 as
( select t4.*,isnull((select sum(cost_value) from table1 
    where table1.payment_type=t4.payment_type and rn<t4.rn),0) CumSum 
  from table1 t4
) 
select payment_type,payment_value,cost_type,cost_value,
case when cost_value+CumSum<=payment_value then cost_value
  else 
    payment_value-Cumsum                  
  end                     

from table2
order by Payment_type,rn;

SQLFIDDLE demo

Answer 2

You need to define some kind of order for your records to define in which order the payments should be applied

Once you have done that (i'm using ID in this example)...

select *
    , case
          when payment_value-(select isnull(SUM(cost_value),0) from yourtable t2 where t2.id<t1.id)<cost_value
          then payment_value-(select isnull(SUM(cost_value),0) from yourtable t2 where t2.id<t1.id)
          else cost_value
          end
from yourtable t1