SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
drop table if exists TMP1; create table TMP1 ( Клиент int, Дата date, Сумма int, Порог int ); insert into TMP1 ( Клиент, Дата, Сумма, Порог ) values (1, '20220201', 5000, 0), (1, '20220202', 10000, 0), (1, '20220203', 5000, 0), (1, '20220204', 36000, 0), (1, '20220205', 17000, 0), (1, '20220206', 100000, 0), (1, '20220207', 33445, 0), (2, '20220104', 700, 0), (2, '20220105', 400, 0), (2, '20220106', 850, 0), (2, '20220107', 50, 0), (2, '20220108', 1000, 0); select * from TMP1; drop function if exists [last3]; CREATE function [last3](@kl int, @dt date) returns integer as Begin return( SELECT SUM(q1.SUMMA) FROM ( SELECT TOP 3 [СУММА] AS SUMMA FROM TMP1 WHERE [КЛИЕНТ] = @KL AND [ДАТА] <= @DT ORDER BY [СУММА] DESC ) q1 ) End; SELECT Клиент, Дата, Сумма, [dbo].last3(Клиент, Дата) Порог from TMP1; update TMP1 set Порог = [dbo].last3(Клиент, Дата); select * from TMP1
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear