I have a table where the RowNumber is of essence. It has a range of clients, and a running total for that client - which resets every time the Client changes. e.g.
client rownr amount runtotal Company A 1 1.00 1.00 Company A 2 1.00 2.00 1+1 = 2 Company A 3 5.00 7.00 2+5 = 7 Company B 4 3.00 3.00 Reset Because Customer B <> Previous Row Customer A Company A 5 1.00 1.00 Reset Because Customer A <> Previous Row Customer B Company B 6 2.00 2.00 Reset Because Customer B <> Previous Row Customer A Company B 7 1.00 3.00 2+1 = 3 Company B 8 5.00 8.00 3+5 = 8
I tried using Partition, but it always Groups Client A together, and Client B together- it removes the crucial part of the Row Numbers.
Any help please?
drop table #Checks CREATE TABLE #Checks ( Client VARCHAR(32), RowNr int, Amount DECIMAL(12,2), RunTotal DECIMAL(12,2), Part int ); INSERT #Checks(Client, RowNr, Amount) SELECT 'Company A', '1', 1 UNION ALL SELECT 'Company A', '2', 1 UNION ALL SELECT 'Company A', '3', 5 UNION ALL SELECT 'Company B', '4', 3 UNION ALL SELECT 'Company A', '5', 1 UNION ALL SELECT 'Company B', '6', 2 UNION ALL SELECT 'Company B', '7', 1 UNION ALL SELECT 'Company B', '8', 5; -- gets the first entries per client - these amounts are -- the base amounts and the other entries are tallied up with cte as ( select c1.client , c1.amount , c1.rowNr , case when c1.client <> c2.client then c1.amount else null end amt from #Checks as c1 left join #Checks as C2 on c1.rownr = (c2.rownr + 1) ) select client , rownr , amount , case when isnull(amt,0) > 0 then amt else total end as runtotal from cte cross apply ( select sum(x.amount) as Total from cte as x where x.rownr <= cte.rownr and cte.client = x.client ) as rt ; Drop table #Checks