July 22, 2015 at 5:53 am
Hi All,
I have come up with an issue where I want to update data in a table using bulk/SET update to get the result shown in below code with output in column titled "Arrear Amt".
Please use this test data.
CREATE TABLE ##vOD_Calc
(
Seq_No INT ,
Contract_id INT ,
Rental_id INT ,
Actual_OD INT ,
Logic_OD INT ,
Due_dte DATETIME ,
Arrear_amt DECIMAL(18, 5) ,
Total_OD INT ,
Charge_Arrear_amt DECIMAL(18, 5)
)
insert into ##vOD_Calc ( Seq_No ,
Contract_id ,
Rental_id ,
Actual_OD ,
Logic_OD ,
Due_dte ,
Arrear_amt ,
Total_OD ,
Charge_Arrear_amt)
select 1,719578,1,31,30,'2015-07-24 00:00:00.000',37.17000,177,37.17000
union
select 2,719578,2,31,30,'2015-08-24 00:00:00.000',37.17000,147,37.17000
union
select 3,719578,3,30,30,'2015-09-24 00:00:00.000',37.17000,117,111.51000
union
select 4,719578,4,31,30,'2015-10-24 00:00:00.000',37.17000,87,37.17000
union
select 5,719578,5,30,30,'2015-11-24 00:00:00.000',37.17000,57,37.17000
union
select6,719578,6,27,27,'2015-12-24 00:00:00.000',37.17000,27,111.51000
select * from ##vOD_Calc
Logic required is that once the sum of column [ArrearAmt] of current row and all previous rows becomes greater than $100 then column [ChArrrearAmt] should show that summed up value and in else case the column [ChArrrearAmt] should show the same value as that of column [ArrearAmt].
Once the column [ChArrrearAmt] reaches the threshold of $100 then the same cycle should start again i.e. in above example rental#1 had $37.17 < $100 then rental#1 + rental#2 is also < $100 and at rental#3 sum of rental#1, rental#2 and rental#3 becomes $111.51 which is greater than $100 so its updated in column [CHArrrearAmt]. The same cycle start overs from rental#4 onwards however the summation of [ArrearAmt] will now begin after rental#4 onwards and not from the starting.
Below is the loop based SQL script which handles the above situation, however in BULK its a total deterioration of performance if thousands of rows are to be processed i.e. with a contract having multiple rentals.
Can some buddy help in solving me this situation using Running SUM LOGIC or any other optimal solution?
The case here is that I have to use the result of previously updated column value of [ChArrrearAmt] to take decision for the next row, however with BULK update since the row is not yet updated with latest amount therefore the decision on next row is also giving wrong result.
this is the code with which I have achieved to update the column 'chArrear Amount', however its a loop based solution and performance killer.
INSERT INTO ##vOD_Calc_loop
( Rows_count ,
contract_id
)
SELECT COUNT(*) ,
T.Contract_id
FROM ##vOD_Calc T
GROUP BY T.Contract_id
SET @vCounter = 1
SELECT @vContractID = contract_id ,
@vCount = Rows_count
FROM ##vOD_Calc_loop
WHERE ID = @vCounter
WHILE EXISTS ( SELECT 1
FROM ##vOD_Calc_loop
WHERE ID = @vCounter )
BEGIN
SET @vSeed = 1
WHILE @vSeed <= @vCount
BEGIN
UPDATE T
SET Charge_Arrear_amt = CASE WHEN ISNULL(Charge_Arrear_amt,
0)
+ ISNULL(( SELECT
SUM(CASE
WHEN ISNULL(Arrear_amt,
0) = 0
THEN ISNULL(Charge_Arrear_amt,
0)
ELSE ISNULL(Arrear_amt,
0)
END)
FROM
##vOD_Calc T2
WHERE
Contract_id = T.Contract_id
AND T2.Seq_No < T.Seq_No
AND T2.Seq_No > ISNULL(( SELECT
MAX(Seq_No)
FROM
##vOD_Calc T3
WHERE
Contract_id = T.Contract_id
AND Seq_No < T.Seq_No
AND ISNULL(Charge_Arrear_amt,
0) > @vMinimum_overdue_amt
), 0)
), 0) > @vMinimum_overdue_amt
THEN ISNULL(Charge_Arrear_amt,
0)
+ ISNULL(( SELECT
SUM(CASE
WHEN ISNULL(Arrear_amt,
0) = 0
THEN ISNULL(Charge_Arrear_amt,
0)
ELSE ISNULL(Arrear_amt,
0)
END)
FROM
##vOD_Calc T2
WHERE
Contract_id = T.Contract_id
AND T2.Seq_No < T.Seq_No
AND T2.Seq_No > ISNULL(( SELECT
MAX(Seq_No)
FROM
##vOD_Calc T3
WHERE
Contract_id = T.Contract_id
AND Seq_No < T.Seq_No
AND ISNULL(Charge_Arrear_amt,
0) > @vMinimum_overdue_amt
), 0)
), 0)
ELSE ISNULL(Charge_Arrear_amt,
0)
END
FROM ##vOD_Calc T
WHERE Seq_No = @vSeed
AND Contract_id = @vContractID
SET @vSeed = @vSeed + 1
END
SET @vCounter = @vCounter + 1
SELECT @vContractID = contract_id ,
@vCount = Rows_count
FROM ##vOD_Calc_loop
WHERE ID = @vCounter
END
A.I.K
July 22, 2015 at 7:17 am
Read the following article and consider using the Quirky Update described in it. It also describes different approaches.
July 22, 2015 at 10:41 pm
Luis Cazares (7/22/2015)
Read the following article and consider using the Quirky Update described in it. It also describes different approaches.
Thank you [Luis Cazares] will check this out to see if it helps.
The post is edited as per your advice, actually this was my very first post so was not aware of some forum rules, however I think I was articulate enough to explain my issue 🙂
A.I.K
July 23, 2015 at 1:10 am
Thanks Luis C.
Quirky Update & a little twist in my logic has done my job.
This really was a good article.
A.I.K
July 23, 2015 at 7:32 am
I'm seriously impressed.
Not only you managed to get things done by reading the article, understanding it and adapt it to your needs, you also took the time to read the article in my signature and changed your original post.
I hope most people could be like you when posting questions on these forums.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply