January 25, 2014 at 2:32 am
Hi,
I would appreciate your help.
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.
Example: year 2005 the maxAllowed to deposit per month is 2000 and for year 2006 it's 3027.03.
For year 2005 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.
For year 2006 I need to only return the months up to and including month 8.
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 union
select 566232,2005, 1, 1704.53 union
select 566232,2005,2,1683.18 union
select 566232,2005,3, 3000.00 union
select 566232,2005,4,5000.00 union
select 566232,2005,5,1174.24 union
select 566232,2005,6,2540.62
Thanks
January 25, 2014 at 8:35 am
jadelola (1/25/2014)
Hi,I would appreciate your help.
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.
Example: year 2005 the maxAllowed to deposit per month is 2000 and for year 2006 it's 3027.03.
For year 2005 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.
For year 2006 I need to only return the months up to and including month 8.
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 union
select 566232,2005, 1, 1704.53 union
select 566232,2005,2,1683.18 union
select 566232,2005,3, 3000.00 union
select 566232,2005,4,5000.00 union
select 566232,2005,5,1174.24 union
select 566232,2005,6,2540.62
Thanks
First, thank you for the readily consumable data.
Your explanation is quite complex. Could it be summarized as follows?
Any time the running total of payments for a year exceeds the month# times the max monthly payment for the given year, display the row?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 27, 2014 at 2:53 am
Thanks for the reply.
Every time a month exceeds the max amount allowed I have to go back to the previous months and sum the months including the month that exceeds. If the total exceeds the maxamount allowed *total months i need to display that month.
If the total doesn't exceed i continue on to the next month that payments exceeds the max amount (if exists).
I then total all the previous months.
Lets say month 3 has 4000 which is bigger than the maxamount. I sum months 1-3 and check if it's bigger than maxamount*3.
If it's bigger i display that month and the total for the 3 months in one row.
I continue and see that month 4 is bigger than maxamount. Since i already displayed month 3 i only work on month 4
and don't go back.
If month 1-3 total was smaller than maxamount*3 than for month 4 i go back to months 1-4 and maxamount will be *4.
Hope it's clearer. I know it's a complex explanation.
January 27, 2014 at 3:22 am
DROP TABLE #Installments;
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 union
select 566232,2005, 1, 1704.53 union
select 566232,2005,2,1683.18 union
select 566232,2005,3, 3000.00 union
select 566232,2005,4,5000.00 union
select 566232,2005,5,1174.24 union
select 566232,2005,6,2540.62
DECLARE @YearCalc TABLE
(
Year VARCHAR(10),
[maxAllowed] decimal (10,2)
)
INSERT INTO @YearCalc
SELECT '2005',2000.00
UNION SELECT '2006','3027.03'
DECLARE @MonthINt TABLE
(
ID INT
)
INSERT INTO @MonthINt(ID)
SELECT distinct number FROM Master..spt_values where number Between 1 and 12
DECLARE @MonthINt1 TABLE
(
ID INT
)
INSERT INTO @MonthINt1(ID)
SELECT distinct number FROM Master..spt_values where number Between 1 and 12
DECLARE @MonthLoop TABLE
(
ID INT IDENTITY(1,1),
StartMonth INT,
EndMonth INT,
[Year] VARCHAR(10),
[maxAllowed] decimal (10,2)
)
INSERT INTO @MonthLoop
select M.ID,M1.ID,[Year],((M1.ID-M.ID)+1)*[maxAllowed] from
@MonthINt M INNER JOIN @MonthINt1 M1
ON M.ID <M1.ID
CROSS JOIN @YearCalc
order by 1
select maxAllowed,[Year],SUM(Payments),MIN(StartMonth),Max(EndMonth) from #Installments I
INNER JOIN
@MonthLoop M
ON I.payMonth Between M.StartMonth AND EndMonth
AND I.payYear = M.[Year]
--INNER JOIN @MonthINt1 M1 ON M.ID <M.ID
GROUP BY M.ID,maxAllowed,[Year]
HAVING SUM(Payments)>=maxAllowed
Regards,
Mitesh Oswal
+918698619998
Regards,
Mitesh OSwal
+918698619998
January 27, 2014 at 7:34 am
Thanks for the help.
In the example below it returns month 6 and it says that the startmonth=5. But in fact the starting month is 3.
maxamount for year 2012 is 3533.79
insert into #Installments
select 27086 ,2012,2,1291
union select 27086 ,2012,3,1320
union select 27086 ,2012,4,2640
union select 27086 ,2012,5,1320
union select 27086 ,2012,6,1320
union select 27086 ,2012,7,3778
union select 27086 ,2012,9,2232
union select 27086 ,2012,10,1890
union select 27086 ,2012,12,774
union select 42671 ,2012,3,3174.64
union select 42671 ,2012,4,3355.09
union select 42671 ,2012,5,3207.71
union select 42671 ,2012,6,3915.06
union select 42671 ,2012,7,3072.54
union select 42671 ,2012,8,2923.99
union select 42671 ,2012,9,2968.76
union select 42671 ,2012,10,2704.52
union select 42671 ,2012,11,2447.09
union select 42671 ,2012,12,3137.91
January 28, 2014 at 12:27 am
Do i need a loop?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply