If we want to get the running total, average, min or max value we can use the frame clause. See the below example.
Read here: Window Frame in window function
https://bageshkumarbagi-msbi.blogspot.com/2024/07/window-frame-in-window-function.html
create table employee( id int identity(1,1) primary key, empid int, emp_name varchar(50), salary_withdraw decimal(18,10), withdraw_dt date ) |
Inserting some records in this
table.
insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1002,'01-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1000,'02-29-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1020,'03-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1050,'04-30-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(1,'Bagesh',1030,'05-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',2002,'02-29-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',2009,'03-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',2000,'04-30-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(2,'Rajesh',1999,'05-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1012,'02-29-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1052,'03-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1082,'04-30-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(3,'Mohan',1002,'05-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',87,'02-29-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',77,'03-31-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',97,'04-30-2024'); insert into employee(empid,emp_name,salary_withdraw,withdraw_dt) values(4,'Sohan',67,'05-31-2024'); |
See the records in the table.
select empid,emp_name,salary_withdraw,withdraw_dt ,sum(salary_withdraw) over(partition by empid order by withdraw_dt rows between unbounded preceding and 0 following) as running_total ,sum(salary_withdraw) over(partition by empid order by withdraw_dt rows between unbounded preceding and 0 following) as running_total ,avg(salary_withdraw) over(partition by empid order by withdraw_dt rows between unbounded preceding and 0 following) as running_avg ,min(salary_withdraw) over(partition by empid order by withdraw_dt rows between unbounded preceding and 0 following) as running_min ,max(salary_withdraw) over(partition by empid order by withdraw_dt rows between unbounded preceding and 0 following) as running_max from employee select empid,emp_name,salary_withdraw,withdraw_dt ,sum(salary_withdraw) over(partition by empid order by withdraw_dt rows between unbounded preceding and CURRENT ROW) as running_total ,sum(salary_withdraw) over(partition by empid order by withdraw_dt rows between unbounded preceding and CURRENT ROW) as running_total ,avg(salary_withdraw) over(partition by empid order by withdraw_dt rows between unbounded preceding and CURRENT ROW) as running_avg ,min(salary_withdraw) over(partition by empid order by withdraw_dt rows between unbounded preceding and CURRENT ROW) as running_min ,max(salary_withdraw) over(partition by empid order by withdraw_dt rows between
unbounded
preceding
and CURRENT ROW) as running_max from employee |
Either we can write 0
following or Current row.
nice post
ReplyDeletedata science course in chennai