Discussion :

[ultrakas80]

Bonjour à tous,

Je me casse la tête sur une requête avec un count qui doit compter le nombre de typologie par client en affichant les lignes à 0.

J'ai une table de client (Actif) et une table typologie (dtr_PlanClassementDetails).

J'arrive à afficher la somme par typologie (en incluant les lignes avec des 0)

Code :

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

1
2
3
4
SELECT PDC.PLtypedoc AS TYPE_DOCUMENT, COUNT(A.CDSPLDOCID) AS nbdoc
FROM [dwdata].[dbo].[Actif] A
RIGHT JOIN [dtr].[dbo].[dtr_PlanClassementDetails] PDC ON PDC.code=A.CDSPLDOCID 
GROUP BY PDC.PLtypedoc

le résultat obtenu:


Mais lorsque je veux y ajouter le nom du client, j'ai des null dans la colonne NOMCLIENT

Code :

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

1
2
3
4
SELECT A.CX_BAIL AS NOMCLIENT,PDC.PLtypedoc AS TYPE_DOCUMENT, COUNT(A.CDSPLDOCID) AS nbdoc
FROM [dwdata].[dbo].[Actif] A
RIGHT JOIN [dtr].[dbo].[dtr_PlanClassementDetails] PDC ON PDC.code=A.CDSPLDOCID 
GROUP BY PDC.PLtypedoc,A.CX_BAIL

le résultat obtenu:



J'ai essayé de retourner la requête dans tout les sens mais sans succès?

Quelqu'un pour m'aiguiller sur la solution?

[al1_24]

J'ai essayé de retourner la requête dans tout les sens mais sans succès

As-tu essayé avec LEFT JOIN plutôt que RIGHT JOIN ?

[ultrakas80]

Yep avec tous les join possible

[escartefigue]

Pouvez-vous svp communiquer le DDL de création des deux tables (ordres CREATE TABLE complets)

[al1_24]

Tu veux bien toutes les lignes de Actif et celles de dtr_PlanClassementDetails qui peuvent être jointes à Actif ?
C'est donc bien FROM Actif LEFT JOIN dtr_PlanClassementDetails ON... qu'il faudrait faire.

...

A moins que la requête que tu exécutes réellement ne soit pas tout à fait celle que tu nous présentes

[escartefigue]

Il est très probable que le comptage soit faux aussi

Tout comme al1__24 je suppose que le sens de la jointure est erroné, mais pour s'en assurer, il faut le script des tables (dans laquelle des deux tables se trouve la FK pointant sur l'autre table ?)

[Waldar]

Essayez ainsi :

Code :

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

1
2
3
4
5
6
    SELECT max(A.CX_BAIL)      AS NOMCLIENT
         , PDC.PLtypedoc       AS TYPE_DOCUMENT
         , COUNT(A.CDSPLDOCID) AS nbdoc
      FROM [dwdata].[dbo].[Actif]                  as A
RIGHT JOIN [dtr].[dbo].[dtr_PlanClassementDetails] as PDC ON PDC.code = A.CDSPLDOCID 
  GROUP BY PDC.PLtypedocL

[ultrakas80]

Bonjour à tous,

Merci pour vos retours, j'ai essayé de modifier le sens de la requête et d'utiliser le max mais sans succès

Voici le script pour créer les 2 tables avec des données:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
create table dtr_PlanClassementDetails(
code int not null,
pltypedoc varchar(255)
);
insert into dtr_PlanClassementDetails(code,pltypedoc)
values ('1','Contrat'),('2','Commande'),('3','Devis'),('4','Facture');
 
 
create table Actif(
cx_bail varchar(255),
CDSPLDOCID int,
);
insert into Actif(cx_bail,CDSPLDOCID)
values ('ABC','1'),('ABC','1'),('ABC','1'),('DEF','1'),('DEF','2'),('GHI','3'),('GHI','4'),('GHI','3'),('GHI','4');

il faut faire une jointure entre dtr_PlanClassementDetails.Code et Actif.CDSPLDOCID.

Et voici le résultat que je souhaite obtenir:

[Waldar]

Merci c'est plus clair avec les données.

Essayez ainsi :

Code :

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

1
2
3
4
5
6
7
    SELECT A.cx_bail      AS NOMCLIENT
         , PDC.pltypedoc       AS TYPE_DOCUMENT
         , COUNT(case when PDC.code = A.CDSPLDOCID then A.CDSPLDOCID end) AS nbdoc
      FROM Actif                     as A
CROSS JOIN dtr_PlanClassementDetails as PDC
  GROUP BY A.cx_bail, PDC.pltypedoc, PDC.code
  ORDER BY A.cx_bail, PDC.code;

[ultrakas80]

C'est parfait merci bcp !!!

[al1_24]

En moins efficace mais plus compréhensible par un néophyte :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select  act.cx_bail
    ,   dtr.pltypedoc
    ,   count(*)        as  nombre
from    Actif   as act
    inner join
        dtr_PlanClassementDetails   as dtr
        on  dtr.CDSPLDOCID  = act.code
group by act.cx_bail
    ,   dtr.pltypedoc
union all
select  distinct
        act.cx_bail
    ,   dtr.pltypedoc
    ,   0               as nombre
from    Actif   as act
    cross join
        dtr_PlanClassementDetails   as dtr
where   not exists
        (   select  0
            from    Actif   as  xcl
            where   xcl.cx_bail     = act.cx_bail
                and xcl.CDSPLDOCID  = dtr.code
        )
;

Mais, sans exemple, il était vraiment difficile de comprendre ton besoin.

[Waldar]

On peut faire un peu plus efficace si vous avez du volume (le produit cartésien génèrera beaucoup moins de lignes ici) :

Code :

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

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
with cte_actif_agg (NOMCLIENT, CODE, NBDOC) as
(
  select cx_bail
       , CDSPLDOCI
       , count(CDSPLDOCID)
    from Actif
group by cx_bail
       , CDSPLDOCI
)
  ,  cte_actif_client_dst (NOMCLIENT) as
(
select distinct NOMCLIENT
  from cte_actif_agg
)
  ,  cte_matrice (NOMCLIENT, CODE, TYPE_DOCUMENT) as
(
    select C.NOMCLIENT, P.code, P.pltypedoc
      from cte_actif_client_dst      as C
cross join dtr_PlanClassementDetails as P
)
    select M.NOMCLIENT
         , M.TYPE_DOCUMENT
         , coalesce(A.NBDOC, 0) as 
      from cte_matrice   as M
 left join cte_actif_agg as A  on A.NOMCLIENT = M.NOMCLIENT
                              and A.CODE      = M.CODE
  order by M.NOMCLIENT
         , M.CODE;

[ultrakas80]

Re bonjour,

J'ai réussi à finaliser ma requête qui fonctionne bien, j'ai le résultat attendu. Merci à vous.
Cependant à cause de la volumétrie, elle met 2/3 minutes à s'exécuter.

Auriez-vous des pistes pour améliorer le temps de réponse ? (modification de la requête, ajout de CPU/RAM sur le serveur BDD, etc...)
J'ai déjà ajouté des index sur chaque champ concerné, j'ai gagné un peu de temps mais hélas pas suffisamment. Je suis sur SQL Server.

Ci-dessous la requête:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT BIM.BIMNUM + ' - ' + BIM.BIMINTITULE as IMMEUBLE,DOS.DOSNUM + ' - ' + DOS.DOSNOM as NOMCLIENT,PDC.PLchapter AS CHAPITRE, PDC.PLsubChapter AS SOUS_CHAPITRE, PDC.pltypedoc AS TYPE_DOCUMENT, COUNT(case when PDC.code = A.CDSPLDOCID then A.CDSPLDOCID end) AS nbdoc
FROM [dwref].[dbo].[KSP_DOSSIER] DOS
INNER JOIN [dwref].[dbo].[KSP_DOSSIERLOCATIF] DOSL ON DOSL.DOSID=DOS.DOSID
INNER JOIN [dwref].[dbo].[KSP_BIENIMMOBILIER] BIM ON BIM.BIMID=DOSL.BIMID
INNER JOIN [dwref].[dbo].[KSP_DOSPHASE] DOSP ON DOSP.DOSID=DOS.DOSID																	
CROSS JOIN dtr.dbo.dtr_PlanClassementDetails as PDC
LEFT JOIN dwdata.dbo.Actif as A on LEFT(CX_BAIL,8)=DOS.DOSNUM									 
WHERE BIM.BIMNUM='663' and PDC._idclass=4 and left(PDC.PLtypedoc,1) not in ('A','B','C','D') AND (left(PDC.PLtypedoc,1)='8' or left(PDC.PLtypedoc,6)='2.8.01') AND DOSP.PHACODE='ES' AND (DOSP.DPHDTFIN IS NULL OR DOSP.DPHDTFIN>GETDATE())
GROUP BY BIM.BIMNUM + ' - ' + BIM.BIMINTITULE,DOS.DOSNUM + ' - ' + DOS.DOSNOM,PDC.pltypedoc, PDC.code,PDC.PLchapter ,PDC.PLsubChapter 
ORDER BY DOS.DOSNUM + ' - ' + DOS.DOSNOM, PDC.PLchapter ,PDC.PLsubChapter,PDC.pltypedoc

[Waldar]

Ahlala, si seulement quelqu'un vous avait proposé une solution optimisée, vous auriez moins de problème.
Indice : relisez le message précédant le votre.

[ultrakas80]

hihi j'avais bien lu votre réponse mais que vouliez vous dire par "génèrera beaucoup moins de lignes" ?
Car ma requête actuelle me renvoie exactement le bon nombre de lignes. Donc je pensais que me renvoyer moins de ligne fausserait le résultat...

[Waldar]

Je parlais des lignes intermédiaires nécessaires à la création du résultat.

La première requête que je vous ai proposée fait un bête produit cartésien entre vos deux tables.
Si vous avez 100.000 lignes dans la première et 4 lignes dans la seconde, SQL-Server gérera un intermédiaire de 400.00 lignes.

La seconde requête est un peu plus intelligente, elle regroupe tous les actifs par client / code, et ne fait le produit cartésien qu'entre les clients distincts et la seconde table. Si sur vos 100.000 lignes vous avez 10.000 clients distincts, l'intermédiaire n'est plus que de 40.000 lignes.

[ultrakas80]

Effectivement vu sous cet angle...
Je vais adapter la seconde requête et voir ce que ça donne. Merci !!