FuJpCjrW

1USE GBC
2GO
3
4/* Check le nb d'acct_id pas lié à un abonnement pour la salle 1 avec les abo de l'année passée et courante 
5************************************************************************************************************/
6/*
7SELECT S1.acct_id as 'S1', AAP.acct_id as 'AAP', AAC.acct_id as 'AAC' 
8FROM dbo.salle1 S1
9LEFT JOIN dbo.abo_annee_passee AAP ON AAP.acct_id = S1.acct_id
10LEFT JOIN dbo.abo_annee_courante AAC ON AAC.acct_id = S1.acct_id
11WHERE AAP.acct_id IS NULL AND AAC.acct_id IS NULL
12*/
13GO
14
15
16
17/***********************************************
18*  Création de la vue pour la table acct_id    *
19***********************************************/
20IF EXISTS (SELECT * FROM sys.views WHERE name = 'vw_acctID')
21
22DROP VIEW vw_acctID
23GO
24
25
26CREATE VIEW vw_acctID
27AS
28SELECT S1.acct_id as 'S1', S2.acct_id as 'S2', AAP.acct_id as 'AAP', AAC.acct_id as 'AAC', PF.[# client] as 'PF', PA.[# client] as 'PA' 
29FROM dbo.salle1 S1
30FULL JOIN dbo.salle2				S2	ON S2.acct_id	= S1.acct_id
31FULL JOIN dbo.abo_annee_passee		AAP ON AAP.acct_id	= S1.acct_id
32FULL JOIN dbo.abo_annee_courante	AAC ON AAC.acct_id	= S1.acct_id
33FULL JOIN dbo.publipostage_francais PF	ON PF.[# client]= S1.acct_id
34FULL JOIN dbo.publipostage_anglais	PA	ON PA.[# client]= S1.acct_id
35
36GO
37/***************************************************************/
38
39
40
41/*******************************************************************************
42*   Création de la table acct_id regroupant les id clients et leur provenance  *
43*******************************************************************************/
44IF EXISTS (SELECT * FROM sys.tables WHERE name = 'acct_id')
45
46
47DROP TABLE dbo.acct_id
48GO
49
50CREATE TABLE dbo.acct_id
51	(
52		acct_id				INT NOT NULL
53		, isInAboCourant	INT NOT NULL
54		, isInAboPasse		INT NOT NULL
55		, isInPubliAng		INT NOT NULL
56		, isInPubliFr		INT NOT NULL
57		, isInSalle1		INT NOT NULL
58		, isInSalle2		INT NOT NULL
59	);
60GO
61
62/* Essayer de faire en sorte que même si le acct_id est null on sache d'où ça vient */
63INSERT INTO dbo.acct_id
64
65SELECT	CASE	WHEN (v.S1 IS NOT NULL AND v.S1 <> -1)		THEN v.S1
66				WHEN (v.S2 IS NOT NULL AND v.S2 <> -1)		THEN v.S2
67				WHEN (v.AAC IS NOT NULL AND v.AAC <> -1)	THEN v.AAC
68				WHEN (v.AAP IS NOT NULL AND v.AAP <> -1)	THEN v.AAP
69				WHEN (v.PF IS NOT NULL AND v.PF <> -1)		THEN v.PF
70				WHEN (v.PA IS NOT NULL AND v.PA <> -1)		THEN v.PA
71				ELSE 0
72		END,
73		CASE	WHEN (v.AAC IS NOT NULL) THEN 1
74				ELSE 0
75		END,
76		CASE	WHEN (v.AAP IS NOT NULL) THEN 1
77				ELSE 0
78		END,
79		CASE	WHEN (v.PA IS NOT NULL) THEN 1
80				ELSE 0
81		END,
82		CASE	WHEN (v.PF IS NOT NULL) THEN 1
83				ELSE 0
84		END,
85		CASE	WHEN (v.S1 IS NOT NULL) THEN 1
86				ELSE 0
87		END,
88		CASE	WHEN (v.S2 IS NOT NULL) THEN 1
89				ELSE 0
90		END
91FROM vw_acctID v
92GO
93
94DELETE FROM acct_id		/* Suppression des rows où l'acct_id est inconnu (aurait pu être fait avant) */
95WHERE acct_id = 0
96
97/**********************************************************/