No, SQL Server only currently supports table-valued constructors in a derived table (or the VALUES clause of an INSERT ... VALUES statement). So, you can use VALUES in a join (including apply) or derived table ("subquery") but not a Common Table Expression.
Sadly, T-SQL is lagging in several areas compared with other popular SQL databases, for example Add ...
You can use a recursive CTE to produce a running total of the quantity field and then simply return the MAX value grouped by ComponentId where the ComponentId is not also in ItemId. This eliminates any components that have child components and shows the highest calculated value from the recursive CTE.
This should work regardless of how many levels of ...
After looking at custom aggregate functions, I realized that was the appropriate way to solve the issue. In case anyone else comes across this question and would like to see how I solved it:
CREATE OR REPLACE FUNCTION sfunc_outstanding_amount_with_carry
previous_row NUMERIC , -- previous row output
current_row NUMERIC -- current ...
This is what I intended to say in my comment:
select ctr_id, mth, cumulative_pmt_amt
, coalesce(cumulative_amt, 0) as cumulative_amt
, coalesce(cumulative_amt, 0) - cumulative_pmt_amt as balance
select ctr_id, mth
, sum(pmt_amt) over (partition by ctr_id
order by mth) as cumulative_pmt_amt
My solution does only 1 scan of #TempBucket but it adds an additional sort, I'm not sure it's more efficient, you should test it on volumes of data:
;with cte as
select t.*, tb.id as BucketId,
row_number() over(partition by t.id order by tb.FromVal) as rn
from #TempData t
join #TempBucket tb
on (t.value = tb.filter)