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;
go
update
TMP1
set
Порог = [dbo].last3(Клиент, Дата) go
select
*
from
TMP1
Telegram: DB developers channel