Help with CTE - 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

General SQL Server Forums

New to SQL Server Programming

Help with CTE

Author

Topic

spendyala
Starting Member

15 Posts

Posted - 2009-01-15 : 18:33:49

Hi,

I have a funding table which is as follows. I have an account no, funddate and amount columns in the table.

I have to take the amount on the minimum date for this account and calculate the rest of months amount to be paid by the student. For this i am using a CTE and the CTE is going into a recursive loop, even though i am getting the correct amount for the first fewones.

Please let me know where i am going wrong.

In the below example, i have funding amount of 2635 for the month of may, so i have to calculate the amount for the months of june - november. In november, i have to add the existing amount for the month of november in the funding table to the calculation, i am doing.

Pls let me know if you have any questions.

declare @funding table(accountno int, funddate datetime, lstartdate datetime, lenddate datetime, amount int)

insert @funding
select 1, '2008-05-17','2008-04-11','2008-11-20',2635
union all select 1, '2008-11-04','2008-04-11','2008-11-20',300
--union all select 2,'2008-03-01','2008-04-10','2008-12-21',3000

declare @minmonth int
select @minmonth = min(datepart(month,funddate)) from @funding

;WITH Yak (accountno,m, amount , discount,dated)
AS
(
select accountno,datepart(month,funddate),amount,0,0 from
@funding where datepart(month,funddate) = @minmonth
union all
select funding.accountno, m+1, yak.amount - (yak.amount * 30 /datediff(dd,lstartdate,lenddate)) ,0,datediff(dd,lstartdate,lenddate)
from @funding funding join yak on
yak.accountno = funding.accountno
where yak.amount > 1000
)

select * from yak
group by accountno,m,amount,discount,dated

thank you,

Sri.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 22:54:09

so whats should be your expected output out of above data?