DECLARE @TodayLastYear AS DateSET @TodayLastYear = (SELECT dateadd(yy,-1,getdate())
Date TotalThisYr TotalLastYr RunningTotalThisYr RunningTotalLastYr1-3-2013 1 1 1 11-6-2013 0 1 1 21-7-2013 1 0 2 21-13-2013 2 0 4 21-15-2013 0 1 4 3
CREATE TABLE t( ID VARCHAR(25) NOT NULL, Stat VARCHAR(25) NOT NULL, StatDate DATE NOT NULL);INSERT INTO t( ID, Stat, StatDate)Values ('1', 'Int', '2012-01-03'), ('2', 'Int', '2012-01-06'), ('3', 'Int', '2012-01-15'), ('4', 'Int', '2012-02-07'), ('5', 'Int', '2013-01-03'), ('6', 'Int', '2013-01-07'), ('7', 'Int', '2013-01-13'), ('8', 'Int', '2013-01-13'), ('9', 'Sec', '2012-01-06'), ('10', 'Sec', '2013-02-01')
CREATE TABLE #t2 (StatDate DATE PRIMARY KEY CLUSTERED ,TotalThisYr INT ,TotalLastYr INT ,RunningTotalThisYr INT ,RunningTotalLastYr INT)INSERT INTO #t2SELECT StatDate, TotalThisYr=SUM(TotalThisYr) ,TotalLastYr=SUM(TotalLastYr) ,RunningTotalThisYr=SUM(RunningTotalThisYr) ,RunningTotalLastYr=SUM(RunningTotalLastYr) FROM ( SELECT StatDate, TotalThisYr=1, TotalLastYr=0 ,RunningTotalThisYr=0, RunningTotalLastYr=0 FROM t a WHERE YEAR(StatDate) = 2013 AND Stat = 'Int' UNION ALL SELECT DATEADD(year, 1, a.StatDate), 0, 1, 0, 0 FROM t a WHERE YEAR(StatDate) = 2012 AND Stat = 'Int') aGROUP BY StatDateORDER BY a.StatDateDECLARE @ThisYr INT = 0, @LastYr INT = 0UPDATE #t2SET @ThisYr = @ThisYr + TotalThisYr ,@LastYr = @LastYr + TotalLastYr ,RunningTotalThisYr = @ThisYr ,RunningTotalLastYr = @LastYrOPTION (MAXDOP 1);SELECT *FROM #t2