Discussion :

[sebastien_m]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[SQLpro]

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 +

[sebastien_m]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[SQLpro]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +

[sebastien_m]

Bonjour,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[al1_24]

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