July 5, 2016 at 8:53 am
daniel.shepherd (7/5/2016)
OP is my supervisor and posted this question on my behalf.Transaction table - MerchantID, TransAmount
20296, 200
20331, 90.58
Corrections table - MerchantID, CorrectionAmount, CreatedOn
20296, 100.00, 6/1/16
20331, 50.00, 6/1/16
20331, 50.00, 6/5/16
We already have the statements to group the total corrections together and calculate CorrectionAmount per merchant. What we need is a way to subtract from the TransAmount the CorrectionAmount, in order from oldest to most recent, until either the TransAmount or all the CorrectionAmount (per merchantID) is 0, and update the CorrectionAmount with how much was able to be subtracted (if the whole amount wasnt able to be).
For example, using the data listed, we should end up with the following data:
Transaction table - MerchantID, TransAmount
20296, 100
20331, 0
Corrections table - MerchantID, CorrectionAmount, CreatedOn
20296, 0, 6/1/16
20331, 0, 6/1/16
20331, 9.42, 6/5/16
maybe I am misunderstanding your logic.....but why are you updating the tables with these results.
where is your audit of changes to the "Transaction table"..?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 5, 2016 at 8:53 am
You could use a recusive cte,
Changed the Id in your sample to Group_Id and added an Identity_Column to enable filtering and sorting.
The first cte filters and sorts the data from #sometable which is used in the recursive cte cte_Rcte.
The result is subtracted from the #sometable.
CREATE TABLE #sometable(
Id int IDENTITY(1,1),
Group_Id INTEGER NOT NULL,
somevalue INTEGER NOT NULL
);
INSERT INTO #sometable(Group_Id,somevalue) VALUES (1,100);
INSERT INTO #sometable(Group_Id,somevalue) VALUES (1,50);
INSERT INTO #sometable(Group_Id,somevalue) VALUES (1,50);
INSERT INTO #sometable(Group_Id,somevalue) VALUES (1,20);
INSERT INTO #sometable(Group_Id,somevalue) VALUES (2,100);
INSERT INTO #sometable(Group_Id,somevalue) VALUES (2,300);
INSERT INTO #sometable(Group_Id,somevalue) VALUES (2,100);
SELECT * FROM #sometable;
DECLARE @Group_Id int = 1,
@Remove int = 175;
WITH cte_SV AS (
SELECT ROW_NUMBER() OVER (ORDER by Id) R,
Id,
somevalue
FROM #sometable
WHERE Group_Id = @Group_Id
),
cte_Rcte (R, Id, somevalue, Q) AS (
SELECT S.R,
S.Id,
CASE WHEN S.Somevalue >= @Remove THEN @Remove ELSE S.SomeValue END,
CASE WHEN S.somevalue >= @Remove THEN 0 ELSE @Remove - S.somevalue END
FROM cte_SV S
WHERE S.R = 1
UNION ALL
SELECT S.R,
S.Id,
CASE WHEN S.somevalue >= cte_Rcte.Q THEN cte_Rcte.Q ELSE S.somevalue END,
CASE WHEN S.somevalue >= cte_Rcte.Q THEN 0 ELSE cte_Rcte.Q - S.somevalue END
FROM cte_SV S
INNER JOIN cte_Rcte ON S.R = cte_Rcte.R + 1
WHERE cte_Rcte.Q > 0
)
UPDATE #sometable
SET somevalue = X.somevalue - cte_Rcte.somevalue
FROM #sometable X INNER JOIN
cte_Rcte ON cte_Rcte.Id = X.Id;
SELECT * FROM #sometable
Louis.
July 5, 2016 at 9:14 am
daniel.shepherd (7/5/2016)
OP is my supervisor and posted this question on my behalf.Transaction table - MerchantID, TransAmount
20296, 200
20331, 90.58
Corrections table - MerchantID, CorrectionAmount, CreatedOn
20296, 100.00, 6/1/16
20331, 50.00, 6/1/16
20331, 50.00, 6/5/16
as you are new, and on your behalf, here is readily consumable code for your tables, so that we can all use.
for future reference please see here https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/%5B/url%5D%5B/b%5D
please confirm that you are using SQL 2016 as this is the forum you are posting in...thanks
CREATE TABLE #Transactions(
MerchantID INT NOT NULL
,TransAmount NUMERIC(6,2) NOT NULL
);
INSERT INTO #Transactions(MerchantId,TransAmount)
VALUES (20296,200.00),(20331,90.58);
CREATE TABLE #Corrections(
MerchantID INTEGER NOT NULL
,CorrectionAmount NUMERIC(6,2) NOT NULL
,CreatedOn DATE NOT NULL
);
INSERT INTO #Corrections(MerchantID,CorrectionAmount,CreatedOn)
VALUES (20296,100.00,'2016-06-01'),(20331,50.00,'2016-06-01'),(20331,50.00,'2016-06-05');
SELECT * FROM #Transactions;
SELECT * FROM #Corrections;
--DROP TABLE #Transactions;
--DROP TABLE #Corrections;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 5, 2016 at 10:43 am
Louis - Thank you, I am still converting your code into something that works for my situation, but I believe its exactly what I am looking for. I will let you know after I am finished testing it.
J Livingston - Thank you for being patient and understanding, and also for creating my sample data for me. After reading the link you posted I see that I have broken quite a few rules of etiquette for this forum, and for that I am sorry. I will follow the guidelines listed in the link for all future posts or questions I have. I am currently using SQL 2016.
July 5, 2016 at 10:53 am
daniel.shepherd (7/5/2016)
J Livingston - Thank you for being patient and understanding, and also for creating my sample data for me. After reading the link you posted I see that I have broken quite a few rules of etiquette for this forum, and for that I am sorry. I will follow the guidelines listed in the link for all future posts or questions I have. I am currently using SQL 2016.
no problem Daniel...no need for the thanks, but appreciate that you have taken it on board.
It will give you quicker and better response in any future posts.
Good luck
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply