How can I exclude rows that exceed the accumulative sum?-CodePudding

I have a simple query which is below:

SELECT fam1 Fam2,
       dim,
       quantit_pr_vue,
       quantit_restante,
       d_signation_2,
       longueur,
       largeur,
       d_signation_1,
       d_cors,
       face,
       nuance
FROM   hel
GROUP  BY fam1,
          fam2,
          dim,
          quantit_pr_vue,
          quantit_restante,
          d_signation_2,
          longueur,
          largeur,
          d_signation_1,
          d_cors,
          face,
          nuance
ORDER  BY fam2,
          longueur,
          nuance;

The result is below :

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
ST1 296 300 300 ST MDF 2F BALANCE 280X210X18 SUPER LIGHT    280 210 SM 2F BALANCE 296 SUPER LIGHT   BALANCE 2F  4

I want to exclude the last row which has Quantit_restante = 300 because when summing it to the previous records, it exceeds 500.

The screenshot only shows only a group which is Fam2 =ST1, Dim=296, Logueur=280, Largeur =210, FACE = 2F.

CodePudding user response：

You can use window function SUM() to get the running total and exclude the rows that the running total exceeds 500:

SELECT *
FROM (
  SELECT *, SUM(Quantit_restante) OVER (ORDER BY Fam2) total
  FROM [dbo].[hel]
) t
WHERE total <= 500
ORDER BY Fam2;

CodePudding user response：

Also by applying to a rolling total.

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

db<>fiddle here