Running Total which Resets when Customer changes (keeping existing RowNumbers)- attempt included

Question

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

Answer 1

How about:

SELECT c.client, c.rownr, c.amount
     , runtotal = SUM(e.amount)
FROM   #Checks c
JOIN   #Checks e ON e.RowNr <= c.RowNr AND e.Client = c.Client -- row from c (note <= not <) and all earlier rows
LEFT OUTER JOIN -- rows between with different cli
       #Checks d ON d.RowNr < c.RowNr AND d.RowNr > e.RowNr AND d.Client<>c.Client
WHERE d.RowNr IS NULL -- drop where there is a row between the one from c and the one from e with different client
GROUP BY c.client, c.rownr, c.amount
ORDER BY c.rownr

That first join could be expensive on a large dataset if an index on client (to be efficient at all you'll need an index on client,rownr and one on rownr (though I'm assuming that would be your PK which would imply an index already)) is not very selective (if the variety of data in the client column and it's spread with respect to rown means that an index based upon it isn't very selective then that first join will be as near to a cartesian product as makes no odds).

Answer 2

This should do what you want:

WITH CTE AS
(
    SELECT  *, 
            RN = ROW_NUMBER() OVER(PARTITION BY Client ORDER BY RowNr)
    FROM #Checks
), CTE2 AS
(
    SELECT  *, 
            RN2 = RowNr - RN 
    FROM CTE 
)
SELECT A.Client, A.RowNr, A.Amount, B.RunTotal
FROM CTE2 A
CROSS APPLY (SELECT SUM(Amount) RunTotal
             FROM CTE2
             WHERE Client = A.Client
             AND RN2 = A.RN2
             AND RowNr <= A.RowNr) B
ORDER BY RowNr

Results:

╔═══════════╦═══════╦════════╦══════════╗
║  Client   ║ RowNr ║ Amount ║ RunTotal ║
╠═══════════╬═══════╬════════╬══════════╣
║ Company A ║     1 ║ 1.00   ║ 1.00     ║
║ Company A ║     2 ║ 1.00   ║ 2.00     ║
║ Company A ║     3 ║ 5.00   ║ 7.00     ║
║ Company B ║     4 ║ 3.00   ║ 3.00     ║
║ Company A ║     5 ║ 1.00   ║ 1.00     ║
║ Company B ║     6 ║ 2.00   ║ 2.00     ║
║ Company B ║     7 ║ 1.00   ║ 3.00     ║
║ Company B ║     8 ║ 5.00   ║ 8.00     ║
╚═══════════╩═══════╩════════╩══════════╝

Answer 3

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.Checks
(
  Client VARCHAR(32),
  RowNr int,
  Amount DECIMAL(12,2),
  RunTotal DECIMAL(12,2),
  Part int
);

INSERT dbo.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;

Query 1:

WITH Islands AS
(
  SELECT Client, RowNr, Amount, RowNr-ROW_NUMBER()OVER(PARTITION BY Client ORDER BY RowNr) IslId
  FROM Checks
)


SELECT *
FROM Islands I1
CROSS APPLY(
  SELECT SUM(Amount) RunningTotal
  FROM Islands I2
  WHERE I1.Client = I2.Client
  AND I1.IslId = I2.IslId
  AND I1.RowNr >= I2.RowNr
)C

Results:

|    CLIENT | ROWNR | AMOUNT | ISLID | RUNNINGTOTAL |
-----------------------------------------------------
| Company A |     1 |      1 |     0 |            1 |
| Company A |     2 |      1 |     0 |            2 |
| Company A |     3 |      5 |     0 |            7 |
| Company A |     5 |      1 |     1 |            1 |
| Company B |     4 |      3 |     3 |            3 |
| Company B |     6 |      2 |     4 |            2 |
| Company B |     7 |      1 |     4 |            3 |
| Company B |     8 |      5 |     4 |            8 |