Cumuls sur période [Résolu]

[darKStein]

Good evening,

Toujours ma petite table comportementale de mes e-mails :

Code :

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
33
34
 
CREATE	TABLE STAT_EMAIL
(
	REPORTID	INT,
	EMAILCLIENT	VARCHAR(255),
	[TYPE]		VARCHAR(5),
	THEMENAME	VARCHAR(255),
	DATEEVT		SMALLDATETIME
)
 
INSERT	STAT_EMAIL
SELECT	1, 'toto@free.fr', 'Send', '', '2010/08/01 10:00:00'
UNION
SELECT	1, 'toto@free.fr', 'Open', '', '2010/08/02 10:00:00'
UNION
SELECT	1, 'toto@free.fr', 'Click', 'lien_1', '2010/08/02 11:00:00'
UNION
SELECT	1, 'toto@free.fr', 'Click', 'lien_2', '2010/08/02 11:03:00'
UNION
SELECT	1, 'titi@free.fr', 'Send', '', '2010/08/01 10:00:00'
UNION
SELECT	1, 'titi@free.fr', 'Open', '', '2010/08/02 10:00:00'
UNION
SELECT	1, 'tutu@free.fr', 'Send', '', '2010/08/01 10:00:00'
UNION
SELECT	1, 'tutu@free.fr', 'Open', '', '2010/08/02 10:00:00'
UNION
SELECT	1, 'tutu@free.fr', 'Click', 'lien_1', '2010/08/02 11:00:00'
UNION
SELECT	2, 'titi@free.fr', 'Send', '', '2011/05/01 10:00:00'
UNION
SELECT	2, 'titi@free.fr', 'Open', '', '2011/05/02 10:00:00'
UNION
SELECT	2, 'titi@free.fr', 'Click', 'lien_1', '2011/05/02 11:00:00'

Je souhaite obtenir des statistiques sur le nombre d'envois, ouvertures et clic par client sur les 6, 12 et 24 derniers mois. J'ai effectué la requête suivante :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
DECLARE	@dd_jour	SMALLDATETIME
 
SET	@dd_jour = GETDATE()
 
SELECT	EMAILCLIENT, 
	SUM(CASE WHEN [TYPE] = 'Send' AND DD_EVT <= DATEADD(month, -6, @dd_jour) THEN 1 ELSE 0 END) AS QI_SEND_6,
		SUM(CASE WHEN [TYPE] = 'Send' AND DATEEVT <= DATEADD(month, -12, @dd_jour) THEN 1 ELSE 0 END) AS QI_SEND_12,
		SUM(CASE WHEN [TYPE] = 'Send' AND DATEEVT <= DATEADD(month, -24, @dd_jour) THEN 1 ELSE 0 END) AS QI_SEND_24,
		SUM(CASE WHEN [TYPE] = 'Open' AND DATEEVT <= DATEADD(month, -6, @dd_jour) THEN 1 ELSE 0 END) AS QI_OPEN_6,
		SUM(CASE WHEN [TYPE] = 'Open' AND DATEEVT <= DATEADD(month, -12, @dd_jour) THEN 1 ELSE 0 END) AS QI_OPEN_12,
		SUM(CASE WHEN [TYPE] = 'Open' AND DATEEVT <= DATEADD(month, -24, @dd_jour) THEN 1 ELSE 0 END) AS QI_OPEN_24,
		SUM(CASE WHEN [TYPE] = 'Click' AND DATEEVT <= DATEADD(month, -6, @dd_jour) THEN 1 ELSE 0 END) AS QI_CLICK_6,
		SUM(CASE WHEN [TYPE] = 'Click' AND DATEEVT <= DATEADD(month, -12, @dd_jour) THEN 1 ELSE 0 END) AS QI_CLICK_12,
		SUM(CASE WHEN [TYPE] = 'Click' AND DATEEVT <= DATEADD(month, -24, @dd_jour) THEN 1 ELSE 0 END) AS QI_CLICK_24
FROM	STAT_EMAIL (NOLOCK)
WHERE	DATEEVT > DATEADD(month, -24, @dd_jour)
GROUP	BY EMAILCLIENT

Problème : pour l'e-mail REPORTID=1, le client toto@free.fr a cliqué 2 fois pour 1 ouverture -> on aimerait avoir l'info de "a cliqué ou pas", mais pas le nombre de fois où il a cliqué sur l'e-mail.

Comment faire ?

[7gyY9w1ZY6ySRgPeaefZ]

un manière est de remplacer :

Code :

SUM(CASE WHEN [TYPE] = 'Click' AND DATEEVT <= DATEADD(month, -6, @dd_jour) THEN 1 ELSE 0 END) AS QI_CLICK_6,

par ceci :

Code :

MAX(CASE WHEN [TYPE] = 'Click' AND DATEEVT <= DATEADD(month, -6, @dd_jour) THEN 1 ELSE 0 END) AS QI_CLICK_6,

[darKStein]

Avec le MAX, il me faudrait donc faire un premier calcul groupé par EMAILCLIENT, REPORTID, puis sommer par EMAILCLIENT pour avoir le total de messages cliqués ?

[darKStein]

Voici ma requête finale, qui fonctionne :

Code :

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
 
DECLARE	@dd_jour	SMALLDATETIME
 
SET	@dd_jour = GETDATE()
 
SELECT	EMAILCLIENT,
	SUM(CI_SEND_6) AS QI_SEND_6,
	SUM(CI_SEND_12) AS QI_SEND_12,
	SUM(CI_SEND_24) AS QI_SEND_24,
	SUM(CI_OPEN_6) AS QI_OPEN_6,
	SUM(CI_OPEN_12) AS QI_OPEN_12,
	SUM(CI_OPEN_24) AS QI_OPEN_24,
	SUM(CI_CLICK_6) AS QI_CLICK_6,
	SUM(CI_CLICK_12) AS QI_CLICK_12,
	SUM(CI_CLICK_24) AS QI_CLICK_24
FROM	(
		SELECT	REPORTID, EMAILCLIENT, 
			MAX(CASE WHEN [TYPE] = 'Send' AND DD_EVT <= DATEADD(month, -6, @dd_jour) THEN 1 ELSE 0 END) AS CI_SEND_6,
			MAX(CASE WHEN [TYPE] = 'Send' AND DATEEVT <= DATEADD(month, -12, @dd_jour) THEN 1 ELSE 0 END) AS CI_SEND_12,
			MAX(CASE WHEN [TYPE] = 'Send' AND DATEEVT <= DATEADD(month, -24, @dd_jour) THEN 1 ELSE 0 END) AS CI_SEND_24,
			MAX(CASE WHEN [TYPE] = 'Open' AND DATEEVT <= DATEADD(month, -6, @dd_jour) THEN 1 ELSE 0 END) AS CI_OPEN_6,
			MAX(CASE WHEN [TYPE] = 'Open' AND DATEEVT <= DATEADD(month, -12, @dd_jour) THEN 1 ELSE 0 END) AS CI_OPEN_12,
			MAX(CASE WHEN [TYPE] = 'Open' AND DATEEVT <= DATEADD(month, -24, @dd_jour) THEN 1 ELSE 0 END) AS CI_OPEN_24,
			MAX(CASE WHEN [TYPE] = 'Click' AND DATEEVT <= DATEADD(month, -6, @dd_jour) THEN 1 ELSE 0 END) AS CI_CLICK_6,
			MAX(CASE WHEN [TYPE] = 'Click' AND DATEEVT <= DATEADD(month, -12, @dd_jour) THEN 1 ELSE 0 END) AS CI_CLICK_12,
			MAX(CASE WHEN [TYPE] = 'Click' AND DATEEVT <= DATEADD(month, -24, @dd_jour) THEN 1 ELSE 0 END) AS CI_CLICK_24
		FROM	STAT_EMAIL (NOLOCK)
		WHERE	DATEEVT > DATEADD(month, -24, @dd_jour)
		GROUP	BY EMAILCLIENT, REPORTID
		) flags
GROUP	BY EMAILCLIENT

Merci !