Sous requête et période [Résolu]

[EmmanuelleC]

Bonjour,

Je cherche a faire une table de prix en sql server 2005 pour l'attaquer ensuite avec Business object

Actuellement j'ai une table qui contient les champs suivant

Code :

1
2
3
4
5
6
7
CREATE TABLE MaTable
(du datetime, 
au datetime, 
client int, 
pn varchar, 
prix real, 
priorite int)

avec ce genre de donnée dedans

Code :

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO MaTable VALUES ('01/01/2011','31/12/2011', 1, 'a', 90, 1)
INSERT INTO MaTable VALUES ('01/01/2011','31/12/2011', 1, 'b', 160, 1)
INSERT INTO MaTable VALUES ('01/01/2011','31/12/2011', 1, 'a', 100, 3)
INSERT INTO MaTable VALUES ('01/01/2011','31/12/2011', 1, 'b', 200, 3)
INSERT INTO MaTable VALUES ('01/01/2011','31/12/2011', 2, 'a', 0.9, 2)
INSERT INTO MaTable VALUES ('01/01/2011','31/12/2011', 2, 'b', 0.8, 2)
INSERT INTO MaTable VALUES ('01/01/2011','31/12/2011', 2, 'a', 100, 3)
INSERT INTO MaTable VALUES ('01/01/2011','31/12/2011', 2, 'b', 200, 3)
INSERT INTO MaTable VALUES ('31/12/2010','30/11/2011', 3, 'a', 0.9, 2)
INSERT INTO MaTable VALUES ('31/12/2010','30/11/2011', 3, 'b', 0.8, 2)
INSERT INTO MaTable VALUES ('01/01/2011','31/12/2011', 3, 'a', 100, 3)
INSERT INTO MaTable VALUES ('01/01/2011','31/12/2011', 3, 'b', 200, 3)

Le principe c'est que :
du au => la remise cours de telle date a telle date
client => ID du client
pn => ID de l'article
prix => soit un prix net (200 si le client paye 200 euro par exemple) soit un pourcentage a multiplier par le tarif (0.8 si le client a 20% de remise par exemple)
priorite => 1 si c'est un prix net, 2 si c'est une remise, 3 si c'est le tarif

Je voudrais faire une view sur cette table de facon a, au lieu d'avoir dans la colonne prix le prix ou la remise, avoir toujours le prix

Exemple : pour le client 3, pour l'article 1 et le 30/09/2011, je voudrais avoir dans la colonne prix avoir 90 (qui correspond a 100, prix tarif pour ce client, cet article et cette date, * 0.9 qui est la remise pour ce client, cet article et cette date) au lieu d'avoir 0.9

Mon probleme : les remises et les tarifs n'ont pas forcement les memes dates de validité et je n'arrive pas a écrire une sous requete comme je veux

si je fais ce code

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
SELECT 
t1.du, 
t1.au, 
t1.client, 
t1.pn, 
t1.prix AS [Prix ou remise],
t1.priorite,
(SELECT prix 
FROM matable t2 
WHERE 
    t2.priorite=3 AND 
    t2.pn = t1.pn AND 
    t2.client = t1.client AND  
    t2.du =t1.du  AND 
    t2.au=t2.au) AS [prix tarif] ,
case 
    when priorite IN (2) 
    then  round(t1.prix *
        (SELECT prix 
        FROM matable t2 
        WHERE 
            t2.priorite=3 AND 
            t2.pn = t1.pn AND 
            t2.client = t1.client AND  
            t2.du =t1.du  AND 
            t2.au=t2.au),2) 
    else t1.prix 
    end AS [Prix net final]
FROM matable t1

Ca marche pour le client 1 : il suffit de garder le prix net
Ca marche pour le client 2 : parce que coup de bol, la date de validité du tarif est la meme que celle de la remise
Ca ne marche pas pour le client 3 : la remise et le tarif n'ont pas exactement les meme période et du coup ca foire
si a la place je fais ca, ca fonctionne mais j'impose la date dans SQL alors que je veux pouvoir la choisir en Business object

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
SELECT 
t1.du, 
t1.au, 
t1.client, 
t1.pn, 
t1.prix AS [Prix ou remise],
t1.priorite,
(SELECT prix 
FROM matable t2 
WHERE 
    t2.priorite=3 AND 
    t2.pn = t1.pn AND 
    t2.client = t1.client AND  
    t2.du <= getdate() AND 
    t2.au >= getdate()) AS [prix tarif] ,
case 
    when priorite IN (2) 
    then  round(t1.prix *
        (SELECT prix 
        FROM matable t2 
        WHERE 
            t2.priorite=3 AND 
            t2.pn = t1.pn AND 
            t2.client = t1.client AND  
            t2.du <= getdate() AND 
            t2.au >= getdate()),2) 
    else t1.prix 
    end AS [Prix net final]
FROM matable t1
WHERE 
t1.au  <= getdate() AND
t2.du  >= getdate()

Dans business object, la vue sera toujours utilisée avec un client, une date et une liste d'articles donc il n'y aura pas de probleme
Il n'y a qu'une remise et qu'un tarif a une date donnée, donc je peux toujours faire la multiplication mais je ne sais pas comment faire la view dans SQL

Je peux y faire quelque chose ? ou je dois forcement finir le boulor dans BO?

J'espere etre sur le bon forum (j'espere avoir bien identifié le fait que le probleme est générique et pas lié a SQL server 2005)

Merci d'avance,

Emmanuelle

[StringBuilder]

Bonjour,

Une suggestion :
Le type REAL (ou FLOAT) est un type de précision inexacte. Ceci implique qu'on peut stocker une valeur avec ce type, et lorsqu'on l'interroge, la valeur restituée n'est pas celle qu'on lui a donné.

Un "bug" d'affichage dans le query anaylsez de SQL Server 2000 mettait bien en évidence ce phénomène : lorsqu'on entrait "2" dans une champ de type float, lorsqu'on l'interrogeait, il était transformé en "1,9999984" par exemple.

La marge d'erreur, même si elle semble faible, peut rapidement devenir problématique lors de calculs sur des prix.

Pour stocker des prix, il faut donc toujours utiliser le type DECIMAL, qui a une précision fixe : le nombre de valeur possibles est énumérable, et il n'y a pas de "trou".

Ensuite, une question :
Ton champ priorité indique des tarifs de base, des remises et des tarifs nets. Quelle est la règle pour la présence d'un tarif net ? Pourquoi stocker une valeur calculée (il ne faut jamais le faire !) Est-on certain que le tarif net = tarif de base + remise ? Que faire s'il est différent ? Quel tarif est systématiquement renseigné ?

Réponse à ta question :
- Si le tarif net est toujours renseigné et prime sur le tarif brut + remise, alors ne lit que celui-ci.
- Sinon, ne stocke pas le tarif net.

Ca simplifiera pas mal la requête déjà, puisque tu n'auras plus qu'à faire un select sur le tarif de base applicable le jour J, avec une jointure ouverte sur la remise applicable le jour J.

[EmmanuelleC]

Bonjour,

Tout d'abbord, merci beaucoup pour le feedback sur le type de donnée chiffrée, je vais aller corriger ca

Pour le probleme de fond, le prix net n'a rien a voir avec la remise (désolée, je n'ai pas bien bien choisit mes termes )

On a un tarif pour tout nos articles, par défaut, si on ne fait rien d'autre le client a le prix tarif.
Si on ne veut pas qu'il ai le prix tarif, on a deux solutions :
- on fixe un prix net
ou
- on fixe une remise sur le tarif

Imaginons que le prix tarif de brique de lait soit de 1€, on peut soit
- avoir un client qui n'a rien négocié et qui va payer 1€ (prix : 1 et priorité : 3)
- avoir un client qui a négocié qu'il payerait sa brique de lait 0,8 € (prix : 0.8 et priorité : 1)
- avoir un client qui a négocié qu'il aurait 15% de remise sur sa brique de lait (prix : 0.85 et priorité : 2)

les prix nets négocié (la priorité 1) sont donc obligés d'etre stockés (ils sont encodé dans l'erp ou je vais les chercher)

Il y a toujours un prix tarif (l'erp met un prix tarif de 0 tant qu'on en a pas encore fixé) donc s'il y a une remise, je peux faire tarif * remise et avoir un résultat

Est-ce que c'est moins confus maintenant?

[StringBuilder]

D'accord.

Si je comprends bien, il y a un enregistrement "3" pour chaque produit, sans être affecté à un client (clientid = null).
Il est systématiquement rempli.

Ensuite, pour un client donné, on peut avoir un prix 1 ou 2 renseigné, et à ce moment, il faut faire le calcul.

C'est bien ça ?

Si oui, je verrais un truc du genre :
Avec <X> = Code produit
Et <Y> = Code client

Code :

1
2
3
4
5
6
7
8
9
 
SELECT case when t1.prix IS NOT NULL then t1.prix else isnull(t2.prix, 1) * t3.prix end prix;
FROM matable t3
LEFT OUTER JOIN matable t2 ON getdate() BETWEEN t2.du AND t2.au AND t2.client = <Y> /* ou t3.client si non null */ AND t2.pn = t3.pn AND t2.priorite = 2
LEFT OUTER JOIN matable t1 ON getdate() BETWEEN t1.du AND t1.au AND t1.client = <Y> /* ou t3.client si non null */ AND t1.pn = t3.pn AND t1.priorite = 1
WHERE getdate() BETWEEN t3.du AND t3.au
AND t3.client IS NULL -- Ou alors <Y> si le prix 3 est dupliqué pour tous les clients
AND t3.pb = <X>
AND t3.priorite = 3;

Si tu n'as pas de filtre sur <X> et <Y> (pour faire une vue par exemple), alors voici la requête que je te propose :

Code :

1
2
3
4
5
6
7
8
9
 
SELECT t3.pn, c.client /* ou t3.client si non null */, case when t1.prix IS NOT NULL then t1.prix else isnull(t2.prix, 1) * t3.prix end prix;
FROM matable t3
CROSS JOIN client c -- seulement si t3.client est null
LEFT OUTER JOIN matable t2 ON getdate() BETWEEN t2.du AND t2.au AND t2.client = c.client /* ou t3.client si non null */ AND t2.pn = t3.pn AND t2.priorite = 2
LEFT OUTER JOIN matable t1 ON getdate() BETWEEN t1.du AND t1.au AND t1.client = c.client /* ou t3.client si non null */ AND t1.pn = t3.pn AND t1.priorite = 1
WHERE getdate() BETWEEN t3.du AND t3.au
AND t3.client IS NULL -- Ou alors <Y> si le prix 3 est dupliqué pour tous les clients
AND t3.priorite = 3;

[EmmanuelleC]

Le tarif est affecté a un client (enfin, c'est l'inverse, c'est plusieurs clients qui sont affectés a un tarif et il existe plusieurs tarifs) mais le montant qui est dans la table avec la priorité 3 est le tarif de ce client la pour cet article la à la période indiquée.

Le code fonctionne mais il ne résoud pas mon probleme dans la mesure ou il impose la date au niveau du SQL.
Ce que je veux faire, c'est c'est choisir via BO a la fin la date, le client et l(es) article(s)

Et c'est vraiment important que je puisse le faire parce que par exemple, en decembre, il y va avoir plein de client qui vont vouloir connaitre leur prix 2012 et si je ne sais donner les prix qu'au 15/12/2011 ca ne va pas les arranger

Mais je ne trouve pas comment faire une sous requete (ou une jointure) sans figer une date parce que les dates de début et de fin des remises ne coincide pas forcement avec celles du tarif

Au pire, je sais le faire avec BO au final, mais
1) c'est pas propre, ca laisse une view non finie en SQL
2) en terme de performance, il vaudrait mieux que les calculs soient fait en sql directement

[StringBuilder]

Il faut faire une jointure avec une table qui contient un calendrier des dates possibles.

Soit une sous-requête avec une fonction analytique, soit une table de calendrier en dur.

[StringBuilder]

Ce qui donne au final :

Code :

1
2
3
4
5
6
7
 
SELECT c.date, t3.pn, t3.client, case when t1.prix IS NOT NULL then t1.prix else isnull(t2.prix, 1) * t3.prix end prix;
FROM calendrier c
INNER JOIN matable t3 ON c.date BETWEEN t3.du AND t3.au
LEFT OUTER JOIN matable t2 ON c.date BETWEEN t2.du AND t2.au AND t2.client = t3.client AND t2.pn = t3.pn AND t2.priorite = 2
LEFT OUTER JOIN matable t1 ON c.date BETWEEN t1.du AND t1.au AND t1.client = t3.client AND t1.pn = t3.pn AND t1.priorite = 1
WHERE t3.priorite = 3;

Il faut donc créer une table (ou vue) calendrier qui contiennent tous les jours dont tu as besoin dans tes analyses BO.

[EmmanuelleC]

heu... je ne comprend plus, j'ai déja une table calendrier (qui sert a d'autres choses) mais je ne vois pas comment elle va m'aider

si je met du code du genre

Code :

LEFT OUTER JOIN matable t2 ON getdate() BETWEEN t2.du AND t2.au

Je ne suis pas entrain de faire pas la jointure sur base du fait que la date du jour soit entre du et au?
Et si c'est bien ca qu'on ca fait, alors je vais ramener les conditions de prix actuelles, pas celles de la date choisie non?

Je ne suis pas tres bonne en sql, donc c'est possible que je m'emele les pinceaux et que j'ai mal compris ce que tu expliques, toutes mes excuses

[StringBuilder]

Exemple pour ta vue calendar, qui contiendra 1 an de dates passées, et 1 an de dates futures : (tu peux ajouter comme tu le souhaite en modifiant les valeurs)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE VIEW calendar
AS
WITH CTE_date
AS
(
  SELECT dateadd(year, -1, dbo.getdate()) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM CTE_date
  WHERE DATEADD(dd, 1, [date]) <= dateadd(year, 1, dbo.getdate())
)
SELECT [date] FROM CTE_date
OPTION (MAXRECURSION 0);

[StringBuilder]

Envoyé par EmmanuelleC

heu... je ne comprend plus

Code :

LEFT OUTER JOIN matable t2 ON getdate() BETWEEN t2.du AND t2.au

Je viens de corriger effectivement, j'avais oublié de mettre des référence à la vue "calendar"

[EmmanuelleC]

D'accord, ouf, je suis moins larguée que ce que je ne craignais

Un grand merci pour ton aide, c'est effectivement ce dont j'avais besoin