1

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);
3
  • 1
    how to calculate YTD? – Ganesh_Devlekar Dec 30 '14 at 19:19
  • What version of SQL Server please? – Aaron Bertrand Dec 30 '14 at 20:45
  • I'm using Sql server 2012. – Taher Dec 30 '14 at 23:26
2

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:

1
  • 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. – Taher Dec 30 '14 at 23:33
0

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.

1
  • Thank you very much, I'll try the code when I am in the office again. – Taher Dec 30 '14 at 23:34
-2
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.

1
  • 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. – Aaron Bertrand Dec 30 '14 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.