1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
|
;WITH Art AS (
SELECT TOP 100 PS.Famille, PS.Référence
, PS.[Px Achat HT net] AS Prix_Achat
, PS.[Px Achat HT net] AS Somme_Cumulee
, ROW_NUMBER() OVER ( PARTITION BY LEFT( Famille, 1 ) ORDER BY PS.[Px Achat HT net] DESC ) Classement
FROM dbo.Produits_Stocks PS
WHERE LEFT( PS.Famille, 1 ) IN ( SELECT DISTINCT LEFT( Code, 1 )
FROM Inside_Replication.dbo.Lib_Famille
WHERE Lib_Sous_Rayon = @P_Type_Gage )
AND PS.Fournisseur IN ( SELECT Gageur
FROM Inside_Gages.dbo.Banque_Gageurs
WHERE Banque = @P_Banque )
AND PS.[Px Achat HT net] > 70000.00
AND LEFT( PS.Magasin, 3 ) = @P_Magasin
AND NOT EXISTS ( SELECT NULL
FROM dbo.Produits_Gages PG
WHERE PG.Reference_Gage = PS.Référence ) )
,Calcul_Gages AS (
SELECT *
, CASE WHEN Somme_Cumulee < @P_Tranche_1 THEN 1 ELSE 1 END Gage
FROM Art
WHERE Classement = 1
UNION ALL
SELECT A.Famille
, A.Référence
, A.Somme_Cumulee AS Prix_Achat
, A.Prix_Achat + T.Somme_Cumulee
, A.Classement
, ( SELECT CASE WHEN T.Somme_Cumulee < @P_Tranche_1 AND T.Gage = 1
THEN 1 ELSE 0 END ) Gage
FROM Art A
INNER JOIN Calcul_Gages T ON A.Classement = T.Classement + 1 )
INSERT INTO Inside_Gages.dbo.Produits_Gages
( ID_Gage, Banque, Reference_Gage )
SELECT @P_Id_Gage, @P_Banque, Référence
FROM Calcul_Gages
WHERE Gage = 1; |
Partager