Author 
Topic 

collie
Constraint Violating Yak Guru
399 Posts 
Posted  01/01/2014 : 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.002000.00 DeviationAmount from #Installments where payMonth <4 union select 4 as payMonth, 2000.00*1 MaxPaymentsAllowedTillMonth,sum (Payments) TotalPaymentsTillMonth,5000.00 TotalforMonth,5000.002000.00 DeviationAmount from #Installments where payMonth in (4) union select 6 as payMonth, 2000.00*2 MaxPaymentsAllowedTillMonth,sum (Payments) TotalPaymentsTillMonth,2540.62 TotalforMonth,4000.002000.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
India
52325 Posts 
Posted  01/02/2014 : 13:22:48

;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
 SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs



collie
Constraint Violating Yak Guru
399 Posts 
Posted  01/15/2014 : 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 13 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 13 =6387.71. I also need to show 2000*3 and the total for the month that exceeds less the maxAmount allowed (30002000). I then continue on to month 4 as the payments exceeds 2000. Since I already took care of months 13 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 5doesn'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 56 =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
India
52325 Posts 
Posted  01/15/2014 : 06:09:55

;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
 SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs



collie
Constraint Violating Yak Guru
399 Posts 
Posted  01/19/2014 : 03:15:16

Thanks for your help and time



collie
Constraint Violating Yak Guru
399 Posts 
Posted  01/19/2014 : 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 911 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
India
52325 Posts 
Posted  01/19/2014 : 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
399 Posts 
Posted  01/20/2014 : 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
399 Posts 
Posted  01/21/2014 : 07:07:50

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



Topic 


