Bonjour,

Tout d'abord (et vous allez vous en rendre compte :D) je suis débutant en SQL mais j'ai quand même quelques notions de BDD d'il y a 20 ans et je suis rouillé.

J'ai une table de ce type :

Qui, en gros, me donne les initiales de mes chargés d'affaires ainsi que l'état de leurs devis (0 pour en prépa et 2 pour accepté)

Ce que je veux : faire du reporting pour voir à tout moment le % d'acceptation de leurs devis.

J'ai bien les requêtes pour afficher le décompte des devis par état (accepté, en prépa, totaux) :

SELECT ColleagueId, COUNT(*) AS TotalDevisAccept FROM bdd.dbo.SaleDocument  WHERE DocumentType='1' AND DocumentState='2' AND (DocumentDate BETWEEN '01-06-2021' AND '30-06-2021 23:59:59.993') GROUP BY ColleagueId
SELECT ColleagueId, COUNT(*) AS TotalDevisPrepa FROM bdd.dbo.SaleDocument  WHERE DocumentType='1' AND DocumentState='0' AND (DocumentDate BETWEEN '01-06-2021' AND '30-06-2021 23:59:59.993') GROUP BY ColleagueId
SELECT ColleagueId, COUNT(*) AS TotalDevisAll FROM bdd.dbo.SaleDocument  WHERE DocumentType='1' AND (DocumentDate BETWEEN '01-06-2021' AND '30-06-2021 23:59:59.993') GROUP BY ColleagueId

Mais je suis incapable de coller ça dans la même requête ! (mon but serait ensuite de traiter ça dans une page php pour calculer les taux et pourcentages  en fonction des dates etc etc)

J'espère que vous voyez ou je veux en venir et que je vais comprendre 

Merci

ILTG

-
Edité par Iluvthisgame 6 juillet 2021 à 17:36:55

Bonjour,

Tu cherches à faire un tableau croisé (pivot en anglais).

Ce n'est pas à proprement parlé "prévu" par SQL, même si la plupart des SGBD le propose maintenant ...

Dans ton cas, il y a plusieurs façons de voir les choses en SQL "basique".

La première c'est de gérer le résultat souhaité à l'affichage, en partant d'un tableau droit :

SELECT
	ColleagueId,
	DocumentState,
	COUNT(*) AS Total
FROM bdd.dbo.SaleDocument
WHERE
	DocumentType='1'
	AND DocumentDate BETWEEN '01-06-2021' AND '30-06-2021 23:59:59.993'
GROUP BY
	ColleagueId,
	DocumentState
ORDER BY
	ColleagueId,
	DocumentState

Tu récupères tes lignes par chargé d'affaires et par état, et tu gère l'affichage dans ton programme / ton application.

Autre solution, les sous-requêtes :

SELECT
	CA.ColleagueId,
	COALESCE( D0.Total, 0 ) AS TotalPrepa,
	COALESCE( D2.Total, 0 ) AS TotalAccept,
	COALESCE( D.Total, 0 ) AS Total
FROM
	(
		-- Liste des employés possibles
		SELECT DISTINCT ColleagueId
		FROM bdd.dbo.SaleDocument
		WHERE
			DocumentType = '1'
			AND DocumentDate BETWEEN '01-06-2021' AND '30-06-2021 23:59:59.993'
	) CA
	
		LEFT JOIN (
				-- Nombre de devis prépa
				SELECT
					ColleagueId,
					COUNT(*) AS Total
				FROM bdd.dbo.SaleDocument
				WHERE
					DocumentType = '1'
					AND DocumentState = '0'
					AND DocumentDate BETWEEN '01-06-2021' AND '30-06-2021 23:59:59.993'
		
			) D0
			ON CA.ColleagueId = D0.ColleagueId
	
		LEFT JOIN (
				-- Nombre de devis accept
				SELECT
					ColleagueId,
					COUNT(*) AS Total
				FROM bdd.dbo.SaleDocument
				WHERE
					DocumentType = '1'
					AND DocumentState = '2'
					AND DocumentDate BETWEEN '01-06-2021' AND '30-06-2021 23:59:59.993'
		
			) D2
			ON CA.ColleagueId = D0.ColleagueId
	
		LEFT JOIN (
				-- Nombre de devis accept
				SELECT
					ColleagueId,
					COUNT(*) AS Total
				FROM bdd.dbo.SaleDocument
				WHERE
					DocumentType = '1'
					AND DocumentDate BETWEEN '01-06-2021' AND '30-06-2021 23:59:59.993'
		
			) D
			ON CA.ColleagueId = D.ColleagueId

Tu vois le bordel ...

Si vraiment ces deux solutions ne te vont pas, il faudra utiliser les PIVOT SQL. Quel SGBD utilises-tu ?

Salut,

Ma Bdd est sous Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1

Pour la 1ere solution, merci j'avoue que j'avais pas pensé derrière ça doit être gérable en effet

Pour les sous-requêtes en effet c'est le bronx J'ai étudié le code (car la syntaxe je ne fais que l'apprendre :D) et en effet ça marche

Ma base est "buguée" dans le sens ou apparemment tous les devis ne sont pas forcément saisis avec un ColleagueId ... Du coup j'ai ajouté une condition pour virer les NULL, modifié un peu le code (coquille pour le CA.ColleagueId = D2.ColleagueId, et les GROUP BY ColleagueID) :

SELECT
    CA.ColleagueId,
    COALESCE( D0.Total, 0 ) AS TotalPrepa,
    COALESCE( D2.Total, 0 ) AS TotalAccept,
    COALESCE( D.Total, 0 ) AS Total
FROM
    (
        -- Liste des employés possibles
        SELECT DISTINCT ColleagueId
        FROM bdd.dbo.SaleDocument
        WHERE
            DocumentType = '1'
            AND DocumentDate BETWEEN '01-06-2021' AND '30-06-2021 23:59:59.993'
			AND ColleagueId IS NOT NULL
    ) CA
     
        LEFT JOIN (
                -- Nombre de devis prépa
                SELECT
                    ColleagueId,
                    COUNT(*) AS Total
                FROM bdd.dbo.SaleDocument
                WHERE
                    DocumentType = '1'
                    AND DocumentState = '0'
                    AND DocumentDate BETWEEN '01-06-2021' AND '30-06-2021 23:59:59.993'
					AND ColleagueId IS NOT NULL
				GROUP BY ColleagueId
         
            ) D0
            ON CA.ColleagueId = D0.ColleagueId
     
        LEFT JOIN (
                -- Nombre de devis accept
                SELECT
                    ColleagueId,
                    COUNT(*) AS Total
                FROM bdd.dbo.SaleDocument
                WHERE
                    DocumentType = '1'
                    AND DocumentState = '2'
                    AND DocumentDate BETWEEN '01-06-2021' AND '30-06-2021 23:59:59.993'
		    AND ColleagueId IS NOT NULL
				GROUP BY ColleagueId
         
            ) D2
            ON CA.ColleagueId = D2.ColleagueId
     
        LEFT JOIN (
                -- Nombre de devis accept
                SELECT
                    ColleagueId,
                    COUNT(*) AS Total
                FROM bdd.dbo.SaleDocument
                WHERE
                    DocumentType = '1'
                    AND DocumentDate BETWEEN '01-06-2021' AND '30-06-2021 23:59:59.993'
					AND ColleagueId IS NOT NULL
				GROUP BY ColleagueId
         
            ) D
            ON CA.ColleagueId = D.ColleagueId

Ce qui donne bien ce que j'attendais :

Merci du coup de main

Bon après les sous-requêtes c'est vrai que c'est l'usine à gaz

ILTG

Au lieu de partir de la première sous-requête tu pourrais utiliser la table listant les ColleagueId, une table Colleague peut-être ?

Je vais regarder ça je n'ai pas exploré entièrement la BDD mais en effet elle doit exister

-
Edité par Iluvthisgame 7 juillet 2021 à 10:01:14

Salut,

Il y avait bien une table Colleague, j'ai donc fait la requete dessus en jointant les ID

Merci encore je passe en résolu

Me reste maintenant à comprendre comment injecter les données des datepickers dans la requête SQL et j'aurais un truc paramétrable à souhait

ILTG

Iluvthisgame a écrit:

comprendre comment injecter les données des datepickers dans la requête SQL

Je te conseille de poster dans le forum du langage que tu utilises pour ton application, et de bien regarder la notion de requête préparée.

-
Edité par Benzouye 8 juillet 2021 à 14:45:26

Yes j'ai regardé les requêtes préparée je vais en effet convertir celle-ci. Le reste je vais en effet poster ça dans Javascript car c'est ce que j'utilise principalement pour les 2 composants les plus importants de ma future webappli.

Merci encore