A quick window function solution

😎

USE tempdb;

GO

CREATE TABLE #TotalRevenue_Investments ( [Month] INT,[Year] INT,TotalRevenue INT,Descr VARCHAR(100),Company VARCHAR(100))

INSERT INTO #TotalRevenue_Investments

( Month ,

Year ,

TotalRevenue ,

Descr ,

Company

)

SELECT 1,2014,12,'Late Sales','US Late Sales' UNION

SELECT 2,2014,44, 'Late Sales','US Late Sales' UNION

SELECT 3,2014,297,'Late Sales','US Late Sales' UNION

SELECT 4,2014,122,'Late Sales','US Late Sales' UNION

SELECT 6,2014,5987,'Late Sales','US Late Sales' UNION

SELECT 7,2014,20,'Late Sales','US Late Sales' UNION

SELECT 1,2014,37750,'Late Sales','Canada Late Sales' UNION

SELECT 2,2014,8844,'Late Sales','Canada Late Sales' UNION

SELECT 3,2014,19349,'Late Sales','Canada Late Sales' UNION

SELECT 4,2014,5579,'Late Sales','Canada Late Sales' UNION

SELECT 5,2014,2411,'Late Sales','Canada Late Sales' UNION

SELECT 6,2014,63224,'Late Sales','Canada Late Sales' UNION

SELECT 7, 2014,29759,'Late Sales','Canada Late Sales' UNION

SELECT 1,2014,257,'Late Sales','Latin America' UNION

SELECT 2,2014,9,'Late Sales','Latin America' UNION

SELECT 3,2014,11,'Late Sales','Latin America' UNION

SELECT 4,2014,62,'Late Sales','Latin America' UNION

SELECT 5,2014,176,'Late Sales','Latin America' UNION

SELECT 6,2014,375,'Late Sales','Latin America' UNION

SELECT 7,2014,44,'Late Sales','Latin America'

SELECT

TI.[Month]

,TI.[Year]

,TI.TotalRevenue

,TI.Descr

,TI.Company

,ROW_NUMBER() OVER

(

PARTITION BY TI.Company

,TI.[Year]

ORDER BY TI.[Month]

) AS TI_RID

,SUM(TI.TotalRevenue) OVER

(

PARTITION BY TI.Company

,TI.[Year]

ORDER BY TI.[Month]

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW

) AS CumulativeAmountCurrentYear

FROM #TotalRevenue_Investments TI

DROP TABLE #TotalRevenue_Investments

Results

Month Year TotalRevenue Descr Company TI_RID CumulativeAmountCurrentYear

------ ----- ------------ ----------- ------------------ ------- ---------------------------

1 2014 37750 Late Sales Canada Late Sales 1 37750

2 2014 8844 Late Sales Canada Late Sales 2 46594

3 2014 19349 Late Sales Canada Late Sales 3 65943

4 2014 5579 Late Sales Canada Late Sales 4 71522

5 2014 2411 Late Sales Canada Late Sales 5 73933

6 2014 63224 Late Sales Canada Late Sales 6 137157

7 2014 29759 Late Sales Canada Late Sales 7 166916

1 2014 257 Late Sales Latin America 1 257

2 2014 9 Late Sales Latin America 2 266

3 2014 11 Late Sales Latin America 3 277

4 2014 62 Late Sales Latin America 4 339

5 2014 176 Late Sales Latin America 5 515

6 2014 375 Late Sales Latin America 6 890

7 2014 44 Late Sales Latin America 7 934

1 2014 12 Late Sales US Late Sales 1 12

2 2014 44 Late Sales US Late Sales 2 56

3 2014 297 Late Sales US Late Sales 3 353

4 2014 122 Late Sales US Late Sales 4 475

6 2014 5987 Late Sales US Late Sales 5 6462

7 2014 20 Late Sales US Late Sales 6 6482