By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE hel (
[Fam2] [nvarchar](50),
[Dim] [smallint],
[Quantit_pr_vue] [smallint],
[Quantit_restante] [smallint],
[D_signation_2] [nvarchar](50),
[Longueur] [smallint],
[Largeur] [tinyint],
[D_signation_1] [nvarchar](50),
[D_cors] [nvarchar](8),
[FACE] [nvarchar](2),
[NUANCE] [tinyint]
);
INSERT INTO hel (
[Fam2],
[Dim],
[Quantit_pr_vue],
[Quantit_restante],
[D_signation_2],
[Longueur],
[Largeur],
[D_signation_1],
[D_cors],
[FACE],
[NUANCE]
)
VALUES
('ST1' , 296, 25, 25 ,'ST MDF 2F KEN02321 280X210X18 SUP LIG' ,280 ,210 ,'SM 2F KENNY 02321 296 SUP LIG' , 'FONCE' ,'2F' ,1)
, ('ST1' , 296, 50, 50 ,'ST MDF 2F KEN02321 280X210X18 SUP LIG' ,280 ,210 ,'SM 2F KENNY 02321 296 SUP LIG' , 'FONCE' ,'2F' ,1)
, ('ST1' , 296, 100, 100 ,'ST MDF 2F KEN02321 280X210X18 SUP LIG' ,280 ,210 ,'SM 2F KENNY 02321 296 SUP LIG' , 'FONCE' ,'2F' ,1)
, ('ST1' , 296, 150, 150 ,'ST MDF 2F BALANCE 280X210X18 SUPER LIGHT' ,280 ,210 ,'SM 2F BALANCE 296 SUPER LIGHT' , 'BALANCE' ,'2F' ,4)
, ('ST1' , 296, 300, 300 ,'ST MDF 2F BALANCE 280X210X18 SUPER LIGHT' ,280 ,210 ,'SM 2F BALANCE 296 SUPER LIGHT' , 'BALANCE' ,'2F' ,4)
5 rows affected
SELECT
[Fam2],
[Dim],
[Quantit_pr_vue],
[Quantit_restante],
[D_signation_2],
[Longueur],
[Largeur],
[D_signation_1],
[D_cors],
[FACE],
[NUANCE]
FROM [hel] AS h
CROSS APPLY (
SELECT SUM(Quantit_restante) AS RollingTotal
FROM [hel] h2
WHERE h2.Fam2 = h.Fam2
AND h2.Dim = h.Dim
AND h2.Longueur = h.Longueur
AND h2.Largeur = h.Largeur
AND h2.FACE = FACE
AND h2.Quantit_restante <= h.Quantit_restante
) ca
WHERE Fam2 = 'ST1'
AND Dim = 296
AND Longueur = 280
AND Largeur = 210
AND FACE = '2F'
AND RollingTotal <= 500
ORDER BY Fam2, Dim, Longueur, Largeur, FACE, Quantit_restante;
| Fam2 | Dim | Quantit_pr_vue | Quantit_restante | D_signation_2 | Longueur | Largeur | D_signation_1 | D_cors | FACE | NUANCE |
|---|---|---|---|---|---|---|---|---|---|---|
| ST1 | 296 | 25 | 25 | ST MDF 2F KEN02321 280X210X18 SUP LIG | 280 | 210 | SM 2F KENNY 02321 296 SUP LIG | FONCE | 2F | 1 |
| ST1 | 296 | 50 | 50 | ST MDF 2F KEN02321 280X210X18 SUP LIG | 280 | 210 | SM 2F KENNY 02321 296 SUP LIG | FONCE | 2F | 1 |
| ST1 | 296 | 100 | 100 | ST MDF 2F KEN02321 280X210X18 SUP LIG | 280 | 210 | SM 2F KENNY 02321 296 SUP LIG | FONCE | 2F | 1 |
| ST1 | 296 | 150 | 150 | ST MDF 2F BALANCE 280X210X18 SUPER LIGHT | 280 | 210 | SM 2F BALANCE 296 SUPER LIGHT | BALANCE | 2F | 4 |