Grouping and suming on a specific number of rows

Question

Post reply

Grouping and suming on a specific number of rows

Corobori

Ten Centuries

Points: 1316
More actions
July 9, 2005 at 4:40 pm

#165258

I need to sum amount where the number of rows (months) to consider depends on a parameter
For example if my date is <= then 3/31/2005 and the parameter is 3 (meaning 3 months) with the data shown below I am expecting:
Funds, Subscriber, Amount
1, 1, 50000
2, 2, 17000
For the same date but with a parameter of 2 I am expecting this:
1, 1, 35000
2, 2, 12000
create table #tblTempRetros(DateMonthEnd datetime,Funds int, Subscriber int, Amount int)
insert into #tblTempRetros VALUES ('1/31/2005', 1, 1, 15000)
insert into #tblTempRetros VALUES ('1/31/2005', 2, 2, 5000)
insert into #tblTempRetros VALUES ('2/28/2005', 1, 1, 20000)
insert into #tblTempRetros VALUES ('2/28/2005', 2, 2, 7000)
insert into #tblTempRetros VALUES ('12/31/2004', 1, 1, 15000)
insert into #tblTempRetros VALUES ('12/31/2004', 2, 2, 5000)
insert into #tblTempRetros VALUES ('3/31/2005', 1, 1, 15000)
insert into #tblTempRetros VALUES ('3/31/2005', 2, 2, 5000)

Jean-Luc
www.corobori.com

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

DECLARE @aa INT SET @aa = 3

SELECT Funds, Subscriber, SUM(Amount)

FROM

#tblTempRetros MyTable

JOIN

(SELECT DISTINCT DateMonthEnd FROM #tblTempRetros A

WHERE

@aa - 1 = (SELECT COUNT(DISTINCT DateMonthEnd)

FROM

#tblTempRetros B

WHERE

B.DateMonthEnd > A.DateMonthEnd)) MyDate

ON

MyTable.DateMonthEnd >= MyDate.DateMonthEnd

GROUP BY Funds, Subscriber

Regards,
gova

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 2

Little better

DECLARE @aa INT SET @aa = 2

SELECT Funds, Subscriber, SUM(Amount) FROM #tblTempRetros A

WHERE

@aa > (SELECT COUNT(DISTINCT DateMonthEnd)

FROM

#tblTempRetros B

WHERE

B.DateMonthEnd > A.DateMonthEnd)

GROUP BY Funds, Subscriber

I should have taken my time. I didn't realize Remi is not online at this time.

Regards,
gova

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 3

A version without a correlated sub-query... but only if you can guarantee that the date you use is a month end date and that all of the DateMonthEnd dates are actually month end dates... if not, you'll need to add a bit more so far as date functions go to find month-ending dates... would probably run faster if you modified @MyDate right after the declarations instead of doing the calculation in the WHERE clause...

DECLARE @MyDate DATETIME

SET @MyDate = '03/31/2005'

DECLARE @TheParm SMALLINT

SET @TheParm = 2

SELECT Funds, Subscriber, SUM(Amount) AS Amount

FROM #tblTempRetros

WHERE DateMonthEnd >= DATEADD(mm,-2,@MyDate+1)

GROUP BY Funds,Subscriber

And thank you for posting the code to build the temp table and populate it with data... made life real easy...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Corobori Ten Centuries Points: 1316 More actions · Answer 4

Thanks guys for the answer.

It seems I missed something in my specs and I need an extra thing. The parameters I was thinking to pass down to count the number of MonthEnd date to retrieve isn't the same for each Fund. In other words Funds 1 needs to retrieve 3 months but funds 2 needs to retrieve just one month.

create table #tblFunds(Funds int, FundsInterval int)

insert into #tblFunds VALUES (1, 3)

insert into #tblFunds VALUES (2, 1)

Jean-Luc
www.corobori.com

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

Like the original post can you give what is the desired output for both the parameters.

Regards,
gova