jxharding
asked on
SQL 2008 - Running total partition over Company - for each grouping per Company - run total else reset
Hi, I spent a lot of time looking through a lot of results on google, but can't get exactly what I need even after changing many methods of the examples
select * from #checks
I need help on getting it to (Sort by RowNumber) and reset whenever a company changes please. Thus
Drop Table #Checks
CREATE TABLE #Checks
(
Client VARCHAR(32),
RowNr int,
Amount DECIMAL(12,2)
);
INSERT #Checks(Client, RowNr, Amount)
SELECT 'Company A', '1', 50
UNION ALL SELECT 'Company A', '2', 75
UNION ALL SELECT 'Company A', '3', 120
UNION ALL SELECT 'Company B', '4', 75
UNION ALL SELECT 'Company A', '5', 40
UNION ALL SELECT 'Company B', '6', 200
UNION ALL SELECT 'Company B', '7', 90;
select * from #checks
I need help on getting it to (Sort by RowNumber) and reset whenever a company changes please. Thus
Company A Running Total 50
Company A Running Total 125
Company A Running Total 245
Company B Running Total 75
Company A Running Total 40
Company B Running Total 200
Company B Running Total 290
have you tried the row_number() analytic function?
ASKER
Hi, Row_Number() was used previously yes, any hint on how to use it in this scenario? The RowNumbers are already generated?
this is a quick reply, using a cte to simulate the temp table - but using row_number() to re- sequence the output.
CLIENT AMOUNT ROWNR
Company A 50 1
Company A 75 2
Company A 120 3
Company A 40 4
Company B 75 1
Company B 200 2
Company B 90 3
with #checks (client, RowNr, Amount) as (
SELECT 'Company A', '1', 50
UNION ALL SELECT 'Company A', '2', 75
UNION ALL SELECT 'Company A', '3', 120
UNION ALL SELECT 'Company B', '4', 75
UNION ALL SELECT 'Company A', '5', 40
UNION ALL SELECT 'Company B', '6', 200
UNION ALL SELECT 'Company B', '7', 90
),
rechecks as (
select
client
, amount
, row_number() over (partition by client order by rownr) as rownr
from #checks
)
select
*
from rechecks
order by client, RowNr
;
see http://sqlfiddle.com/#!3/1fa93/7924
>>any hint on how to use it in this scenario? The RowNumbers are already generated?
I hope my previous post answers this
it's really up to you, but I would suggest
row_number() over (partition by client order by RowNr) as <<something you choose>>
the 'partition by client' will re-start the numbers for each client, &
the 'order by rowNr' will leverage the wanted sequence
you can give this calculated column a different alias - i just re-used rowNr above.
I hope my previous post answers this
it's really up to you, but I would suggest
row_number() over (partition by client order by RowNr) as <<something you choose>>
the 'partition by client' will re-start the numbers for each client, &
the 'order by rowNr' will leverage the wanted sequence
you can give this calculated column a different alias - i just re-used rowNr above.
ASKER
Thanks, I think we missed each other on this one unfortunately. I'd like to keep the existing row numbers, and create a running total for each company, until the company changes, which then resets the running total -
e.g.
Company A (First case) Running Total 50
Company A (First case) Running Total 125
Company A (First case) Running Total 245
Company B (Second case) Running Total 75
Company A (Third case) Running Total 40
Company B (Fourth case) Running Total 200
Company B (Fourth case) Running Total 290
e.g.
Company A (First case) Running Total 50
Company A (First case) Running Total 125
Company A (First case) Running Total 245
Company B (Second case) Running Total 75
Company A (Third case) Running Total 40
Company B (Fourth case) Running Total 200
Company B (Fourth case) Running Total 290
Oh, and you don't have to use CTEs, let's assume you continue with the temp table, then it could look like this:
select
client, amount, rowNr, rowNr2
from (
select
client
, amount
, rowNr
, row_number() over (partition by client order by rownr) as rownr2
from #checks
) as rechecks
from rechecks
order by client, RowNr2
running total ....my bad ... hold on ... most sorry
this is SQL Server 2008 - is that correct? (or is it 2012?)
ASKER
SQL Server 2008 yes please, thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is brilliant, thank you very much! Will definitely be able to used this as an example! Thanks again!!
are you sure? it's NOT fully correct
ASKER
I'm sure i'll be able to battle out the last one, way better than anything I was able to get, thank you again!