sum next n values

[CdnRissa]

I need to get the sum of the next 12 months worth of revenue from an amortization table and instead of getting one value per grouped row, I'm getting one row for each row in the table.

The actual table is more complicated than this example and is pushing 23 million rows, but this is representative of the issue.

-- If the test table already exists, drop it
IF OBJECT_ID('TempDB..#test','U') IS NOT NULL
DROP TABLE #test

--Create the test table
CREATE TABLE #test
(DRAId int,
TxnId int,
Entity char(3),
GLAcct char(4),
RecogMth DATE,
Amount money
)

--POPULATE WITH TEST DATA
insert into #test
SELECT 1,1, 'ABC', '2600','2015-01-01',25	 UNION ALL
SELECT 2,1, 'ABC', '2600','2015-02-01',50	 UNION ALL
SELECT 3,1, 'ABC', '2600','2015-03-01',50	 UNION ALL
SELECT 4,1, 'ABC', '2600','2015-04-01',50	 UNION ALL
SELECT 5,1, 'ABC', '2600','2015-05-01',50	 UNION ALL
SELECT 6,1, 'ABC', '2600','2015-06-01',50	 UNION ALL
SELECT 7,1, 'ABC', '2600','2015-07-01',25	 UNION ALL
SELECT 8,2, 'ABC', '2600','2015-01-01',25	 UNION ALL
SELECT 9,2, 'ABC', '2600','2015-02-01',50	 UNION ALL
SELECT 10,2, 'ABC', '2600','2015-03-01',50	 UNION ALL
SELECT 11,2, 'ABC', '2600','2015-04-01',50	 UNION ALL
SELECT 12,2, 'ABC', '2600','2015-05-01',50	 UNION ALL
SELECT 13,2, 'ABC', '2600','2015-06-01',50	 UNION ALL
SELECT 14,2, 'ABC', '2600','2015-07-01',50	 UNION ALL
SELECT 15,2, 'ABC', '2600','2015-08-01',25	 UNION ALL
SELECT 16,3, 'ABC', '2600','2015-02-01',37	 UNION ALL
SELECT 17,3, 'ABC', '2600','2015-03-01',42	 UNION ALL
SELECT 18,3, 'ABC', '2600','2015-04-01',42	 UNION ALL
SELECT 19,3, 'ABC', '2600','2015-05-01',42	 UNION ALL
SELECT 20,3, 'ABC', '2600','2015-06-01',42	 UNION ALL
SELECT 21,3, 'ABC', '2600','2015-07-01',15	 UNION ALL
SELECT 22,4, 'XYZ', '2601','2015-08-01',12	 UNION ALL
SELECT 23,4, 'XYZ', '2601','2015-09-01',42	 UNION ALL
SELECT 24,4, 'XYZ', '2601','2015-01-01',42	 UNION ALL
SELECT 25,4, 'XYZ', '2601','2015-02-01',42	 UNION ALL
SELECT 26,4, 'XYZ', '2601','2015-03-01',42	 UNION ALL
SELECT 27,4, 'XYZ', '2601','2015-04-01',42	 UNION ALL
SELECT 28,4, 'XYZ', '2601','2015-05-01',42	 UNION ALL
SELECT 29,4, 'XYZ', '2601','2015-06-01',30	 UNION ALL
SELECT 30,5, 'ABC', '2600','2015-01-01',150	 UNION ALL
SELECT 31,5, 'ABC', '2600','2015-02-01',200	 UNION ALL
SELECT 32,5, 'ABC', '2600','2015-03-01',200	 UNION ALL
SELECT 33,5, 'ABC', '2600','2015-04-01',200	 UNION ALL
SELECT 34,5, 'ABC', '2600','2015-05-01',200	 UNION ALL
SELECT 35,5, 'ABC', '2600','2015-06-01',50	 UNION ALL
SELECT 36,6, 'XYZ', '2601','2015-01-01',25	 UNION ALL
SELECT 37,6, 'XYZ', '2601','2015-02-01',200	 UNION ALL
SELECT 38,6, 'XYZ', '2601','2015-03-01',200	 UNION ALL
SELECT 39,6, 'XYZ', '2601','2015-04-01',200	 UNION ALL
SELECT 40,6, 'XYZ', '2601','2015-05-01',200	 UNION ALL
SELECT 41,6, 'XYZ', '2601','2015-06-01',200	 UNION ALL
SELECT 42,6, 'XYZ', '2601','2015-07-01',200	 UNION ALL
SELECT 43,6, 'XYZ', '2601','2015-08-01',175	 UNION ALL
SELECT 44,7, 'ABC', '2600','2015-01-01',150	 UNION ALL
SELECT 45,7, 'ABC', '2600','2015-02-01',200	 UNION ALL
SELECT 46,7, 'ABC', '2600','2015-03-01',200	 UNION ALL
SELECT 47,7, 'ABC', '2600','2015-04-01',200	 UNION ALL
SELECT 48,7, 'ABC', '2600','2015-05-01',200	 UNION ALL
SELECT 49,7, 'ABC', '2600','2015-06-01',200	 UNION ALL
SELECT 50,7, 'ABC', '2600','2015-07-01',50

This query

Select Entity, GLAcct, RecogMth  --, SUM(amount) as Amt
, sum(amount) over (partition by entity, GLAcct
			Order by RecogMth
			rows between 1 following and 3 following
			) as Next3Mths
from #test
where RecogMth = '2015-02-01'

gives me this result:

Entity	GLAcct	RecogMth	Next3Mths
ABC	2600	2015-02-01	287.00
ABC	2600	2015-02-01	437.00
ABC	2600	2015-02-01	400.00
ABC	2600	2015-02-01	200.00
ABC	2600	2015-02-01	NULL
XYZ	2601	2015-02-01	42.00
XYZ	2601	2015-02-01	NULL

It should give me this:

Entity	GLAcct	RecogMth	Next3Mths
ABC	2600	2015-02-01	1626.00
XYZ	2601	2015-02-01	726.00

When I run the same query without the where clause, it gives me 50 rows (there are only 50 rows in the table).

In other words, for any given month, I want the sum for the next n months (3 in this example). If the current month is Feb 2015, I want the sum of Mar, Apr & May 2015. It would also be nice (though not critical) if I could get the sum of the current month in the same resultset like this:

Entity	GLAcct	RecogMth	Recog(Feb)	Next3Mths(Mar,Apr,May)
ABC	2600	2015-02-01	537.00		1626.00
XYZ	2601	2015-02-01	242.00		726.00

Given that I am dealing with millions of records in the base table, efficiency is a bit of an issue so I would prefer to avoid a correlated subquery. I have been wracking my brain over this one and I do not understand what I'm doing wrong, but it may be that I can't see the forest for the trees anymore. Any help would be much appreciated!

I am using SQL Server 2012.

 

[fredericofonseca]

Based on your sampled data you can not use the windowing function alone to achieve this as window function processes rows of the input criteria, not based on the group by implicit by the partition clause
trick here is to group by month before applying the windowing and if this is only done for current month + 12 month then look at the commented code below for pre filtering


following works

select *
from (Select Entity
           , GLAcct
           , RecogMth
           , amount as amount2
           , sum(amount) over (partition by entity
                                          , GLAcct
                                   Order by RecogMth
		                       rows between 1 following and 3 following
			) as Next3Mths
           , sum(amount) over (partition by entity
                                          , GLAcct
                                   Order by RecogMth
		                       rows between 1 following and 6 following
			) as Next6Mths
           , sum(amount) over (partition by entity
                                          , GLAcct
                                   Order by RecogMth
		                       rows between 1 following and 12 following
			) as Next12Mths
      from (Select Entity
                 , GLAcct
                 , RecogMth
                 , sum(amount) as amount
            from #test
            -- filter just current plus 12 month -- define and populate variables before query
            -- where RecogMth between @reportdate and dateadd(m, 12, @reportdate)
            group by Entity, GLAcct, RecogMth
           ) t
    ) t1
where RecogMth = '2015-02-01';

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[CdnRissa]

Thanks, Frederico!

So given that I have to limit the data before the windowing, I can't put this into a view. I will try your code when I'm back in the office next week.

Cheers, Rissa