Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
This is a really good question. Or to be more specific, it's a really well-asked question. I wish other questions were as clear. –  Michael J Swart Jun 26 '13 at 20:30
add comment

3 Answers

up vote 2 down vote accepted

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

share|improve this answer
    
Brilliant, thank you so much! I learned a bucket load from all the help on this question! –  Peter PitLock Jun 26 '13 at 16:27
add comment

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     ║
╚═══════════╩═══════╩════════╩══════════╝
share|improve this answer
    
Brilliant, thank you so much! I learned a bucket load from all the help on this question! –  Peter PitLock Jun 26 '13 at 16:26
add comment

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 |
share|improve this answer
    
Brilliant, thank you so much! I learned a bucket load from all the help on this question! –  Peter PitLock Jun 26 '13 at 16:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.