January 7, 2019 at 2:02 am
There are two columns in two table and we need to add bothe column based on id and get the result suppose 3000.
we have other table for the same id we have different transactions like 0,800,800,800,600,236.76... like this (these are column values for that particular id).Now we need to sum the value and to compare whether it is equal to that above one or not that is 3000.
so here 800+800+800+600 here it is equal to 3000 so it will return 4(as in 4 step its equal to 3000).
Suppose the value is like 800,800,800,800,234.56..like this.In this case when we will sum 800+800+800+800 here it is 3200 and its greater then 3000 so it should return 3.75 as 3000 completed after 3.75 sum and rest .25 is extra amount.
id of all 3 tables are same.so it should be based on id.
January 7, 2019 at 2:35 am
soumyapapoon - Monday, January 7, 2019 2:02 AMThere are two columns in two table and we need to add bothe column based on id and get the result suppose 3000.
we have other table for the same id we have different transactions like 0,800,800,800,600,236.76... like this (these are column values for that particular id).Now we need to sum the value and to compare whether it is equal to that above one or not that is 3000.
so here 800+800+800+600 here it is equal to 3000 so it will return 4(as in 4 step its equal to 3000).
Suppose the value is like 800,800,800,800,234.56..like this.In this case when we will sum 800+800+800+800 here it is 3200 and its greater then 3000 so it should return 3.75 as 3000 completed after 3.75 sum and rest .25 is extra amount.id of all 3 tables are same.so it should be based on id.
If you're using SQL Server 2012 or above, you could start with the LEAD and LAG functions.
Posting some sample data will make it much easier to answer your question. If you read the link in my signature it will give you some pointers.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 7, 2019 at 2:50 am
Neil Burton - Monday, January 7, 2019 2:35 AMsoumyapapoon - Monday, January 7, 2019 2:02 AMThere are two columns in two table and we need to add bothe column based on id and get the result suppose 3000.
we have other table for the same id we have different transactions like 0,800,800,800,600,236.76... like this (these are column values for that particular id).Now we need to sum the value and to compare whether it is equal to that above one or not that is 3000.
so here 800+800+800+600 here it is equal to 3000 so it will return 4(as in 4 step its equal to 3000).
Suppose the value is like 800,800,800,800,234.56..like this.In this case when we will sum 800+800+800+800 here it is 3200 and its greater then 3000 so it should return 3.75 as 3000 completed after 3.75 sum and rest .25 is extra amount.id of all 3 tables are same.so it should be based on id.
If you're using SQL Server 2012 or above, you could start with the LEAD and LAG functions.
Posting some sample data will make it much easier to answer your question. If you read the link in my signature it will give you some pointers.
Thank you for your quick response Neil.Please find the detailed scenario.
table 1 :
InvId pmtinfo
1 1500
2 1200
3 1100
table 2:
AltId pmtInfo
1 1500
2 1000
3 800
table 3 :
id partpmnt
1 0
1 800
1 800
1 800
1 600
1 600.78
1 500.87
so first i will add the two columns of table 1 and 2 by joining both tables on id so e.g we got 3000(1500+1500) for id = 1
So now we will sum the partpmnt column of table 3 till it will become 3000.
so as you can see in 4th payment it becomes 3000(i mean sum(partpmnt) 800+800+800+600) so it should give us the number as 4 as in 4th payment full payment of 3000 completed and it will leave other things.
suppose in second scenario
table 3 :
id partpmnt
1 0
1 800
1 800
1 800
1 800
1 600.78
1 500.87
when we will sum at 4th payment its become 3200 which is greater then 3000.So at that time it should return 3.75 as in 3.75 time it has completed the transaction of 3000.and they have given an extra payment of 200 which is 0.25 time.
Hope you can help me with this.
Thanks in advance
January 7, 2019 at 2:57 am
soumyapapoon - Monday, January 7, 2019 2:50 AMNeil Burton - Monday, January 7, 2019 2:35 AMsoumyapapoon - Monday, January 7, 2019 2:02 AMThere are two columns in two table and we need to add bothe column based on id and get the result suppose 3000.
we have other table for the same id we have different transactions like 0,800,800,800,600,236.76... like this (these are column values for that particular id).Now we need to sum the value and to compare whether it is equal to that above one or not that is 3000.
so here 800+800+800+600 here it is equal to 3000 so it will return 4(as in 4 step its equal to 3000).
Suppose the value is like 800,800,800,800,234.56..like this.In this case when we will sum 800+800+800+800 here it is 3200 and its greater then 3000 so it should return 3.75 as 3000 completed after 3.75 sum and rest .25 is extra amount.id of all 3 tables are same.so it should be based on id.
If you're using SQL Server 2012 or above, you could start with the LEAD and LAG functions.
Posting some sample data will make it much easier to answer your question. If you read the link in my signature it will give you some pointers.
Thank you for your quick response Neil.Please find the detailed scenario.
table 1 :
InvId pmtinfo
1 1500
2 1200
3 1100table 2:
AltId pmtInfo
1 1500
2 1000
3 800
table 3 :
id partpmnt
1 0
1 800
1 800
1 800
1 600
1 600.78
1 500.87so first i will add the two columns of table 1 and 2 by joining both tables on id so e.g we got 3000(1500+1500) for id = 1
So now we will sum the partpmnt column of table 3 till it will become 3000.
so as you can see in 4th payment it becomes 3000(i mean sum(partpmnt) 800+800+800+600) so it should give us the number as 4 as in 4th payment full payment of 3000 completed and it will leave other things.
suppose in second scenario
table 3 :
id partpmnt
1 0
1 800
1 800
1 800
1 800
1 600.78
1 500.87
when we will sum at 4th payment its become 3200 which is greater then 3000.So at that time it should return 3.75 as in 3.75 time it has completed the transaction of 3000.and they have given an extra payment of 200 which is 0.25 time.
Hope you can help me with this.
Thanks in advance
Without using LEAD or LAG or any function because there are database which is 2008 as well, so query,sp or function will be helpful.
Thank You!!
January 7, 2019 at 3:27 am
Ok, that's better but it's still not hugely clear.
Are you adding all three tables together for all ID's? You appear to have used ID 1 for all your examples. What happens for ID 2? For them, the sum of tables 1 and 2 is only 2200. Will there be values in table 3 that can take them over 3000?
From what I understand though, you only actually need to calculate the payments from table 3. The biggest problem you'll have here is that there's no way of ordering the rows as things stand. How do you know that this 800 comes before this 800? Unless there is some way to order the rows that you've not included, this is actually impossible.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 7, 2019 at 3:38 am
Neil Burton - Monday, January 7, 2019 3:27 AMOk, that's better but it's still not hugely clear.Are you adding all three tables together for all ID's? You appear to have used ID for all your examples. What happens for ID 2? For them, the sum of tables 1 and 2 is only 2200. Will there be values in table 3 that can take them over 3000?
From what I understand though, you only actually need to calculate the payments from table 3. The biggest problem you'll have here is that there's no way of ordering the rows as things stand. How do you know that this 800 comes before this 800? Unless there is some way to order the rows that you've not included, this is actually impossible.
in table 3 i have given example for only id =1,for id = 2 also record will be there in table 3 i have skipped that.Its not like 800 will come it can be any number like 230.75,280.25,600.. and so on so we need to sum those value starting from first once we got the sum value as 3000 or above we need to stop and return the exact value(as i described above).
For id = 2 types after sum 1 and 2 its 2200 so in table 3 there will data with id = 2 and we need to sum those until 2200 and return the result.So it will be for each id.
January 7, 2019 at 4:00 am
soumyapapoon - Monday, January 7, 2019 3:38 AMNeil Burton - Monday, January 7, 2019 3:27 AMOk, that's better but it's still not hugely clear.Are you adding all three tables together for all ID's? You appear to have used ID for all your examples. What happens for ID 2? For them, the sum of tables 1 and 2 is only 2200. Will there be values in table 3 that can take them over 3000?
From what I understand though, you only actually need to calculate the payments from table 3. The biggest problem you'll have here is that there's no way of ordering the rows as things stand. How do you know that this 800 comes before this 800? Unless there is some way to order the rows that you've not included, this is actually impossible.
in table 3 i have given example for only id =1,for id = 2 also record will be there in table 3 i have skipped that.Its not like 800 will come it can be any number like 230.75,280.25,600.. and so on so we need to sum those value starting from first once we got the sum value as 3000 or above we need to stop and return the exact value(as i described above).
For id = 2 types after sum 1 and 2 its 2200 so in table 3 there will data with id = 2 and we need to sum those until 2200 and return the result.So it will be for each id.
Does that mean you will need to sum the values for all three tables? You haven't answered the question about ordering the rows either. I understand that it could be any values in the column but how do you determine in what order they need to be added? There is nothing to indicate what order the rows should be in. There is no way to guarantee the order of the rows as things stand and without that they cannot be added in any meaningful sequence.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 7, 2019 at 4:14 am
Neil Burton - Monday, January 7, 2019 4:00 AMsoumyapapoon - Monday, January 7, 2019 3:38 AMNeil Burton - Monday, January 7, 2019 3:27 AMOk, that's better but it's still not hugely clear.Are you adding all three tables together for all ID's? You appear to have used ID for all your examples. What happens for ID 2? For them, the sum of tables 1 and 2 is only 2200. Will there be values in table 3 that can take them over 3000?
From what I understand though, you only actually need to calculate the payments from table 3. The biggest problem you'll have here is that there's no way of ordering the rows as things stand. How do you know that this 800 comes before this 800? Unless there is some way to order the rows that you've not included, this is actually impossible.
in table 3 i have given example for only id =1,for id = 2 also record will be there in table 3 i have skipped that.Its not like 800 will come it can be any number like 230.75,280.25,600.. and so on so we need to sum those value starting from first once we got the sum value as 3000 or above we need to stop and return the exact value(as i described above).
For id = 2 types after sum 1 and 2 its 2200 so in table 3 there will data with id = 2 and we need to sum those until 2200 and return the result.So it will be for each id.Does that mean you will need to sum the values for all three tables? You haven't answered the question about ordering the rows either. I understand that it could be any values in the column but how do you determine in what order they need to be added? There is nothing to indicate what order the rows should be in. There is no way to guarantee the order of the rows as things stand and without that they cannot be added in any meaningful sequence.
The main sum is for 3rd table.The first two tables value we will add and we will get a value like 3000.But in 3rd table for that we will sum all the value starting from first.There is no order like which value will come first and which value will come second.we need to keep on adding the value till it is 3000.means suppose values are like 0,200,100,800,900,223.25,776.75,0,0,500 like this data is there.
So we will start adding them and will compare with 3000 whether after sum its equal to 3000 or not.
0+200+100+800+900+223.75+776.75 = 3000 we will skip other values
0 is not a payment so here result is 6.
There is no ceratin condition like after this value that value will come.Any value can come.we need to simply add those from starting untill that 3000 and give the result..
January 7, 2019 at 4:38 am
If there is no way to establish the order of the rows this can't be done properly. If you don't know what value is first then second and so on how can you meaningfully know which order in which to add them? There is no guaranteed ordering in SQL Server unless the ORDER BY clause is used and this needs something to put in to order. At the moment the rows in Table 3 are in an entirely arbitrary order. It may appear that they are always returned in the same way but this is essentially down to chance. You may query the table 1,000,000 times and get the rows in the same order every time. Query 1,000,001 may return them in a different order and this will change your results. You cannot do anything that returns a value based an order without something to ensure this order is the same every time.
I'm sorry if it sounds like I'm lecturing you but it seems to me that you can't do what you want with what you have.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 7, 2019 at 9:01 am
(sorry for interrupting)
It's possible to do a running total (if you're using 2012 or later...)
See this: https://docs.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql?view=sql-server-2017
Look for the Cumulative Total.
PARTITION OVER is analogous to GROUP BY.
So maybe the way to go about sorting this out is trying to do a regular SUM() with GROUP BY and then turn that into a running total by using a windowing function instead. But it won't work if you don't have a column to partition by.
January 8, 2019 at 1:35 am
No need to apologise Pietlinden, especially when you've just taught me something too. I didn't realise running totals had become so straightforward.
However.... The OP's after a solution that works on 2008 because they still have databases on that. There's also still the problem of ordering the rows. There's an ID for partitioning but there's apparently no way of establishing what value comes next. Without that I can't see a way to get a running total because we don't know in what direction things are supposed to run.
There's also this line:
when we will sum at 4th payment its become 3200 which is greater then 3000.So at that time it should return 3.75 as in 3.75 time it has completed the transaction of 3000.and they have given an extra payment of 200 which is 0.25 time.
I understand we're looking for values of 3000 or over but I'm not sure about the logic behind '3.75 time'. I get the thinking that 3000 is reached in less than a 'whole number of payments' but what's the logic behind 0.25 time?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 8, 2019 at 3:55 am
Neil Burton - Tuesday, January 8, 2019 1:35 AMNo need to apologise Pietlinden, especially when you've just taught me something too. I didn't realise running totals had become so straightforward.However.... The OP's after a solution that works on 2008 because they still have databases on that. There's also still the problem of ordering the rows. There's an ID for partitioning but there's apparently no way of establishing what value comes next. Without that I can't see a way to get a running total because we don't know in what direction things are supposed to run.
There's also this line:
when we will sum at 4th payment its become 3200 which is greater then 3000.So at that time it should return 3.75 as in 3.75 time it has completed the transaction of 3000.and they have given an extra payment of 200 which is 0.25 time.
I understand we're looking for values of 3000 or over but I'm not sure about the logic behind '3.75 time'. I get the thinking that 3000 is reached in less than a 'whole number of payments' but what's the logic behind 0.25 time?
If I understand correctly what the OP wants is to verify if the part payments made will suffice the expected payment. Though I'm not clear what the goal of this requirement to ignore any payments made over the expected amount. Also, 0.25 might be calculated as the remainder of the overpayment/last payment (this is all my interpretation).
Basing on what i understood the below might be a start for the OP or others to refactor. This might not be the best way to write-up but gives an idea for other experts if OP confirms this is what is intended
DECLARE @Table1 TABLE (InvID INT,PymtInfo DECIMAL(10,2));
DECLARE @Table2 TABLE (AltID INT,PymtInfo DECIMAL(10,2));
DECLARE @Table3 TABLE (Id INT,PartPymt DECIMAL(10,2));
INSERT INTO @Table1 VALUES (1,1500),(2,1200),(3,1100);
INSERT INTO @Table2 VALUES (1,1500),(2,1000),(3,800);
INSERT INTO @Table3 VALUES (1,0),(1,800),(1,800),(1,800),(1,800),(1,600.78),(1,500.87),(2,1000),(2,1000),(2,250),(2,100),(3,500),(3,123.45),(3,1500);
WITH ExpPmt AS
(
SELECT
ID = A.InvID,
ExpPymt = SUM(A.PymtInfo + B.PymtInfo)
FROM @Table1 AS A
JOIN @Table2 AS B ON A.InvID = B.AltID
GROUP BY A.InvID
),
Payments AS
(
SELECT
Rno = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY (SELECT 1)),
Id,PartPymt
FROM @Table3
WHERE PartPymt <> 0
),
FinalResult AS
(
SELECT A.Id,A.ExpPymt,B.Rno,B.PartPymt,Prcnt = CONVERT(DECIMAL(5,2),0)
FROM ExpPmt AS A
JOIN Payments AS B ON A.Id = B.Id
AND Rno = 1
UNION ALL
SELECT
A.ID,A.ExpPymt,B.Rno,
CONVERT(DECIMAL(10,2),A.PartPymt + B.PartPymt),
IIF(A.PartPymt + B.PartPymt >= A.ExpPymt,CONVERT(DECIMAL(5,2),(A.ExpPymt - A.PartPymt - B.PartPymt)/B.PartPymt),0)
FROM FinalResult AS A
JOIN Payments AS B ON A.ID = B.Id
AND A.Rno = B.Rno - 1
)
SELECT
A.ID,
ExpectedPayment = A.ExpPymt,
NumPayments = A.Rno,
OverpaymentPercent = ABS(A.Prcnt)
FROM FinalResult AS A
WHERE A.Rno = (SELECT MIN(B.Rno) FROM FinalResult AS B WHERE B.ID = A.ID AND B.PartPymt >= A.ExpPymt AND B.Prcnt <= 0)
ORDER BY A.ID;
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply