September 2, 2014 at 7:53 am
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
September 2, 2014 at 8:16 am
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
September 2, 2014 at 9:20 am
Thanks!
September 2, 2014 at 10:08 am
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