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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  SQL Server 2008 Forums  Transact-SQL (2008)  Recursive query Reply to Topic  Printer Friendly
Author  Topic

collie
Constraint Violating Yak Guru

400 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 unionselect 34576,2006,2,1683.18 unionselect 34576,2006,3, 3000.00 unionselect 34576,2006,4,5000.00 unionselect 34576,2006,5,1174.24 unionselect 34576,2006,6,2540.62 select *,case when payments>2000.00 then 1 else 0 end from #Installmentsidno payYear payMonth Payments Exceeds 2000.0034576 2006 1 1704.53 N34576 2006 2 1683.18 N34576 2006 3 3000.00 Y34576 2006 4 5000.00 Y34576 2006 5 1174.24 N34576 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 OverAmount3 6000.00 6387.71 3000.00 1000.004 2000.00 5000.00 5000.00 3000.006 4000.00 3714.86 2540.62 2000.00

visakh16
Very Important crosS Applying yaK Herder

India
52326 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

collie
Constraint Violating Yak Guru

400 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 1-3 and sumthe 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

India
52326 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

collie
Constraint Violating Yak Guru

400 Posts

 Posted - 01/19/2014 :  03:15:16 Thanks for your help and time

collie
Constraint Violating Yak Guru

400 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 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

India
52326 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.03both 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs

collie
Constraint Violating Yak Guru

400 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.073027.03*3=9081.09So 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 - 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
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC