CODEWITHSUNDEEP
sqlt-sqlsql-server-2016
MISNole
MISNole

Reputation: 1062

Calculating Running Totals - TSQL?

I am trying to calculate running totals over a time period with each time period rolling up into the next period for a cumulative total.

There will be multiple events in the real data along with multiple event types and cost types - but in the example below, I'm only showing one event and one type. If I can get this to work, I can make it work for the other types as well.

The screenshot below is my expected output:

enter image description here

I would like to sum the amounts for each month's number for both types of reserves: Expense & Indemnity - so month 1 would have a total of $31.7k. Month 2 has a total of approximately $4.1k so that would be added to the prior months' total giving me 35.9k. And this running total should continue on to the last record.

I am trying various ways to sum through a window function but so far I am unable to get the expected outcome. Any suggestions on how to achieve this total?

Sample data is found below:

CREATE TABLE #temptable
( Catastrophe VARCHAR (60), Type VARCHAR (256), CostType VARCHAR (256), FirstLossDate DATE, MonthNumber INT, Amount DECIMAL (38, 6) );

INSERT INTO #temptable
( Catastrophe, Type, CostType, FirstLossDate, MonthNumber, Amount) 
VALUES
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 1, 13460.320000),
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 1, 18314.610000),
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 2, -1589.340000),
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 2, 5750.000000),
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 3, -2981.250000),
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 3, -10000.000000),
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 4, 0.000000),
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 4, 0.000000),
('Hurricane Humberto', 'Reserve', 'Expense - A&O', N'2007-09-13', 5, 0.000000),
('Hurricane Humberto', 'Reserve', 'Indemnity', N'2007-09-13', 5, 0.000000);

SELECT Catastrophe,
       Type,
       CostType,
       FirstLossDate,
       MonthNumber,
       Amount,
       SUM ( Amount ) OVER (PARTITION BY Catastrophe, MonthNumber, Type ORDER BY MonthNumber ROWS UNBOUNDED PRECEDING  ) AS RunningTotals,
       SUM ( Amount ) OVER (PARTITION BY Catastrophe, Type, MonthNumber) AS RunningTotal2
FROM #temptable
ORDER BY Catastrophe,
         Type,
         MonthNumber;

DROP TABLE #temptable;

Upvotes: 1

Views: 85

Answers (1)

Georgina Skibinski
Georgina Skibinski

Reputation: 13407

Try this one:

SELECT Catastrophe,
       Type,
       CostType,
       FirstLossDate,
       MonthNumber,
       Amount,
       SUM(Amount) OVER (PARTITION BY Catastrophe, Type ORDER BY MonthNumber asc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS RunningTotals
FROM 
       temptable
ORDER BY 
       Catastrophe,
       Type,
       MonthNumber;

I think your main problem was with partition by clause - it is meant to "reset" the calculation - so you can calculate windowing function for many groups.

As per documentation:

PARTITION BY Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

REF

SQL FIDDLE

Upvotes: 1

Related Questions