05 – Construire les requêtes des indicateurs et des graphiques

Objectif de cette étape #

Nous allons maintenant créer des requêtes plus synthétiques.
Contrairement à la requête détaillée, ces requêtes effectuent directement les regroupements nécessaires aux KPI et aux graphiques.

Requête 1 — Chiffre d’affaires total #

Nom #

PARCOURS_02_CA_TOTAL

Utilisation #

Cette requête sera utilisée par le premier KPI.

SQL #

SELECT
    CAST(
        COALESCE(
            SUM(
                cd.Prix
                * cd.Quantite
                * (1 - cd.Remise)
            ),
            0
        )
        AS decimal(18, 2)
    ) AS ChiffreAffaires
FROM Demonstration.dbo.Commandes AS co
INNER JOIN Demonstration.dbo.Clients AS cl
    ON cl.CodeClient = co.CodeClient
INNER JOIN Demonstration.dbo.Commandes_Details AS cd
    ON cd.NumCommande = co.NumCommande
WHERE co.DateCommande IS NOT NULL
  AND (
        :pPays = '#NULL'
        OR cl.Pays = :pPays
      );

Pourquoi utiliser SUM ? #

La fonction SUM additionne le montant net de toutes les lignes de commande.

La requête retourne une seule ligne et une seule colonne :

ChiffreAffaires

C’est exactement le niveau de détail attendu par une tuile présentant une valeur unique.

Requête 2 — Nombre de commandes #

Nom #

PARCOURS_03_NOMBRE_COMMANDES

Utilisation #

Cette requête sera utilisée par le deuxième KPI.

SQL #

SELECT
    COUNT(DISTINCT co.NumCommande) AS NombreCommandes
FROM Demonstration.dbo.Commandes AS co
INNER JOIN Demonstration.dbo.Clients AS cl
    ON cl.CodeClient = co.CodeClient
WHERE co.DateCommande IS NOT NULL
  AND (
        :pPays = '#NULL'
        OR cl.Pays = :pPays
      );

Pourquoi utiliser DISTINCT ? #

Une commande peut posséder plusieurs lignes de produits.

Dans cette requête, nous partons directement de la table Commandes. Cependant, l’utilisation de DISTINCT rend explicite le fait que nous voulons compter les commandes uniques.

La requête retourne :

NombreCommandes

Requête 3 — Chiffre d’affaires mensuel #

Nom #

PARCOURS_04_CA_MENSUEL

Utilisation #

Cette requête sera utilisée par le graphique d’évolution des ventes.

SQL #

SELECT
    DATEADD(
        MONTH,
        DATEDIFF(MONTH, 0, co.DateCommande),
        0
    ) AS Mois,
    CAST(
        SUM(
            cd.Prix
            * cd.Quantite
            * (1 - cd.Remise)
        )
        AS decimal(18, 2)
    ) AS ChiffreAffaires
FROM Demonstration.dbo.Commandes AS co
INNER JOIN Demonstration.dbo.Clients AS cl
    ON cl.CodeClient = co.CodeClient
INNER JOIN Demonstration.dbo.Commandes_Details AS cd
    ON cd.NumCommande = co.NumCommande
WHERE co.DateCommande IS NOT NULL
  AND (
        :pPays = '#NULL'
        OR cl.Pays = :pPays
      )
GROUP BY
    DATEADD(
        MONTH,
        DATEDIFF(MONTH, 0, co.DateCommande),
        0
    )
ORDER BY
    Mois;

Pourquoi transformer la date ? #

Les commandes peuvent être passées à des jours différents.
Nous voulons regrouper toutes les commandes d’un même mois sous une date commune représentant le premier jour du mois.

Exemples :

05/01/1997 → 01/01/1997
18/01/1997 → 01/01/1997
30/01/1997 → 01/01/1997

Le graphique pourra ainsi afficher une valeur par mois et conserver un véritable champ de type date.

Pourquoi utiliser GROUP BY ? #

Le GROUP BY demande à SQL de calculer un total distinct pour chaque mois.

Requête 4 — Chiffre d’affaires par catégorie #

Nom #

PARCOURS_05_CA_CATEGORIE

Utilisation #

Cette requête sera utilisée par le graphique comparant les catégories de produits.

SQL #

SELECT
    cat.NomCategorie AS Categorie,
    CAST(
        SUM(
            cd.Prix
            * cd.Quantite
            * (1 - cd.Remise)
        )
        AS decimal(18, 2)
    ) AS ChiffreAffaires
FROM Demonstration.dbo.Commandes AS co
INNER JOIN Demonstration.dbo.Clients AS cl
    ON cl.CodeClient = co.CodeClient
INNER JOIN Demonstration.dbo.Commandes_Details AS cd
    ON cd.NumCommande = co.NumCommande
INNER JOIN Demonstration.dbo.Produits AS p
    ON p.CodeProduit = cd.CodeProduit
INNER JOIN Demonstration.dbo.Categories AS cat
    ON cat.CodeCategorie = p.NumCategorie
WHERE co.DateCommande IS NOT NULL
  AND (
        :pPays = '#NULL'
        OR cl.Pays = :pPays
      )
GROUP BY
    cat.NomCategorie
ORDER BY
    ChiffreAffaires DESC;

Pourquoi ajouter Produits et Categories ? #

La ligne de commande connaît le code du produit, mais pas directement sa catégorie.

Nous devons suivre le chemin :

Commandes_Details
    → Produits
        → Categories

La requête retourne une ligne par catégorie.

Requête 5 — Liste des pays #

Nom #

PARCOURS_06_LISTE_PAYS

Utilisation #

Cette requête alimentera la liste de sélection permettant de filtrer le Tableau de Bord.

SQL #

SELECT DISTINCT
    cl.Pays
FROM Demonstration.dbo.Clients AS cl
WHERE cl.Pays IS NOT NULL
  AND LTRIM(RTRIM(cl.Pays)) <> ''
ORDER BY
    cl.Pays;

Pourquoi utiliser DISTINCT ? #

Plusieurs clients peuvent être situés dans le même pays.
Nous voulons afficher chaque pays une seule fois dans la liste.

Tests à réaliser #

Résultat attendu
Toutes les données nécessaires aux composants du Tableau de Bord sont maintenant disponibles.

Captures #

Chiffres d’affaires (= #NULL) #

Regroupement mensuel (= #NULL) #

Chiffre d’affaires par catégorie (= #NULL) #