Query without WHILE loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query without WHILE loop

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-06-11 : 02:35:05

Hi Team,

I have a payment table as follows. I need to find out the sum of last 12 payments by the customer. The critical point here is that the customer may not pay at some months. Hence we cannot take the sum of last 12 months. Last 12 payments may be over a span of 2 years. So we need to take the sum of last 12 payments.

DECLARE @PaymentTable TABLE (payID INT, CustomerID INT, MonthOfPay INT, DayOfPay INT, YearOfPay INT, Amount INT)
INSERT INTO @PaymentTable (payID,CustomerID,MonthOfPay,DayOfPay,YearOfPay,Amount) VALUES (1,1,1,13,2010,500)
INSERT INTO @PaymentTable (payID,CustomerID,MonthOfPay,DayOfPay,YearOfPay,Amount) VALUES (2,1,1,13,2008,467)
INSERT INTO @PaymentTable (payID,CustomerID,MonthOfPay,DayOfPay,YearOfPay,Amount) VALUES (3,1,1,14,2010,300)

If there is no 12 payments available, list the sum of what he has paid already. The expected result in the above example is 1267.

The following query is not the required one.
DECLARE @CurrentDate DATETIME
SET @CurrentDate = '6/11/2010'

--Following is NOT the required query.
SELECT
CustomerID AS [Customer],
SUM(Amount)
FROM @PaymentTable
WHERE (YearOfPay*100+MonthOfPay) BETWEEN ( ( (YEAR(@CurrentDate)-1) *100) + (MONTH(@CurrentDate)) ) AND ( ( YEAR(@CurrentDate) *100) + (MONTH(@CurrentDate)-1) )
GROUP BY CustomerID

I can achieve it if I am allowed to use WHILE loop. How can we do it without using WHILE?

Please help..

Thanks
Lijo Cheeran Joseph

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 02:47:59

[code]
DECLARE @PaymentTable TABLE (payID INT, CustomerID INT, MonthOfPay INT, DayOfPay INT, YearOfPay INT, Amount INT)
INSERT INTO @PaymentTable (payID,CustomerID,MonthOfPay,DayOfPay,YearOfPay,Amount) VALUES (1,1,1,13,2010,500)
INSERT INTO @PaymentTable (payID,CustomerID,MonthOfPay,DayOfPay,YearOfPay,Amount) VALUES (2,1,1,13,2008,467)
INSERT INTO @PaymentTable (payID,CustomerID,MonthOfPay,DayOfPay,YearOfPay,Amount) VALUES (3,1,1,14,2010,300)
INSERT INTO @PaymentTable (payID,CustomerID,MonthOfPay,DayOfPay,YearOfPay,Amount) VALUES (4,2,1,13,2010,300)
INSERT INTO @PaymentTable (payID,CustomerID,MonthOfPay,DayOfPay,YearOfPay,Amount) VALUES (5,2,1,14,2010,300)

select * from @PaymentTable

select
CustomerID AS [Customer],
SUM(Amount)
FROM
(
select
row_number() over (partition by CustomerID order by YearOfPay,MonthOfPay,DayOfPay) as rnum,
*
from @PaymentTable
)dt
where rnum <= 12
GROUP BY CustomerID[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources