Discussion :

[masterdash]

Salut tous le monde, je suis coincer sur une requête, j'essaye de trouver les périodes manquantes pour les adhérents qui paye trimestriellement et les mois manquants pour ceux qui paye mensuellement. Chaque Adhérent paye pour ces salaries par trimestre ou par mois tous dépends du nombre des ces effectifs. le but et de trouver la période manquante ou le mois manquant dans tous les payements par adhérent.
Structure de la Table :

NumAdh

NumAdh	Journee	Periode	Effectif	Montant
00258975	10/04/2020	T12020	5	45000.00
00258975	15/07/2020	T22020	5	45000.00
00258975	05/10/2020	T32020	5	45000.00
00258975	03/04/2021	T12021	7	75000.00

Sur le tableau par exemple il manque la Période T42020.
Voila ma requête pour trouver les périodes, mais je n'arrive pas à détecter la ou les période qui manque afin de les affiches, merci pour votre aide.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT Distinct 
			  Case When SubStr(e.Periode, 5, 1) =  ''T'' Then e.Periode Else '''' End Periode_Trim					 
			, Case When SubStr(e.Periode, 5, 1) <> ''T'' Then e.Periode Else '''' End Periode_Mens
		--	, Case When SubStr(e.Periode, 5, 1) =  ''T'' Then e.Effectif Else 0 End Eff_Trim			
		--	, Case When SubStr(e.Periode, 5, 1) <> ''T'' Then e.Effectif Else 0 End Eff_Mens					
			, Case When SubStr(e.Periode, 5, 1) =  ''T'' Then To_Char(t.DebuActivite, ''YYYYMM'') 
				   When SubStr(e.Periode, 5, 1) <> ''T'' Then To_Char(t.DebuActivite, ''YYYYMM'') Else '''' End Date_Debut			
 
	From Adh_Payment e, Adherent t
	Where e.NumAdh = ''00258975'' And t.NumAdh = e.NumAdh
	Order By 1 Desc, 2 Desc

DB : Oracle 11g

[vttman]

Bonjour,
En passant ...
J'aurais créé un table Periode P avec les différentes périodes T12020, T22020,T32020,T42020,T12021 etc.
puis un simple left join en partant de cette table P vers NumAdh avec comme condition Periode (Table NumAdh) à NULL et les adhérents en retard ou autre sortent facilement ...

[masterdash]

Merci pour la réponse, mais je n'ai pas le droit créer aucun table, tous ce fait par l'administrateur, aussi il n'a pas de Période nul, y'a t'il une autre solution. Merci

[tatayo]

Bonjour,
Il faudrait nous donner la description des tables en jeu, un jeu de test et le résultat attendu.

Sinon j'ai une petite question: tu nous dis qu'il manque la période T42020.
Mais d'où sort cette période ? Dans quelle table se trouve t'elle ?
A priori elle n'est pas dans la table adh_Payment, sinon pas de paiement -> pas de période. Et elle n'a rien à faire dans la table Adherent.
Donc à priori il manque au moins une table dans la requête, celle qui permet d'avoir la liste des période.

Tatayo.

[masterdash]

Il n y'a pas de Période dans une autre table, se que je cherche, sait en classent les périodes je doit détecter la ou les quels manque à l'adhérent parmi tous.
Ex : Entre T12020 et T42020 il manque à déclarer T22020 et T32020, j'espère être claire, je fais de mon mieux.

[al1_24]

Si ces valeurs sont absentes de la base de données, comment une requête pourrait-elle en fournir la liste ?

Il faut d'une manière ou d'une autre que la liste complète des périodes soit disponible.

[vanagreg]

Bonjour,

Si je comprends bien il faut construire les périodes manquantes à partir des données existantes. Tu donnes l'exemple de Trimestres avec je pense une codification TnAAAA. Donc on aurait T1, T2, T3 ou T4 suivi de l'année. Si c'est ça je ne comprends pas bien le substr(e.Periode, 5, 1).
Sinon quelle est la codification pour des périodes mensuelles?

[JeitEmgie]

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
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
WITH DATA(numadh, period) AS (
	SELECT '00258975', 'T12020' FROM DUAL
	UNION ALL
	SELECT '00258975', 'T42021' FROM DUAL
	UNION ALL
	SELECT '00258900', 'M012020' FROM DUAL
	UNION ALL
	SELECT '00258900', 'M122021' FROM DUAL
	UNION ALL
	SELECT '00258000', 'M122019' FROM DUAL
	UNION ALL
	SELECT '00258000', 'M122021' FROM DUAL
)
SELECT numadh, type_cotisation, start_gap AS start_gap, end_gap AS end_gap,
	type_cotisation || 
	CASE type_cotisation 
		WHEN 'M' THEN
			LPAD(EXTRACT(MONTH FROM start_gap),2,'0')
		ELSE
			TO_CHAR(TRUNC(EXTRACT(MONTH FROM start_gap)  / 3,0) + 1)
	END || EXTRACT(YEAR FROM start_gap)
	|| ' to ' || type_cotisation || 
	CASE type_cotisation 
		WHEN 'M' THEN
			LPAD(EXTRACT(MONTH FROM end_gap),2,'0')
		ELSE
			TO_CHAR(TRUNC(EXTRACT(MONTH FROM end_gap)  / 3,0))
	END || EXTRACT(YEAR FROM start_gap)
 
	AS diag
FROM (
	SELECT 
		numadh, type_cotisation,
		ADD_MONTHS(TRUNC(TO_DATE(year,'YYYY'),'YYYY'),start_month) AS start_date,
		ADD_MONTHS(TRUNC(TO_DATE(year,'YYYY'),'YYYY'),end_month) - (1/(24*3600)) AS end_date
	FROM (
		SELECT 
			numadh,
			SUBSTR(period,1,1) AS type_cotisation, 
			CASE substr(period,1,1) WHEN 'T'
				THEN 3*(TO_NUMBER(substr(period,2,1))-1) 
			ELSE
				TO_NUMBER(SUBSTR(period,2,2))-1
			END AS start_month,
			CASE SUBSTR(period,1,1) WHEN 'T'
				THEN 3*TO_NUMBER(SUBSTR(period,2,1)) 
			ELSE
				TO_NUMBER(substr(period,2,2))
			END AS end_month,
			TO_NUMBER(SUBSTR(period,-4)) AS year
		FROM data 
	)
)
MATCH_RECOGNIZE(
	PARTITION BY numadh
	ORDER BY start_date
	MEASURES MAX(end_date) + (1/(24*3600)) start_gap, NEXT(start_date) - (1/(24*3600)) end_gap
	ALL ROWS PER MATCH
	PATTERN((A| {- B -} )+)
	DEFINE A AS MAX(end_date) < NEXT(start_date) - (1/(24*3600))
)
;

00258000	M	01-JAN-2020 00:00:00	30-NOV-2021 23:59:59	M012020 to M112020
00258900	M	01-FEB-2020 00:00:00	30-NOV-2021 23:59:59	M022020 to M112020
00258975	T	01-APR-2020 00:00:00	30-SEP-2021 23:59:59	T22020 to T32020