mardi 30 décembre 2014

Building year to date aggregations using SQL


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




Aucun commentaire:

Enregistrer un commentaire