Discussion :

[noureddine1967]

Bonjour,

J'ai une table qui contient les créances de nos clients, il existe deux champs que je voudrais utiliser "date_facture" et "montant_facture".
En exécutant la requête suivante :

Code :

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

1
2
3
4
select substr(date_facturation,7,4) as annee, sum(montant_facture)
from tmp_creances_clients
group by annee
order by annee  desc

j'obtiens le résultat suivant :


annee montant_facture
2022 7654.82
2021 1518.95
2020 18218.39
2019 165744.34
2018 46991.41
2017 76701.13
2016 447148.53
2015 17713.54
2014 17713.54
2000 159766.95
1999 6558.68
1998 1031.14

Ce que je voudrais c'est d'avoir les quatre premières années 2022, 2021, 2020, 2019 à part et de regrouper le reste des montants des années 2018 et antérieur dans l'année 2018.

Merci par avance pour vos réponses

[tatayo]

Bonjour,
Je ne sais pas si SqlLite accepte ce genre d'écriture, à tester:

Code :

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

1
2
3
4
5
 
select (case when substr(date_facturation,7,4) < '2019' then '2018' else substr(date_facturation,7,4) end)  as annee, sum(montant_facture)
from tmp_creances_clients
group by annee
order by annee  desc

Il ne reste plus qu'à ajouter un calcul pour ne pas avoir à spécifier "en dur" l'année de bascule.

Tatayo.

[noureddine1967]

Merci,

Ça marche sur Sqlite3, mais

Il ne reste plus qu'à ajouter un calcul pour ne pas avoir à spécifier "en dur" l'année de bascule.

Je vais essayer de le trouver.

[SergioMaster]

Code :

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

select strftime('%Y',current_date)-4

donc ceci devrait fonctionner

Code :

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

1
2
3
4
select (case when strftime('%Y',date_facturation) < strftime('%Y',current_date)-3 then strftime('%Y',current_date)-4 else substr(date_facturation,7,4) end)  as annee, sum(montant_facture)
from tmp_creances_clients
group by annee
order by annee  desc

[noureddine1967]

Merci infiniment.

[noureddine1967]

Bonjour,

Dans le même contexte j'ai voulu utilisé la requête modifiée, pour regrouper les montants selon les 5 dernières années jusqu'à l'an 2018 :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT (CASE WHEN max(annee) < max(annee) -4
                         THEN max(annee) -5 ELSE max(annee) END) AS annee, 
                         ROUND(SUM(montant_facture), 2) AS montant, 
                         ROUND(SUM(montant_facture)/t.total_general * 100, 2) AS taux 
                         FROM tmp_creances_clients,
                         (SELECT SUM(montant_facture) AS total_general FROM tmp_creances_clients a) AS t
                         GROUP BY annee
                         ORDER BY annee DESC

mais, le résultat affiche l'ensemble des années dans la table comme ceci :



Est ce que j'ai oublier quelque chose ???
Merci

[SergioMaster]

Est ce que j'ai oublié quelque chose ?

Oui, d'utiliser la colonne date_facturation

Code :

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

1
2
3
4
5
6
7
8
*
SELECT (CASE WHEN strftime('%Y',date_facturation) < strftime('%Y',date_facturation) -4
                         THEN strftime('%Y',date_facturation) -5 ELSE strftime('%Y',date_facturation) END) AS annee, 
                         ROUND(SUM(montant_facture), 2) AS montant, 
                         ROUND(SUM(montant_facture)/t.total_general * 100, 2) AS taux 
                         FROM tmp_creances_clients, (SELECT SUM(montant_facture) AS total_general FROM tmp_creances_clients a) AS t
                         GROUP BY annee
                         ORDER BY annee DESC

si SQlite supporte les CTE j'écrirai plutôt

Code :

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

1
2
3
4
5
6
7
8
WITH T AS (SELECT SUM(montant_facture) AS total_general FROM tmp_creances_clients a)
SELECT (CASE WHEN strftime('%Y',c.date_facturation) < strftime('%Y',c.date_facturation) -4
                         THEN strftime('%Y',c.date_facturation) -5 ELSE strftime('%Y',c.date_facturation) END) AS annee, 
                         ROUND(SUM(c.montant_facture), 2) AS montant, 
                         ROUND(SUM(c.montant_facture)/t.total_general * 100, 2) AS taux 
                         FROM tmp_creances_clients c JOIN  t ON 1=1
                         GROUP BY annee
                         ORDER BY annee DESC

[SergioMaster]

Merci infiniment.

il est d'usage de mettre un sur les réponses acceptées

[noureddine1967]

Merci SergioMaster,

Seulement, je voudrais utilisé le champ "annee" au lieu date_facturation et groupé les montants de l'année 2018 et antérieur.

[SergioMaster]

Et alors ? Si je me base sur les premières réponses, je ne vois aucune colonne année dans la table tmp_creances_clients .

Quand vous posez des questions SQL il est bon de fournir la structure de la table pour commencer.

[noureddine1967]

Merci SergioMaster pour votre patience,

En effet, au début j'ai ecrit ma requête en fonction de la date de facturation.
Mais, en raison de la date de facturation du dernier mois de l'année qui s'effectue au premier mois de l'année qui suit. Là, j'ai constaté que mes calculs sont, en quelques sorte, faussé.

A cet effet, j'ai décidé d'extraire "l'année" du champ "num_facture" qui lui correspond exactement à l'année.

Par conséquent, ma view "tmp_creances_clients" contient les champs suivants : code_client, num_facture, date_facturation, montant et annee

Dans le même contexte j'ai voulu utilisé la requête modifiée, pour regrouper les montants selon les 5 dernières années jusqu'à l'an 2018 :

Ainsi, à partir de la dernière année, "MAX(annee)" qui est 2022, je calcul mes soldes : 2021, 2020, 2019 et je cumul le reste avec l'année 2018.
J'espère qui j'ai bien pu expliquer mon problème.

[SergioMaster]

pour votre patience

Je ne vous le fais pas dire !

Mais, en raison de la date de facturation du dernier mois de l'année qui s'effectue au premier mois de l'année qui suit.

Clair comme de la vase ! Mais je ne vais pas épiloguer, tout comme je ne vais pas écrire qu'un script de création de table ainsi qu'un script de remplissage de données test permettrait de faire une réponse contrôlée

voici ma proposition

Code :

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

1
2
3
4
5
6
7
WITH T AS (SELECT SUM(montant_facture AS total_general, max(annee) AN FROM tmp_creances_clients)
SELECT (CASE WHEN T.AN< T.AN -4 THEN TAN-5 ELSE TAN) AS annee, 
                         ROUND(SUM(c.montant_facture), 2) AS montant, 
                         ROUND(SUM(c.montant_facture)/t.total_general * 100, 2) AS taux 
                         FROM tmp_creances_clients c JOIN  t ON 1=1
                         GROUP BY annee
                         ORDER BY annee DESC

[noureddine1967]

Merci encore,

J'ai exécuter votre requête:

Code :

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

1
2
3
4
5
6
7
WITH T AS (SELECT SUM(montant_facture) AS total_general, MAX(annee) AS AN FROM tmp_creances_clients a)
SELECT (CASE WHEN T.AN<T.AN-4 THEN T.AN-5 ELSE T.AN END) AS annee, 
ROUND(SUM(c.montant_facture), 2) AS montant, 
ROUND(SUM(c.montant_facture)/T.total_general * 100, 2) AS taux 
FROM tmp_creances_clients c JOIN T ON 1=1
GROUP BY annee
ORDER BY annee DESC

Le résultat affiche uniquement l'année 2022:

[SergioMaster]

Ah, j'aurais pu le découvrir si j'avais eu un jeu d'essai. Comme j'ai écrit le SQL à la volée, j'ai fait des confusions

Code :

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

1
2
3
4
5
6
7
WITH T AS (SELECT SUM(montant_facture) total_general, MAX(annee) AN FROM tmp_creances_clients a)
SELECT (CASE WHEN c.annee>T.AN-4 THEN c.Annee else  T.an-4 END)  annee, 
ROUND(SUM(c.montant_facture), 2) montant, 
ROUND(SUM(c.montant_facture)/T.total_general * 100, 2)  taux 
FROM tmp_creances_clients c JOIN T ON 1=1
GROUP BY annee
ORDER BY annee DESC

[noureddine1967]

Pour éliminer toute confusion j'ai remplacé annee_r par exercice

Code :

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

1
2
3
4
5
6
7
8
9
 
 
WITH T AS (SELECT SUM(montant_facture) AS total_general, MAX(annee) AS AN FROM tmp_creances_clients a)
SELECT (CASE WHEN T.AN < T.AN-4 THEN T.AN-5 ELSE T.AN END) AS exercice, 
ROUND(SUM(c.montant_facture), 2) AS montant, 
ROUND(SUM(c.montant_facture)/T.total_general * 100, 2) AS taux 
FROM tmp_creances_clients c JOIN T ON 1=1
GROUP BY exercice
ORDER BY exercice DESC

Cette fois le résultat une seule ligne :

[SergioMaster]

mon dernier post est corrigé, et grâce à un jeu d'essai bidon


on ne m'otera pas de la tête qu'avec des fonctions de fenêtrage https://www.sqlite.org/windowfunctions.html il serait possible de faire quelque chose, mais je maitrise mal

[noureddine1967]

Merci SergioMaster,

Mais, j'ai dû remplacer annee par exercice

Code :

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

1
2
3
4
5
6
7
8
 
WITH T AS (SELECT SUM(montant_facture) AS total_general, MAX(annee) AS AN FROM tmp_creances_clients a)
SELECT (CASE WHEN c.annee >T.AN-4 THEN c.annee ELSE T.AN-4 END) AS exercice, 
ROUND(SUM(c.montant_facture), 2) AS montant_an, 
ROUND(SUM(c.montant_facture)/T.total_general * 100, 2) AS taux
FROM tmp_creances_clients c JOIN T ON 1=1
GROUP BY exercice
ORDER BY exercice DESC

[Waldar]

on ne m'otera pas de la tête qu'avec des fonctions de fenêtrage https://www.sqlite.org/windowfunctions.html il serait possible de faire quelque chose, mais je maitrise mal

Comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
with cte_exercice (exercice, montant_facture) as
(
select max(annee, max(annee) over() -4)
     , montant_facture
  from tmp_creances_clients
)
  select exercice
       , round(sum(montant_facture), 2) as montant_an
       , round(100 * sum(montant_facture) / sum(sum(montant_facture)) over(), 2) as taux
    from cte_exercice
group by exercice
order by exercice desc;

[SergioMaster]

d'un autre côté, cela dépend de la version de SQLite

[Waldar]

J'ai essayé sur les versions 3.27.2 et 3.39.1, celles proposées sur db<>fiddle :

• https://dbfiddle.uk/Vc8-ID_6
• https://dbfiddle.uk/DVLvnziE