Query to Update balance based on columns from two different rows.

Question

Post reply

Query to Update balance based on columns from two different rows.

pingeli dileep

Old Hand

Points: 353
More actions
March 30, 2023 at 3:03 pm
Go to Answer
#4167154

Hello All, Below is the my existing table structure from which I am trying to do an update statement.

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.

Jonathan AC Roberts

SSCoach

Points: 18361

March 30, 2023 at 3:56 pm

Answer

#4167187

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 
;

Screenshot 2023-03-30 165757

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246974 More actions · Answer 1

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 2

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?

pingeli dileep Old Hand Points: 353 More actions · Answer 3

Hello Phil, Thanks for the reply actually we have a Date Column in the table(CreatedDate) to do Order By.

pingeli dileep Old Hand Points: 353 More actions · Answer 4

Hello Jonathan , Below is the before and after data from the table.

Before:

before

After:

Screenshot 2023-03-30 203223

Phil Parkin SSC Guru Points: 246974 More actions · Answer 5

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;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pingeli dileep Old Hand Points: 353 More actions · Answer 6

Jonathan, Thank you..! really appreciate your help. It worked.