Use of FRAME_CLAUSE in the SQL server

 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

 We have a table.

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.