Jointures complexes de croisements

Bonjour à tous,
je suis en train d'essayer d'alimenter une table me permettant de définir des liaisons entre 2 autres selon une 3°.

Voici un peu de détails fonctionnels :
J'ai une table de conversions une table d'évènements et une table de scenarii.
Le principe est de lier une conversion à 1 évènement selon des scenarii.

Ma table de conversions

Code:

1
2
3
4
5
6
7
8
9
10
CREATE TABLE [dwh].[FtConversion](
	[IdConversion] [int] IDENTITY(1,1) NOT NULL,
	[IdDateConversion] [int] NULL,
	[IdHeureConversion] [int] NULL,
	[IdCampagne] [int] NULL,
	[IdVisiteur] [int] NULL,
 CONSTRAINT [PK_FTCONVERSION] PRIMARY KEY CLUSTERED 
(
	[IdConversion] ASC
) )

FtEvenement

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE [dwh].[FtEvenement](
	[IdEvenement] [int] IDENTITY(1,1) NOT NULL,
	[IdDate] [int] NULL,
	[IdHeure] [int] NULL,
	[IdVisiteur] [int] NULL,
	[IdCampagne] [int] NULL,
	[IdClient] [smallint] NULL,
	[IdTypeAction] [int] NULL,
 CONSTRAINT [PK_FTEVENEMENT] PRIMARY KEY CLUSTERED 
(
	[IdEvenement] ASC
))

et la table des scenarii que j'ai défini pour le moment de la manière suivante :

Code:

1
2
3
4
5
6
7
8
CREATE TABLE [dwh].[DimScenario](
	[IdScenario] [smallint] NOT NULL,
	[NumeroAttrbution] [int] NULL,
	[Ordre] [int] NOT NULL,
	[IdTypeAction] [int] NULL,
	[IdCampagne] [int] NULL,
	[Ponderation] [int] NOT NULL
) ON [PRIMARY]

Un exemple simple de scénario

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
INSERT INTO dwh.DimScenario(IdScenario, NumeroAttrbution, Ordre, IdTypeAction, Ponderation, IdCampagne)
SELECT
	1
    , 1
	, 1
	, 2
	, 100
	, NULL
UNION
SELECT
	1
	, 2
    , 1
	, 1
	, 100
	, NULL
UNION
SELECT
	1
	, 3
    , 1
	, NULL
	, 100
	, 2

Un évènement est de TypeAction 1, 2 ou 3.
Ce qui m'intéresse est d'attribuer à une conversion l'évènement selon le type d'action définie dans le scénario.

Pour le moment je croise mes conversions avec tous les évènements qui lui sont liés et j'essaye ensuite de trouver le bon..

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
35
36
37
38
39
40
41
42
43
WITH EvenementConversion (IdEvenement, IdVisiteur, IdDate, IdHeure) AS (
	SELECT 
		IdEvenement, IdVisiteur, IdDate, IdHeure
	FROM dwh.FtEvenement ft
		JOIN dwh.DimTypeAction ta ON ta.IdTypeAction = ft.IdTypeAction
	WHERE 
		ta.IdTypeAction = 3
	)
select distinct
ft.IdConversion
, ft.IdVisiteur
, ft.IdDateConversion
, ft.IdHeureConversion
, ft.IdCampagne
 
, ev.IdEvenement
-- , ev.IdVisiteur
, ev.IdDate
, ev.IdHeure
, ev.IdTypeAction
, DENSE_RANK () OVER 
	(PARTITION BY ev.IdVisiteur, ev.IdTypeAction 
	order by ev.IdDate DESC, ev.IdHeure DESC)
	AS rang
 
, sce.Ordre
, sce.IdTypeAction
, sce.Ponderation
, sce.NumeroAttrbution
from dwh.FtConversion ft
	JOIN EvenementConversion evt
		ON evt.IdVisiteur = ft.IdVisiteur
		AND evt.IdDate <= ft.IdDateConversion
	JOIN dwh.FtEvenement ev
		ON ev.IdVisiteur = ft.IdVisiteur
		AND ev.IdDate <= ft.IdDateConversion
 
	, dwh.DimScenario sce
WHERE
	sce.IdScenario = 1
	AND sce.NumeroAttrbution in (1)
	order by ft.IdVisiteur, ft.IdConversion, ev.IdDate DESC, ev.IdHeure DESC
			, sce.NumeroAttrbution asc