Recursive query - 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 2008 Forums

Transact-SQL (2008)

Recursive query

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2014-01-01 : 13:58:22

I need help please.

I have a table with payments. I need to check for each idno if he has payments for a certain year that exceeds the total amount allowed. In my example the total amount allowed for each month of year 2006 is 2000,00.
I need to go each month and check the total.
For month 1 payments<2000 so I continue to month 2 and in month 3 I see that payments>2000.
I have to show in my output month 3,the maximum amount of payments allowed till that month so it's 2000*3, sum (payments) where month in (1,2,3) and also show the result for payments for month 3 less 2000.
I continue to check the next month. Month 4 has payments over 2000 so I show this month also.
However, since I already checked the previous months I don't need to calculate values from previous months. Only show the payments for that month.
Month 5 is fine but month 6 again has payments>2000.
So I show month 6, max amount of payments allowed is now 2000*2, sum (payments) where month in (5,6)

create table #Installments (idno int, payYear int, payMonth int,Payments decimal (10,2))
insert into #Installments
select 34576,2006, 1, 1704.53 union
select 34576,2006,2,1683.18 union
select 34576,2006,3, 3000.00 union
select 34576,2006,4,5000.00 union
select 34576,2006,5,1174.24 union
select 34576,2006,6,2540.62

select *,case when payments>2000.00 then 1 else 0 end from #Installments

idno payYear payMonth Payments Exceeds 2000.00
34576 2006 1 1704.53 N
34576 2006 2 1683.18 N
34576 2006 3 3000.00 Y
34576 2006 4 5000.00 Y
34576 2006 5 1174.24 N
34576 2006 6 2540.62 Y

OUTPUT

select 3 as payMonth, 2000.00*3 MaxPaymentsAllowedTillMonth,sum (Payments) TotalPaymentsTillMonth ,3000.00 TotalforMonth,3000.00-2000.00 DeviationAmount
from #Installments
where payMonth <4
union
select 4 as payMonth, 2000.00*1 MaxPaymentsAllowedTillMonth,sum (Payments) TotalPaymentsTillMonth,5000.00 TotalforMonth,5000.00-2000.00 DeviationAmount
from #Installments
where payMonth in (4)
union
select 6 as payMonth, 2000.00*2 MaxPaymentsAllowedTillMonth,sum (Payments) TotalPaymentsTillMonth,2540.62 TotalforMonth,4000.00-2000.00 DeviationAmount
from #Installments
where payMonth in (5,6)

payMonth MaxPaymentsAllowedTillMonth TotalPaymentsTillMonth TotalforMonth OverAmount
3 6000.00 6387.71 3000.00 1000.00
4 2000.00 5000.00 5000.00 3000.00
6 4000.00 3714.86 2540.62 2000.00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-02 : 13:22:48

[code]
;with cte
as
(
select *,case when payments>2000.00 then 1 else 0 end AS Exceeds
from #Installments
)
select c1.payMonth,
2000 * coalesce(c3.cnt+1,1) as MaxPaymentsAllowedPerMonth,
coalesce(totalprev,0) + c1.Payments AS TotalPaymentsTillMonth,
c1.Payments as TotalforMonth,
coalesce(totalprev,0) + c1.Payments - 2000 AS OverAmount
from cte c1
outer apply (select max(paymonth) as prev
from cte
where paymonth < c1.paymonth
and payyear = c1.payyear
and idno = c1.idno
and exceeds = 1
)c2
outer apply (select sum(Payments) as totalprev,count(1) as cnt
from cte
where idno = c1.idno
and paymonth > c2.prev and paymonth < c1.paymonth
)c3
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2014-01-15 : 04:06:34

Thanks for the reply. I was working on other urgent queries so didn't have time to check this. The requirement has changed slightly.
I will appreciate the help. I tried with a loop but it is taking ages to execute.

create table #Installments (idno int, payYear int, payMonth int,Payments decimal (10,2))
insert into #Installments 
select 34576,2006, 1, 1704.53 union
select 34576,2006,2,1683.18 union
select 34576,2006,3, 3000.00 union
select 34576,2006,4,5000.00 union
select 34576,2006,5,1174.24 union
select 34576,2006,6,2540.62

I need to find the months that exceed 2000. In the example month 3 exceeds 2000. So I have to go back to months 1-3 and sum
the total payments. The total =6387.71. I then have to see if 2000*3 (the months up to and including the month that exceeds 2000)
is smaller than 6387.71. It is so i need to display the sum of the payments from months 1-3 =6387.71. I also need to show 2000*3
and the total for the month that exceeds less the maxAmount allowed (3000-2000).
I then continue on to month 4 as the payments exceeds 2000. Since I already took care of months 1-3 I don't need to sum them.
I need to only work on month 4. I show that the total payments until month 4 is 5000, the payments for month 4 is also obviously 5000 and it exceeds 2000 by 3000.
On to month 5-doesn't exceed 2000. On to month 6 which exceeds.
I then go back to month 5 and 6 (since the last time the payment exceeds 2000).
The total for months 5-6 =3714.86 . I check the maxAmount *2 and i get 4000. Total for months 5+6 doesn't exceed 4000 so i don't show month 6.
If i had months after month 6 I would continue checking the months until the last month for that idno and year.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-15 : 06:09:55

[code]

;with cte
as
(
select *,case when payments>2000.00 then 1 else 0 end AS Exceeds
from #Installments
)
select c1.payMonth,c1.Payments,
coalesce(totalprev,0) + c1.Payments AS TotalPaymentsTillMonth,
(COALESCE(cnt,1) +1) * 2000 AS AllowedPaymentsTillMonth,
c1.Payments - 2000 AS ExceedAmount
from cte c1
outer apply (select max(paymonth) as prev
from cte
where paymonth < c1.paymonth
and payyear = c1.payyear
and idno = c1.idno
and exceeds = 1
)c2
outer apply (select sum(Payments) as totalprev,count(1) as cnt
from cte
where idno = c1.idno
and (paymonth > c2.prev or c2.prev is null) and paymonth < c1.paymonth
)c3
where exceeds = 1
and coalesce(totalprev,0) + c1.Payments >= (COALESCE(cnt,1) +1) * 2000

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2014-01-19 : 03:15:16

Thanks for your help and time

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2014-01-19 : 04:52:19

Hi,

In the example below i don't get why month 11 is returned when the maxPayment is 3027.03.If I sum months 9-11 the total for the months is less than 3027.03*3. I tried playing around with it and it still gets returned.


create table #Installments (idno int, payYear int, payMonth int,Payments decimal (10,2))
insert into #Installments 
select 566232,	2006,	1	,3568.67 union
select 566232	,2006,	2,	3542.76 union
select 566232,	2006,	3,	3517.03 union
select 566232,	2006,	4,	3499.69 union
select 566232,	2006,	5,	3499.69 union
select 566232,	2006,	6,	3499.69 union
select 566232,	2006,	7,	3499.69 union
select 566232,	2006,	8,	3499.69 union
select 566232,	2006,	9,	1197.69 union
select 566232,	2006,	10,	3499.69 union
select  566232,	2006,	11,	3499.69

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-19 : 05:34:51

do you mean this?



;with cte
as
(
select *,
case when payments> 3027.03
then 1 else 0 end AS Exceeds 
from #Installments
)
select c1.payMonth,c1.Payments,
coalesce(totalprev,0) + c1.Payments AS TotalPaymentsTillMonth,
(COALESCE(cnt,1) +1) * 3027.03 AS AllowedPaymentsTillMonth,
c1.Payments - 3027.03 AS ExceedAmount
from cte c1
outer apply (select max(paymonth) as prev
             from cte
             where paymonth < c1.paymonth
             and payyear = c1.payyear
             and idno = c1.idno
             and exceeds = 1
             )c2
outer apply (select sum(Payments) as totalprev,count(1) as cnt
             from cte
             where idno = c1.idno
             and (paymonth > c2.prev or c2.prev is null) and paymonth < c1.paymonth
            )c3
			where exceeds = 1
			and coalesce(totalprev,0) + c1.Payments >= (COALESCE(cnt,1) +1) * 3027.03

If yes its because payment for 11 th month is 3499.69 which is > 3027.03
both 9 and 10 gets excluded as in both cases its less (1197.69< 3027.03 in case of 9 and 1197.69 + 3499.69 < 3027.03 * 2 in case of 10)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2014-01-20 : 02:32:39

Thanks,

Also month 11 should be excluded as i don't show months 9 and 10 so i have to calculate from month 9.
1197.69+3499.69+3499.69=8197.07
3027.03*3=9081.09

So since 8197.07<9081.09 its fine and i don't have to show month 11 either.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2014-01-21 : 07:07:50

Do i need a loop to achieve this or can I do it your way? Can't get it to work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources