SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Recursive query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

collie
Constraint Violating Yak Guru

399 Posts

Posted - 01/01/2014 :  13:58:22  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/02/2014 :  13:22:48  Show Profile  Reply with Quote

;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
Go to Top of Page

collie
Constraint Violating Yak Guru

399 Posts

Posted - 01/15/2014 :  04:06:34  Show Profile  Reply with Quote

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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/15/2014 :  06:09:55  Show Profile  Reply with Quote


;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
Go to Top of Page

collie
Constraint Violating Yak Guru

399 Posts

Posted - 01/19/2014 :  03:15:16  Show Profile  Reply with Quote
Thanks for your help and time
Go to Top of Page

collie
Constraint Violating Yak Guru

399 Posts

Posted - 01/19/2014 :  04:52:19  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/19/2014 :  05:34:51  Show Profile  Reply with Quote
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
Go to Top of Page

collie
Constraint Violating Yak Guru

399 Posts

Posted - 01/20/2014 :  02:32:39  Show Profile  Reply with Quote
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.
Go to Top of Page

collie
Constraint Violating Yak Guru

399 Posts

Posted - 01/21/2014 :  07:07:50  Show Profile  Reply with Quote
Do i need a loop to achieve this or can I do it your way? Can't get it to work.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000