Besoin d'aide pour un petit Algorithme

[arnolem]

Bonjour,

J'ai un petit problème de réflexion autour d'une requête.

J'ai une table contenant l'historique des crédits achetés et dépensés par un utilisateur.

1. A chaque achat de crédit, j'indique une date d'expiration et le nombre de crédit du pack
2. A chaque dépense, j'indique le nombre de crédit dans cette même table en négatif.

On a donc quelques choses comme :

Code :

1
2
3
4
5
6
7
+ credit  + expiration +    date    +
|    10   |  01/06/01  |  01/01/01  |
|    -1   |            |  07/01/01  |
|    -1   |            |  09/01/01  |
|    10   |  01/12/01  |  12/01/01  |
|    -2   |            |  16/01/01  |
|    -1   |            |  20/01/01  |

Comment feriez vous pour connaitre le crédit actuel ?

Merci

PS : Vous pouvez me répondre en SQL ou en francais

[tatayo]

Bonjour,
Je dirai que tu as un soucis pour calculer le crédit actuel, car tu ne sais pas si les achats ont été utilisés avant leur fin de validité, donc tu n'as aucun moyen de savoir s'il faut les inclure ou pas dans le calcul.

Tatayo.

[arnolem]

En fait, j'ai d'autres colonnes que je n'avais pas indiqué.
Je viens d'éditer mon poste précédant

[saymoneu]

En fait au bout de 6mois si le type n'a pas utilisé son crédit, il est remis à zero c'est bien ca?

[aieeeuuuuu]

Bonjour

Code sql :

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
 
WITH tmp AS (
SELECT 10 AS credit, '2001-06-01' AS expi, '2001-01-01' AS dte UNION ALL
SELECT -1,NULL,'2001-01-07' UNION ALL
SELECT -10,NULL, '2001-02-09' UNION ALL
SELECT 10,'2001-12-01','2001-01-12' UNION ALL
SELECT -2,NULL,'2001-01-16' UNION ALL
SELECT -1,NULL,'2001-01-20' UNION ALL
SELECT -3, NULL, '2001-07-01' )
, credit AS (
	SELECT credit AS montant, expi, dte
	FROM tmp
	WHERE expi IS NOT NULL
)
, debit AS (
	SELECT credit AS montant, dte
	FROM tmp
	WHERE expi IS NULL
)
SELECT SUM(Solde)
FROM (
	SELECT C.montant + COALESCE(SUM(D.montant),0) AS Solde, C.dte, C.expi
	FROM Credit C
	LEFT OUTER JOIN Debit D
		ON D.dte <= C.expi
		AND D.dte > COALESCE((SELECT MAX(expi) FROM Credit WHERE dte < C.dte), '2000-01-01')
	GROUP BY C.montant, C.dte, C.expi
) S
WHERE S.expi >= '2001-10-01'
OR S.Solde < 0

sous SQL Server 2008

J'explique :
1/ Les 3 CTE :
La première sert à générer le jeu de données. J'ai ajouté une ligne à votre jeu de données, afin d'avoir un debit après l'expiration du premier credit
Les deux autres CTE servent à isoler les debit des crédits. j'ai également modifié le montant d'un débit, mis à 10 et changé sa date pour faire ressortir un autre cas particulier : un débit "à cheval" sur deux crédits...

Bien sur, on peut les remplacer par des pseudo tables dans la requete, voire faire une auto-jointure en modifiant les filtres et ainsi eviter des sous requetes. Mais je pense que c'est plus clair comme ça pour commencer.

2/ La sous requete
Pour chaque crédit, je soustrait les débits qui ont eu lieu entre la date d'expiration du crédit, et la date d'expiration du crédit précédent.
(la aussi, on pourrait faire mieux, avec une jointure supplémentaire, mais c'est pour rester le plus simple possible sur l'algo...)

3/enfin, je fais la somme des crédits qui n'ont pas encore expiré à la date voulue (j'ai mis arbitrairement le '2001-10-01' dans mon exemple, mais si vous voulez le crédit actuel, comparez à la date courante), ou les crédit dont le solde est négatif (heu... vous autorisez les découverts ? :d)
Blague à part, ce dernier point est important, car il permet de prendre en compte les points débités "à tort" par la sous requête sur des crédits "épuisés", lorsqu'un nouveau crédit à eu lieu avant la date d'expiration du débit précédent... (mon crédit "à cheval" de tout à l'heure...)

J’espère n'avoir pas "oublié" d'autres cas particuliers, et je vous conseille de tester sur un jeu de données plus important, mais je crois que c'est deja un bon debut...

[arnolem]

Salut,

J'essai de transposer ça à du Mysql mais je ne suis pas sur de retrouver la syntaxe WITH table AS.

Je regarde ça.

En tout cas, un grand merci pour cette nouvelle piste !

[aieeeuuuuu]

La même requête, sans les CTE (WITH...AS)
En un peu moins lisible donc...

Code sql :

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
 
SELECT SUM(Solde)
FROM (
	SELECT C.montant + COALESCE(SUM(D.montant),0) AS Solde, C.dte, C.expi
	FROM (
		SELECT credit AS montant, expi, dte
		FROM MaTable
		WHERE expi IS NOT NULL
	) C
	LEFT OUTER JOIN (
		SELECT credit AS montant, dte
		FROM MaTable
		WHERE expi IS NULL
	) D
		ON D.dte <= C.expi
		AND D.dte > COALESCE((SELECT MAX(expi) FROM (
				SELECT credit AS montant, expi, dte
				FROM MaTable
				WHERE expi IS NOT NULL	
				) Credit 
			WHERE dte < C.dte), '2000-01-01')
	GROUP BY C.montant, C.dte, C.expi
) S
WHERE S.expi >= '2001-10-01'
OR S.Solde < 0

dis nous déjà si ça fonctionne comme tu veux, on pourra optimiser après

[arnolem]

Salut,

J'ai essayé d'adapté avec mes vrais noms de colonnes et j'ai également ajouté un condition sur l'utilisateur.
J'ai également mis NOW() pour avoir la date du jour.
Par contre, le résultat ne va pas. Je devrais avoir un crédit de +47 et je me retrouve avec -1

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
SELECT SUM(Solde)
FROM (
	SELECT C.montant + COALESCE(SUM(D.montant),0) AS Solde, C.dte, C.expi
	FROM (
		SELECT
        credit AS montant,
        dateExpiration AS expi,
        dateExpiration AS dte
		FROM Credit
		WHERE Credit.credit < 0
    AND Credit.idLearner  = 136
	) C
	LEFT OUTER JOIN (
		SELECT
        credit AS montant,
        dateExpiration AS dte
		FROM Credit
		WHERE Credit.credit > 0
    AND Credit.idLearner  = 136
	) D
		ON D.dte <= C.expi
		AND D.dte > COALESCE((SELECT MAX(expi) FROM (
        SELECT
            credit AS montant,
            dateExpiration AS expi,
            dateExpiration AS dte
				FROM Credit
				WHERE Credit.credit < 0	
        AND Credit.idLearner  = 136
				) Credit 
			WHERE dte < C.dte), now())
	GROUP BY C.montant, C.dte, C.expi
) S
WHERE S.expi >= now()
OR S.Solde < 0

[aieeeuuuuu]

a première vue, j'ai l'impression que tu as inversé les clauses de filtrage sur les pseudos tables

pour les crédits, tu cherche

Code sql :

WHERE credit.Credit < 0

Alors que ça devrait être l'inverse...

[arnolem]

Bien vu. Voici mon nouveau 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
30
31
32
33
34
35
SELECT SUM(Solde)
FROM (
	SELECT C.montant + COALESCE(SUM(D.montant),0) AS Solde, C.dte, C.expi
	FROM (
		SELECT
        credit AS montant,
        dateExpiration AS expi,
        dateExpiration AS dte
		FROM Credit
		WHERE Credit.credit > 0
    AND Credit.idLearner  = 136
	) C
	LEFT OUTER JOIN (
		SELECT
        credit AS montant,
        dateExpiration AS dte
		FROM Credit
		WHERE Credit.credit < 0
    AND Credit.idLearner  = 136
	) D
		ON D.dte <= C.expi
		AND D.dte > COALESCE((SELECT MAX(expi) FROM (
        SELECT
            credit AS montant,
            dateExpiration AS expi,
            dateExpiration AS dte
				FROM Credit
				WHERE Credit.credit > 0	
        AND Credit.idLearner  = 136
				) Credit 
			WHERE dte < C.dte), now())
	GROUP BY C.montant, C.dte, C.expi
) S
WHERE S.expi >= now()
OR S.Solde < 0

Par contre, ça me donne 60 pour le jeu de donné suivant au lieu de 43 :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
idLearner;credit;dateEvenement;dateExpiration
136;2;"2010-12-13 09:04:52";"2011-02-18 00:00:00"
136;-1;"2010-12-22 14:22:54";NULL
136;-1;"2011-01-04 14:22:06";NULL
136;60;"2011-01-26 16:41:30";"2011-12-31 00:00:00"
136;-1;"2011-02-02 15:17:03";NULL
136;-1;"2011-02-02 15:19:01";NULL
136;-1;"2011-02-02 15:23:07";NULL
136;-1;"2011-02-02 15:24:23";NULL
136;-1;"2011-02-02 15:25:25";NULL
136;-1;"2011-02-02 15:26:34";NULL
136;-1;"2011-02-02 15:27:16";NULL
136;-1;"2011-02-02 15:27:48";NULL
136;-1;"2011-02-02 15:28:17";NULL
136;-1;"2011-02-02 15:29:02";NULL
136;-1;"2011-02-02 15:35:38";NULL
136;-1;"2011-02-02 15:38:20";NULL
136;-1;"2011-02-02 15:39:03";NULL
136;-1;"2011-02-02 15:42:32";NULL
136;-1;"2011-02-02 15:43:36";NULL
136;-1;"2011-02-04 17:52:21";NULL
136;-1;"2011-02-15 09:57:37";NULL

[aieeeuuuuu]

Bon
première remarque, il y a une petite erreur dans la recherche des bornes pour l'attribution des credit a un debit donné.

1/ Dans ma requete d'origine, (je cherche les credits dont la date se situe entre l'expiration du credit, et la date d'expiration du credit précédent), je fais un COALESCE pour le cas du premier credit (qui n'a donc pas de credit précédent ) et je mettais une date bidon qui doit etre inférieure à toutes tes dates. tu as remplacé cette date en dur par now(), ce qui est une erreur. De toute façon c'est pas très propre, mais pour l'exemple et la logique, ca fonctionne.

2/ cependant, même en corrigeant cette erreur, la requete renvoie toujours 60 avec ton jeu de données.

Par contre j'ai vérifié avec ma requete initiale, elle me renvoi bien 43...

Il y a donc eu une erreur dans la traduction...
Je cherche d'ou ca peut venir

[aieeeuuuuu]

reBon !
Deuxième remarque, vous utilisez toujours dateExpiration dans votre requete, parfois à la place de dateEvenement...

Mais on arrive toujours sur 60

Au passage, ma deuxième requête (sans les CTE) donne également 43... pourtant elles sont presque identiques maintenant...

[aieeeuuuuu]

ceci doit etre mieux...

Code sql :

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
 
SELECT SUM(Solde)
FROM (
	SELECT C.montant + COALESCE(SUM(D.montant),0) AS Solde, C.dte, C.expi
	FROM (
		SELECT
        credit AS montant,
        dateExpiration AS expi,
        dateEvenement AS dte
		FROM Credit
		WHERE Credit.credit > 0
    AND Credit.idLearner  = 136
	) C
	LEFT OUTER JOIN (
		SELECT
        credit AS montant,
        dateEvenement AS dte
		FROM Credit
		WHERE Credit.credit < 0
    AND Credit.idLearner  = 136
	) D
		ON D.dte <= C.expi
		AND D.dte > COALESCE((SELECT MAX(expi) FROM (
        SELECT
            credit AS montant,
            dateExpiration AS expi,
            dateEvenement AS dte
				FROM Credit 
				WHERE credit > 0	
        AND idLearner  = 136
				) Credit
			WHERE dte < C.dte), '2000-01-01')
	GROUP BY C.montant, C.dte, C.expi
) S
WHERE S.expi >= now()
OR S.Solde < 0

[aieeeuuuuu]

Attention toutefois si deux crédits ont lieu à la même date, un seul sera pris en compte.
Vu que vous prenez les secondes en comptes, cela ne devrait pas arriver, mais vous devriez ajouter une contrainte pour vous en assurer...