Building year to date aggregations using SQL

Question

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);

• Dgan: how to calculate YTD?
• Aaron Bertrand: What version of SQL Server please?
• Taher: I'm using Sql server 2012.

Answer 1

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:

• Best approaches for grouped running totals

• Taher: Thank you very much for taking the time to write such an answer! I'll try the code out as soon as I am in the office again.

Answer 2

First you will need a table that has all of the months you may need in it which I'll call MonthValue (20140101, 20140201, etc..)

Your query will look like this:

SELECT MonthValue.DateCode, #Tran.AccountID,
 (SELECT Sum(Value) FROM #Tran as sub
 WHERE sub.AccountID = #Tran.AccountID 
 AND sub.DateCode BETWEEN DATEADD(day, -DAY(MonthValue.DateCode) + 1, DateCode) AND MonthValue.DateCode) AS YTD
FROM MonthValue LEFT OUTER Join #Tran ON MonthValue.DateCode = #Tran.DateCode
GROUP BY MonthValue.DateCode, #Tran.AccountID

I didn't test it, but it should at least get you close to what you need.

• Taher: Thank you very much, I'll try the code when I am in the office again.

Answer 3

WITH TestData AS (
SELECT 
 DATEADD(DAY,(-1*DATEPART(DAY,CONVERT(DATETIME,DateCode)))+1,CONVERT(DATETIME,DateCode)) DateCode,
 AccountCode,
 Value
FROM (
 VALUES ('20140101', '1', 5),
 ('20140106', '1', -3),
 ('20140201', '1', 6),
 ('20140401', '1', 3),
 ('20140101', '2', 3),
 ('20140201', '2', 7),
 ('20140501', '2', 6)
) AS T (DateCode, AccountCode, Value)
)
SELECT DISTINCT 
 TD1.DateCode, TD1.AccountCode,
 (SELECT SUM(TD2.Value) Total
 FROM TestData TD2
 WHERE TD2.AccountCode = TD1.AccountCode AND TD2.DateCode <= TD1.DateCode) Total
FROM TestData TD1
ORDER BY TD1.AccountCode, TD1.DateCode;

You have to find a way to group the dates into the month of the year, and then perform the calculation such as the above query.

• Aaron Bertrand: This doesn't match the OP's desired result, for example it's missing the row 20140301 1 8. It will also include data from prior years, where the OP just wants current YTD.