Query to Update balance based on columns from two different rows.
-
Hello All, Below is the my existing table structure from which I am trying to do an update statement.
<!--more-->
I need a query to update records for each employee and subtract it from a column value of different row. For example in the above case query should update the value in closing balance for empid 1 like
ClosingBalance-LeavesTaken (20.5 - 1) = 19.5 has to be updated in the second row closing balance column. In the same way for next record
ClosingBalance-LeavesTaken(19.5 - 3) = 16.5 has to be updated in the third row closing balance column.
Any help here would be really appreciated.
-
The sample data you have provided has no column which provides ordering. Without a column to order by, a T-SQL solution is not possible.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
I can't see how you can order the data in that table?
Can you provide how the table looks before and after the update?
-
Hello Phil, Thanks for the reply actually we have a Date Column in the table(CreatedDate) to do Order By.
-
Hello Jonathan , Below is the before and after data from the table.
Before:
After:
-
Here is a version with a sequence number which you should be able to adapt
DROP TABLE IF EXISTS #Leave;
CREATE TABLE #Leave
(
EmployeeId INT
,SeqNo INT NOT NULL
,OpeningBalance DECIMAL(5, 2)
,LeavesTaken INT
,ClosingBalance DECIMAL(5, 2)
,
PRIMARY KEY CLUSTERED(
EmployeeId
,SeqNo
)
);
INSERT #Leave
(
EmployeeId
,SeqNo
,OpeningBalance
,LeavesTaken
)
VALUES
(1, 1, 21.5, 1)
,(1, 2, 21.5, 1)
,(1, 3, 21.5, 3);
WITH totals
AS (SELECT l.ClosingBalance
,ClosingBalanceCalc = l.OpeningBalance
- SUM (l.LeavesTaken) OVER (PARTITION BY l.EmployeeId ORDER BY l.SeqNo ROWS UNBOUNDED PRECEDING)
FROM #Leave l)
UPDATE totals
SET totals.ClosingBalance = totals.ClosingBalanceCalc;
SELECT *
FROM #Leave l
ORDER BY l.EmployeeId
,l.SeqNo;If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
drop table if exists #temp;
select *
into #temp
from (values (1,21.5,1,20.5, cast('20230121' as date)),
(1,21.5,1, NULL,cast('20230222' as date)),
(1,21.5,3, NULL,cast('20230317' as date)),
(2,20,1, 19, cast('20230121' as date)),
(2,20,2, NULL,cast('20230222' as date)),
(2,20,1, NULL,cast('20230317' as date)),
(2,20,5, NULL,cast('20230321' as date))) T(EmployeeId, OpeningBalance, LeaveTaken,ClosingBalance, CreatedDate)
;
select * from #temp;
with cte as
(
select t.ClosingBalance,
t.OpeningBalance,
sum(t.LeaveTaken) over (partition by t.EmployeeId ORDER BY t.CreatedDate) TotalLeaveTaken
from #temp t
)
update cte
set cte.ClosingBalance = cte.OpeningBalance - cte.TotalLeaveTaken
;
select * from #temp
; -
Jonathan, Thank you..! really appreciate your help. It worked.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply