SQL Server 2012 – Building Year to Date Aggregations

running-totalssql serversql-server-2012

I need some help with building a monthly aggregation using only SQL.

Imagine the following table:

TranID  DateCode    Account Value
   1    20140101        1   5
   2    20140106        1   -3
   3    20140207        1   6
   4    20140409        1   3
   5    20140103        2   3
   6    20140215        2   7
   7    20140519        2   6

There are two accounts that have transactions on various dates. I would like to write code that gives me this result assuming we are in July:

MonthID     Account YTD
20140101        1   2
20140201        1   8
20140301        1   8
20140401        1   11
20140501        1   11
20140601        1   11
20140701        1   11
20140101        2   3
20140201        2   10
20140301        2   10
20140401        2   10
20140501        2   16
20140601        2   16
20140701        2   16

I'm thinking I should be able convert the dates to MonthCode using

DATEADD(day, -DAY(DateCode) + 1, DateCode) AS MonthCode

I'm thinking I should be able to solve this somehow by joining the table to itself but I don't really get the numbers right. Also I need to somehow get the months without transactions in as well..

Any and all help will be greatly appreciated!

Code to generate the mock data:

Create table #Tran(
TranID int identity(1,1),
DateCode Date,
AccountCode varchar(50),
Value int
);

insert into #Tran (DateCode, AccountCode, Value)
values ('20140101', '1', 5),
('20140106', '1', -3),
('20140201', '1', 6),
('20140401', '1', 3),
('20140101', '2', 3),
('20140201', '2', 7),
('20140501', '2', 6);

Best Answer

For SQL Server 2012, given this sample data:

CREATE TABLE #t(TranID INT, DateCode DATE, Account INT, Value INT);
CREATE CLUSTERED INDEX x ON #t(Account, DateCode);

INSERT #t VALUES(1,'20140101',1,5 ),(2,'20140106',1,-3),(3,'20140207',1,6 ),
(4,'20140409',1,3 ),(5,'20140103',2,3 ),(6,'20140215',2,7 ),(7,'20140519',2,6 );

I would use this query (imagine the first line as stored procedure parameters):

DECLARE @year INT = 2014, @cutoff DATE = NULL; -- just state the year

-- and optionally an explicit cutoff date (leave NULL for today)
SET @cutoff = '20140731';

DECLARE @startdate DATE = DATEADD(YEAR, @year-1900, 0);

;WITH accounts(a) AS
(
  SELECT DISTINCT Account FROM #t 
    WHERE DateCode >= @startdate
    AND DateCode < DATEADD(YEAR, 1, @startdate)
),
months(m) AS 
(
  SELECT TOP (12) DATEADD(MONTH, ROW_NUMBER() OVER 
      (ORDER BY s.[object_id])-1, @startdate)
    FROM sys.all_objects AS s
),
s AS
(
  SELECT a.a, m.m, v = COALESCE(SUM(t.Value),0)
  FROM accounts AS a 
  CROSS APPLY months AS m
  LEFT OUTER JOIN #t AS t
    ON t.DateCode >= m.m
    AND t.DateCode < DATEADD(MONTH, 1, m.m)
    AND t.Account = a.a
  WHERE m.m < COALESCE(@cutoff, SYSDATETIME())
  GROUP BY a.a, m.m
)
SELECT 
  MonthID = m, 
  Account = a, 
  YTD = SUM(v) OVER(PARTITION BY a ORDER BY m ROWS UNBOUNDED PRECEDING) 
FROM s
ORDER BY a,m;

If you are using a version older than SQL Server 2012, then you may want to try some of the alternative approaches here:

Related Question