New answers tagged

2

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 ...


0

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 ...


1

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 ...


0

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 from ( select ctr_id, mth , sum(pmt_amt) over (partition by ctr_id order by mth) as cumulative_pmt_amt ...


2

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) or (tb....


Top 50 recent answers are included