Скачать или смотреть T-SQL: Command OVER. Alternative for subquery. Running totals.Partitioning and ordering.

CREATE TABLE TABLE2
(ORD nvarchar(10),
DT datetime,
CUSTOMER nvarchar(200),
PRODUCT_NAME nvarchar(200),
QUANTITY decimal(18,2),
PRICE decimal(18,2))

INSERT INTO TABLE2 VALUES
('001','2018-07-01','Customer Apple','TABLE 40x60',50,32.55),
('002','2018-07-02','Customer Orange','TABLE 100x60',10,65.00),
('003','2018-07-02','Customer Apple','CHAIR X1',30,15),
('004','2018-07-03','Customer Orange','CHAIR X2',20,13),
('005','2018-07-03','Customer Melon','PC IBM',10,800),
('006','2018-07-04','Customer Apple','PC HP',10,720.12),
('007','2018-07-04','Customer Orange','PC DELL',8,950),
('011','2018-08-11','Customer Apple','PC DELL',1,320.55),
('012','2018-08-12','Customer Orange','TABLE 100x60',10,25.00),
('013','2018-08-12','Customer Apple','CHAIR X1',30,15),
('014','2018-08-13','Customer Orange','CHAIR X2',50,13),
('015','2018-08-17','Customer Melon','PC IBM',5,800),
('016','2018-08-20','Customer Apple','PC HP',5,720.12),
('017','2018-08-22','Customer Orange','CHAIR X1',8,50),
('021','2018-09-01','Customer Apple','PC LENOVO',3,551),
('022','2018-09-08','Customer Orange','TABLE 200x220',1,250.00),
('023','2018-09-09','Customer Apple','CHAIR X1',30,15),
('024','2018-09-10','Customer Orange','CHAIR X2',50,13),
('025','2018-09-14','Customer Melon','CHAIR X3',90,20),
('026','2018-09-19','Customer Apple','CHAIR X4',5,12.50),
('027','2018-09-28','Customer Melon','CHAIR X1',8,50)
-------------------------------------------

select
convert(nvarchar(8),DT,112) as [Date],
(QUANTITY*PRICE) as Amount,
ROW_NUMBER() OVER (ORDER BY DT) as SeqPerYear,
ROW_NUMBER() OVER (PARTITION BY month(DT) ORDER BY DT) as SeqPerMonth,
'/' as '_',
SUM(QUANTITY*PRICE) OVER (PARTITION BY month(DT) ORDER BY DT,ORD) as RunningPerMonth,
max(QUANTITY*PRICE) OVER (PARTITION BY month(DT) ) as MaxPerMonth,
min(QUANTITY*PRICE) OVER (PARTITION BY month(DT) ) as MinPerMonth,
avg(QUANTITY*PRICE) OVER (PARTITION BY month(DT) ) as AvgPerMonth,
'/' as '_',
max(QUANTITY*PRICE) OVER (PARTITION BY year(DT) ) as MaxPerYear,
min(QUANTITY*PRICE) OVER (PARTITION BY year(DT) ) as MinPerYear,
avg(QUANTITY*PRICE) OVER (PARTITION BY year(DT) ) as AvgPerYear,
'/' as '_',
SUM(QUANTITY*PRICE) OVER (PARTITION BY year(DT) ORDER BY DT,ORD) as RunningPerYear,
SUM(QUANTITY*PRICE) OVER (PARTITION BY year(DT) ) as TotalPerYear
FROM TABLE2
ORDER BY ORD

-------------------------------------------
DROP TABLE TABLE2
-------------------------------------------