Discussion :

[teuhinui]

Bonjour à tous,

Voici mon problème : j'ai dans une table toutes les facturations qui ont été réalisées pour nos clients depuis 4 ans.
Un client peut être un client depuis longtemps sur une activité mais être un nouveau client sur une autre activité. Je dois alors le considérer comme nouveau client sur cette activité pendant 1 an et suivre son CA.
De plus, un client est considéré comme nouveau si il n'a pas été facturé pendant 6 mois sur une activité et que nous le facturons à nouveau. Je dois alors le suivre également pendant 1 an...

EX :
Client Activité Date de facture
CLIENT A ACTIVITE 1 01/01/2015
CLIENT B ACTIVITE 1 18/01/2015
CLIENT A ACTIVITE 1 12/02/2015
CLIENT A ACTIVITE 2 02/03/2015
CLIENT B ACTIVITE 1 12/10/2015


=> Le client A est nouveau en mars sur l'activité 2
=> Le client B est nouveau en octobre sur l'activité 1


L'idée est donc de repérer quand un client est nouveau et de le flaguer comme nouveau dans une table pour une période de 1 an sur une activité pour prendre en compte son CA sur la période étudiée.
J'ai commencé par calculer le nombre de factures réalisées par client, par mois et par activité en triant par client, activité, année, mois. Je repère également dans cette requête la date minimum et la date maximum de la facturation dans le mois pour le client.
L'idée est de faire la différence entre la date max de facturation et la date minimum de facturation de la ligne suivante.. mais je suis un peu bloquée...

Avez vous une idée?

D'avance merci pour votre aide! Je tourne en rond....

[julien94320]

Bonjour,

Personellement j'utiliserai du CTE en comptant le nombre d'occurence par type de compte avec un code similaire a celui ci :

Code :

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

1
2
3
4
5
6
7
8
9
WITH TESTClient(IDClient, Activite, date,numero) AS
(
SELECT IdClient, Activite, date,ROW_NUMBER() OVER(PARTITION BY idClient ORDER BY activite,date DESC) AS numero
FROM MATABLE 
 
)
SELECT IDClient, Activite, date  
FROM TESTClient
WHERE numero = 1

Voila un peu d'aide :

https://msdn.microsoft.com/fr-fr/lib...=sql.120).aspx
https://msdn.microsoft.com/fr-fr/lib...=sql.120).aspx
http://sqlpro.developpez.com/cours/s...te-recursives/

Bon courage

[escartefigue]

Ca ne va pas cette solution, car il manque la détection de facturation ou non dans les 6 mois qui précèdent la facture en cours, afin de déterminer si le client doit être considéré comme nouveau

Pour chaque facture, il faut donc calculer la date de facture moins 6 mois (avec un dateadd) et faire un test d'existence d'une facture pour le même client et la même activité dans la période comprise entre la date de facture et la date calculée
Point de détail manquant dans l'expression de besoin : comment calcule-t-on les 6 mois : en mois calendaires ? en appliquant 6*30 jours à partir de la date de facturation...

[julien94320]

Tout dépend si la periode d'activite est bien défini, on sait dans quel periode on est donc la solution proposé fonctionnera ...

Après je suis d'accord avec toi il manque pleins d'infos, j'ai essayé de fournir un exemple de solution pour aider dans la reflexion

A+

[escartefigue]

Voici une solution :

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
WITH CTE AS (                                      
   SELECT Client, 
          Activite, 
          DateFac,         
          DATEADD(mm, -6, DateFac) AS DMoin6         
   FROM   TabFac
            )                                      
SELECT Client, Activite, DateFac, DMoin6             
      ,CASE When exists                           
           (SELECT 1 TabFac SUBQ           
            WHERE SUBQ.Client    = CTE.Client       
              AND SUBQ.Activite  = CTE.Activite        
              AND SUBQ.DateFac  <  CTE.DateFac        
              AND SUBQ.DateFac  >= CTE.DMoin6)       
            THEN 'ANCIEN'                          
            ELSE 'NOUVEAU'                         
       END AS CLIENT_TYPE                          
FROM CTE

[teuhinui]

Bonjour à tous,

merci beaucoup pour vos réponses.
De mon coté, voilà ce que j'avais trouvé :

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
29
30
31
32
SELECT 
	R1.INVOICEACCOUNT, 
	R1.SEGMENT, 
	R1.NB_YEAR, 
	R1.NB_MONTH, 
	R1.NB_FACTURES, 
	R1.MAX_DATE as DERNIERE_DATE,
	R2.MIN_DATE as DEBUT_ANALYSE, 
	DATEDIFF(month,R1.MAX_DATE,R2.MIN_DATE) as NB_MOIS_DIFF,
	DATEADD(month,12,R2.MIN_DATE) as FIN_ANALYSE,
	'NOUVEL FACTURATION' as TYPE_CLIENT
FROM (
		SELECT ROW_NUMBER() OVER(PARTITION BY INVOICEACCOUNT, dimension7_ ORDER BY year(invoicedate), month(invoicedate)) AS POSITION, 
		INVOICEACCOUNT, dimension7_ AS SEGMENT, year(invoicedate) AS NB_YEAR, month(invoicedate) as NB_MONTH, count(*) AS NB_FACTURES, min(invoicedate) AS MIN_DATE, max(invoicedate) AS MAX_DATE 
		FROM CUSTINVOICEJOUR
		WHERE 
			Dimension7_ <> ''
			and Dimension7_ <> 'EMPTY'
		GROUP BY INVOICEACCOUNT, dimension7_, year(invoicedate), month(invoicedate)) as R1
 
CROSS JOIN 
			(
			SELECT ROW_NUMBER() OVER(PARTITION BY INVOICEACCOUNT, dimension7_ ORDER BY year(invoicedate), month(invoicedate)) AS POSITION, 
			INVOICEACCOUNT, dimension7_ AS SEGMENT, year(invoicedate) AS NB_YEAR, month(invoicedate) AS NB_MONTH, count(*) AS NB_FACTURES, min(invoicedate) as MIN_DATE, max(invoicedate) as MAX_DATE 
			FROM CUSTINVOICEJOUR
			where 
				Dimension7_ <> ''
				and Dimension7_ <> 'EMPTY'
			group by INVOICEACCOUNT, dimension7_, year(invoicedate), month(invoicedate)) as R2 
 
where  R1.POSITION >1 and R1.INVOICEACCOUNT = R2.INVOICEACCOUNT and R1.SEGMENT = R2.SEGMENT and R2.POSITION = R1.POSITION +1
and DATEDIFF(month,R1.MAX_DATE,R2.MIN_DATE) >= 6

Ça me donne le résultat que je souhaite. Je vais regarder si vos requêtes sont plus simples. J'ai créé une vue à partir de la mienne et les ORDER BY sont très couteux...

Merci à tous pour votre aide!

[teuhinui]

Gloups, pas indenté c'est imbuvable ...

[teuhinui]

Bonjour,

par contre temps de réponse beaucoup trop long.... j'ai essayé en créant la table temporaire CTE comme indiqué mais ce ne me donne pas le résultat que j'attends.

Voici mon plan d’exécution de la requête : quelqu'un sait comment réduire le coût du tri dans ROW_NUMBER ? Merci!!



Merci à vous

[teuhinui]

Finalement je m'en suis sortie avec ma requête.

Merci pour votre aide

PS :
Les 6 mois se calculent à partir de la date de facturation - 6 mois.
Le client doit être considéré comme nouveau pendant 1 an