Répartition de valeur

Bonjour à tous,

Je cherche un moyen de répartir une valeur sur plusieurs enregistrements au prorata de chaque ligne.
Par exemple : J'ai une commande qui comporte des lignes, chaque ligne possède une valeur. Quand j'ai des frais de ports associés à la commande, je veux les répartir au prorata du montant de chaque ligne.

Je souhaite également être certain de ne pas perdre un centime en route lors de la répartition.

Je sais le faire en SQL, mais je voudrais fédérer mon code à un seul endroit.

J'ai donc créé le type SQL suivant :

Code:

---

1 2 3

CREATE TYPE [dbo].[ClefValeur] AS TABLE( [Clef] [varchar](max) NULL, [Valeur] [numeric](19, 8) NULL)

---

J'ai ensuite créé une fonction :

Code:

---

1 2	CREATE FUNCTION [GPSQL].[test_prorata] (@ListeClefValeur ClefValeur READONLY, @ValeurARepartir numeric(19,8), @NbChiffreArrondi int) RETURNS @Resultat TABLE (Clef varchar(max), valeur numeric(19,8), ValeurRepartie numeric(19,8))

---

Ma fonction fonctionne correctement lorsque je l'utilise comme ci dessous :

Code:

---

1 2 3 4 5

DECLARE @x ClefValeur INSERT INTO @x SELECT 'A', 1.5 INSERT INTO @x SELECT 'B', 3.5   SELECT * FROM [GPSQL].[test_prorata] (@x , 0.111, 2)

---

Afin d'éviter cet INSERT, j'ai tenté la requête suivante :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

;WITH Liste AS ( SELECT 'A' as Clef, 1.5 as Valeur UNION ALL SELECT 'B' As Clef, 3.5 as Valeur ) --SELECT * from Liste , Repartition as ( SELECT * FROM [GPSQL].[test_prorata] (Liste, 10, 2) ) select * from Repartition

---

ça ne fonctionne pas. Je comprends bien les origines possibles du problème :
- Liste n'est pas une variable
- Liste n'est pas du type attendu.
Le message d'erreur SQL est "Nom de colonne non valide*: 'Liste'."

Pensez vous que je peux aboutir sur cette piste ?
Il y a peut être une autre idée à explorer ?

Merci d'avance

Pourquoi ne pas tout simplement créer une table ?
Pourquoi utilisez vous le type VARCHAR(max) qui est un LOB (précisément un CLOB) destiné à manipuler de TRES grand texte (limitées à 2 milliards d'octets) et qui ne supporte pas certaines opérations relationnelle ?
Enfin, un nom d'objet (votre table dérivé de votre type table) ne peut pas être un paramètre d'une fonction...

Tout ceci est très confus et n'a pas grand sens.

Exprimez vous avec un exemple concret sans mettre un SQL abscons.

A +

Bonjour SqlPro,

à la base, je cherche un moyen de répartir un montant sur plusieurs enregistrements au prorata de la valeur de chacun, sans perdre un morceau en route.

Voici un exemple concret de ce que je cherche à faire :

Code:

---

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 40 41 42 43 44 45 46 47 48 49 50 51

DECLARE @ValeurARepartir NUMERIC(19, 8) = 0.111 ,@Arrondi INT = 2;   WITH Liste AS ( SELECT ROW_NUMBER() OVER ( ORDER BY Clef ) AS lig ,Clef ,Valeur FROM ( SELECT 'A' AS Clef ,1.5 AS Valeur   UNION ALL   SELECT 'B' AS Clef ,3.5 AS Valeur   UNION ALL   SELECT 'C' AS Clef ,3.5 AS Valeur ) AS TMP ) ,CUMUL AS ( SELECT count(1) AS NbLig ,SUM(Valeur) AS ValeurTotale FROM Liste ) ,LISTE_CUMUL AS ( SELECT Liste.Lig ,Liste.Clef ,Liste.Valeur ,CUMUL.NbLig ,CUMUL.ValeurTotale FROM Liste CROSS JOIN CUMUL ) SELECT CASE WHEN Lig = NbLig THEN @ValeurARepartir - ( SELECT SUM(ROUND(@ValeurARepartir * Valeur / ValeurTotale, @Arrondi)) FROM LISTE_CUMUL WHERE Lig <> nbLig ) ELSE ROUND(@ValeurARepartir * Valeur / ValeurTotale, @Arrondi) END FROM LISTE_CUMUL

---

Je voudrais rendre ce code réutilisable dès que je cherche à répartir une valeur.

je m'orientais donc vers une fonction table.

J'aurais aimé pouvoir écrire quelque chose du genre :

Code:

---

SELECT * FROM MafonctionDeRepartition (@RequeteClefValeur nvarchar(max), @ValeurARepartir numeric(19,8), @Arrondi int)

---

Je sais que ce n'est pas possible (à cause du SQL dynamique que ça impliquerait), j'essaie donc de contourner ce problème.
D'où mon post avec un essai comportant un type.
Cet après-midi, sentant bien que avec un type c'est une impasse,je fais un test avec du @Xml.

Non, montrez moi un exemple concret... pas du SQL

Par exemplue :
j'ai la colonne ZigZorNifle que je cherche à répartir par la colonne PetoChiasse, pour le montant figurant dans Duflonigon

Code:

---

1 2 3 4 5 6 7

ZigZorNifle PetoChiasse Duflonigon ------------- -------------- ----------- AAAAA 10 1234.25 AAAAA 20 1234.25 AAAAA 30 1234.25 ZZZZZ 50 487152.12 ZZZZZ 50 487152.12

---

Au final je veut avoir ces valeurs là :

Code:

---

1 2 3 4 5 6 7 8

ZigZorNifle PetoChiasse Duflonigon Répartition ------------- -------------- ------------ --------------- AAAAA 10 1234.25 2057,54 AAAAA 20 1234.25 4115,08 AAAAA 30 1234.25 6172,63 ZZZZZ 50 487152.12 243576,06 ZZZZZ 50 487152.12 243576,06

---

Voila ce qu'est un exemple....

A +

Bonjour,

Un exemple concret :
un utilisateur saisit une commande avec 3 lignes, j'ai donc 3 enregistrements dans la table CDE_LIG :

Code:

---

1 2 3 4 5

Lig montant 1 22.50 2 19.20 3 21.90

---

Sur cette commande, il indique 5.98 € de frais de port sur l'entête de commande.
Je cherche à répartir ce montant de 5.98€ sur chacune des lignes :

Code:

---

1 2 3 4

NoLigne montant ValeurRepartie 1 22.50 2.12 2 19.20 1.81 3 21.90 2.05

---

je n'ai pas de problème pour faire ce calcul pour les commandes.
Je cherche juste un moyen de faire cela de manière générique : il arrive de retrouver ce genre de problématique en compta, mais aussi en fabrication.

Le principe est de répartir le montant proportionnellement sur toutes les lignes en affectant ensuite la différence d'arrondi sur une seule.