Saturday 20 July 2024

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.


1 comment:

If you have any doubt, please let me know.

Popular Posts