1

I'm using SQL Server 2014. I have a Claims table containing totals of claims made per month in my system:

+-----------+-------------+------------+
| Claim_ID  | Claim_Date  | Nett_Total |
+-----------+-------------+------------+
| 1         | 31 Jan 2012 |  321454.67 |
| 2         | 29 Feb 2012 |  523542.34 |
| 3         | 31 Mar 2012 |   35344.33 |
| 4         | 30 Apr 2012 |  142355.63 |
| etc.      | etc.        | etc.       |
+-----------+-------------+------------+

For a report I am writing I need to be able to produce a cumulative running total that resets to zero at the start of each fiscal year (in my country this is from March 1 to February 28/29 of the following year).

The report will look similar to the table, with an extra running total column, something like:

+-----------+-------------+------------+---------------+
| Claim_ID  | Claim_Date  | Nett_Total | Running Total |
+-----------+-------------+------------+---------------+
| 1         | 31 Jan 2012 |  321454.67 |     321454.67 |
| 2         | 29 Feb 2012 |  523542.34 |     844997.01 |
| 3         | 31 Mar 2012 |   35344.33 |      35344.33 | (restart at 0
| 4         | 30 Apr 2012 |  142355.63 |     177699.96 |  for new yr) 
| etc.      | etc.        | etc.       |               |
+-----------+-------------+------------+---------------+

I know windowing functions are very powerful and I've used them in rudimentary ways in the past to get overall sums and averages while avoiding needing to group my resultset rows. I have an intuition that I will need to employ the 'preceding' keyword to get the running total for the current fiscal year each row falls into, but I can't quite grasp how to express the fiscal year as a concept to use in the 'preceding' clause (or if indeed it's possible to use a date range in this way).

Any assistance on the way of "phrasing" the fiscal year for the "preceding" clause will be of enormous help to me, please.

Frank Bailey
  • 185
  • 2
  • 17
  • 1
    If you have a calendar table you can do this quite easily by simply joining to that table and then partitioning by FiscalYear – Sean Lange Dec 05 '16 at 15:17
  • 1
    Am I missing something? Am I mistaken or did you forget to put a yearbrake into your example? Because as far as I can see, either the running total of ID 3 and 4 is faulty or they belong to another year... – Tyron78 Dec 05 '16 at 15:55
  • Jan and Feb of 2012 Belong to the 2012 fiscal year, and March and April belong to the 2013 fiscal year (year ending Feb 2013). So because I only have Jan and Feb of 2012 in my data, the running total is the nett total of Jan for the Jan row, and the sum of the nett totals of Jan and Feb for the Feb row. For March (now 2013 fiscal year) the running total resets to zero so for March it counts only the March nett total and April counts both March and April's nett totals. I hope I'm making sense and not doing something horribly idiotic. – Frank Bailey Dec 05 '16 at 16:16

1 Answers1

2

i think you should try this:

/* Create Table*/
CREATE TABLE dbo.Claims (
 Claim_ID int
,Claim_Date datetime
,Nett_Total decimal(10,2)
);

/* Insert Testrows*/
INSERT INTO dbo.Claims VALUES 
 (1, '20120101', 10000)
,(2, '20120202', 10000)
,(3, '20120303', 10000)
,(4, '20120404', 10000)
,(5, '20120505', 10000)
,(6, '20120606', 10000)
,(7, '20120707', 10000)
,(8, '20120808', 10000)

Query the Data:

SELECT  Claim_ID, Claim_Date, Nett_Total, SUM(Nett_Total) OVER
(PARTITION BY YEAR(DATEADD(month,-2,Claim_Date)) ORDER BY Claim_ID) AS
[Running Total] FROM dbo.Claims

The Trick: PARTITION BY YEAR(DATEADD(month,-2,Claim_Date))

New Partition by year, but i change the date so it fits your fiscal year.

Output:

Claim_ID |Claim_Date                 |Nett_Total  |Running Total
---------+---------------------------+------------+-------------
1        |2012-01-01 00:00:00.000    |10000.00    |10000.00
2        |2012-02-02 00:00:00.000    |10000.00    |20000.00
3        |2012-03-03 00:00:00.000    |10000.00    |10000.00   <- New partition
4        |2012-04-04 00:00:00.000    |10000.00    |20000.00
5        |2012-05-05 00:00:00.000    |10000.00    |30000.00
6        |2012-06-06 00:00:00.000    |10000.00    |40000.00
7        |2012-07-07 00:00:00.000    |10000.00    |50000.00
8        |2012-08-08 00:00:00.000    |10000.00    |60000.00
tgr
  • 244
  • 1
  • 9
  • 2
    Just a short annotation: I just tried your example, but with modified Nett Totals. As far as I could see, the year brake is at 2012-04-04 and not at 2012-03-03... might be worth modifying. ;-) – Tyron78 Dec 05 '16 at 16:03