ROWS BETWEEN AND RANGE BETWEEN WINDOW CLAUSE

---DAY 5

--#15DAYSSQLCHALLENGE

--DROP TABLE Employees

Create Table Employees

(

 Id int primary key,

 HIRE_DATE DATE,

 Name nvarchar(50),

 Salary int

)

Go

Insert Into Employees Values (1,'2022-12-21 12:53:27.120', 'Mark', 1000)

Insert Into Employees Values (6, '2022-12-20 12:53:27.120' ,'ARABINDA', 1000)

Insert Into Employees Values (6, '2022-12-2 12:53:27.120' ,'NAYAK', 1000)

Insert Into Employees Values (2, '2022-12-8 12:53:27.120', 'John', 2000)

Insert Into Employees Values (3, '2022-12-9 12:53:27.120', 'Pam', 3000)

Insert Into Employees Values (4, '2022-12-11 12:53:27.120',  'Sara', 4000)

Insert Into Employees Values (5, '2022-12-14 12:53:27.120', 'Todd', 5000)

Insert Into Employees Values (5, '2022-12-14 12:53:27.120', 'Todd', 5000)

Go

--Now execute the following query. Notice that we get the running total as expected.

--1. When no value is specified for ROWS or RANGE clause
--2. When RANGE clause is used explicitly with it's default value
--3. When ROWS clause is used instead of RANGE clause
--ROWS BETWEEN doesn't care about the exact values. It cares only about the order of rows, and takes fixed number of preceding and following rows 
--when computing frame.
--RANGE BETWEEN cluase consider each row as entities 
--UNBOUNDED PRECEDING – All rows before the current row.
--n PRECEDING – n rows before the current row.
--CURRENT ROW – Just the current row.
--n FOLLOWING – n rows after the current row.
---UNBOUNDED FOLLOWING – All rows after the current row.

SELECT  ID, HIRE_DATE,Name, Salary, 
SUM(Salary) OVER(ORDER BY Salary ) as 'Running Total Default',

    SUM(Salary) OVER(ORDER BY Salary  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal_ROWS,

SUM(Salary) OVER(ORDER BY Salary  DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal_RANGE,

AVG(Salary) OVER (ORDER BY Salary DESC  ROWS BETWEEN UNBOUNDED PRECEDING AND unbounded FOLLOWING) AS 'Running_Avg',

AVG(Salary) OVER (ORDER BY HIRE_DATE ROWS BETWEEN 2  PRECEDING AND CU
RRENT ROW ) AS 'Running_2_Avg',

AVG(Salary) OVER (ORDER BY HIRE_DATE ROWS BETWEEN 29 PRECEDING AND CURRENT ROW )as '30day_moving_average'

FROM Employees
ORDER BY Salary desc