Cumulative Amount

  • Hi,

    I need to calculate cum amount from the following table.

    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 * FROM #TotalRevenue_Investments

    DROP TABLE #TotalRevenue_Investments

    FOR January, the Cumulative Amount OF CURRENT YEAR FOR 'US Late Sales' should be 12

    FOR January, the Cumulative Amount OF CURRENT YEAR FOR 'Latin America' should be 257

    FOR January, the Cumulative Amount OF CURRENT YEAR FOR 'Canada Late Sales' should be 37750

    FOR February, the Cumulative Amount OF CURRENT YEAR FOR 'US Late Sales' should be 12 + 44

    FOR February, the Cumulative Amount OF CURRENT YEAR FOR 'Latin America' should be 257 + 9

    FOR February, the Cumulative Amount OF CURRENT YEAR FOR 'Canada Late Sales' should be 37750 + 8844

    FOR March, the Cumulative Amount OF CURRENT YEAR FOR 'US Late Sales' should be 56 + 297

    FOR March, the Cumulative Amount OF CURRENT YEAR FOR 'Latin America' should be 266 + 11

    FOR March, the Cumulative Amount OF CURRENT YEAR FOR 'Canada Late Sales' should be 46594 + 19349

    AND so ON ..

    I need the OUTPUT AS FOR example

    SELECT 1 AS Month,2014 AS Year,12 AS cumAmt,'Late Sales' AS Descr,'US Late Sales' AS Company

    Thanks,

    PSB

  • take a look at the windowing functions in 2012

    something along these line may help you get started

    SELECT

    Month

    , Year

    , TotalRevenue

    , Company

    , Descr

    , SUM(totalrevenue) OVER (PARTITION BY company ORDER BY year , month) AS rt

    FROM TotalRevenue_Investments;

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Thanks!

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply