LAST_VALUE() or not LAST_VALUE() ?

**StringBuilder** · 20/02/2019, 12h36

Bonjour,

J'ai une table de suivi de changement de status.
Je souhaite récupérer, pour un critère de regroupement donné, la "dernière valeur" du statut, en prenant l'ordre de l'id comme critère chronologique.

Voici un exemple de code :

Code sql :

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 test (id, num, nom)
as
(
	select 1, 2, 'TOTO'
	union all
	select 2, 4, 'TOTO'
	union all
	select 3, 3, 'TOTO'
	union all
	select 4, 1, 'TITI'
	union all
	select 5, 2, 'TATA'
	union all
	select 6, 4, 'TATA'
)
select nom, last_value(num) over (partition by nom order by id)
from test;

Je m'attentais à obtenir :

Code :

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

1
2
3
4
 
TOTO	3
TITI	1
TATA	4

Au au moins :

Code :

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

1
2
3
4
5
6
7
 
TOTO	3
TOTO	3
TOTO	3
TITI	1
TATA	4
TATA	4

Mais à la place, j'obtiens :

Code :

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

1
2
3
4
5
6
7
 
TOTO	2
TOTO	4
TOTO	3
TITI	1
TATA	2
TATA	4

J'ai bien l'impression que j'ai rien compris à quoi sert LAST_VALUE() ni comment ça marche...
Dois-je utiliser une autre fonction ? Laquelle ?

**StringBuilder** · 20/02/2019, 13h04

Je m'en suis sorti de cette manière...

Mais c'est franchement mieux qu'une sous-requête ?

Y'a pas plus propre ?

Code sql :

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
 
with test (id, num, nom)
as
(
	select 1, 2, 'TOTO'
	union all
	select 2, 4, 'TOTO'
	union all
	select 3, 3, 'TOTO'
	union all
	select 4, 1, 'TITI'
	union all
	select 5, 2, 'TATA'
	union all
	select 6, 4, 'TATA'
),
tmp (id, num, nom, last_id)
as
(
	select id, num, nom, max(id) over (partition by nom order by nom)
	from test
)
select id, num, nom from tmp where id = last_id

**aieeeuuuuu** · 20/02/2019, 15h24

Bonjour,

Avec LAST_VALUE, la clause ORDER BY restreint la fenêtre aux seules lignes qui précédent (selon l'ORDER BY), comme lorsqu'elle est utilisée avec SUMPar défaut, c'est donc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Il faut donc faire ainsi :

Code :

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

1
2
3
 
SELECT DISTINCT nom, last_value(num) over (partition by nom order by id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
from test

**StringBuilder** · 20/02/2019, 16h17

WOW, j'avais jamais vu cette syntaxe !

Et elle saute pas aux yeux dans la doc de SQL Server...

Et sinon, on saurait faire une combinaison de :
- LAST_VALUE
- distinct / group by histoire de virer les doublons
- LAG pour aller voir si le last value de la partition précédente est différent

Autrement qu'en refaisant une CTE ?

Code sql :

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
 
declare @TypeMarche int;
declare @PlanAction bit;
declare @Solution int;
declare @DroitId bigint;
 
set @TypeMarche = null;			-- Null pour tous les avoir
set @PlanAction = 1;			-- Null pour tous les avoir
set @Solution = 16;				-- Null pour tous les avoir
-- 117681039105				-- user test 1
-- 122405679361				-- user test 2
-- 53257944705					-- user test 3
set @DroitId = 532575944705;
 
/*
Prendre en compte uniquement le dernier statut pour chaque mois
 
Nouvelle affaire = Se baser sur la date de détection de l'affaire
En cours = Date de changement de "statut prescription" à la valeur "en cours"
 
Réussie: statut prescription réussi
=> Attente AO = Statut Affaire DETECTEE + Stade d'avancement INTENTION ou AVANTPROJET
=> En étude = Statut Affaire ENETUDE
=> Gagnée = Statut Affaire GAGNEE
=> Perdue = Statut Affaire PERDUE
 
Filtres :
Solution prescrite : filtrer le type de solution prescrite
Présence plan d'action : vérifier qu'il y en a au moins 1 sur l'affaire
Type de marché : champ de l'entête de l'affaire
 
Droits : prendre en compte l'arborescence hiérarchique
*/
 
with calendrier (annee, mois, date)
as
(
	-- On prends l'année en cours ainsi que l'année passée
	select year(getdate()) - 1, 1, DATEFROMPARTS(year(getdate()) - 1, 1, 1)
	union all
	select year(dateadd(m, 1, calendrier.date)), MONTH(dateadd(m, 1, calendrier.date)), dateadd(m, 1, calendrier.date)
	from calendrier
	where calendrier.date < DATEFROMPARTS(year(getdate()), 12, 1)
),
hierarchie (RACINE, ID, NAME, chemin) AS (
	SELECT ID.ID, ID.ID, ID.BEZEICHNUNG, cast(concat(';', ID.ID, ';') as varchar(max))
	FROM TE_ISI_ID ID
	UNION ALL
	SELECT hierarchie.RACINE, ID.ID, ID.BEZEICHNUNG, cast(concat(hierarchie.CHEMIN, ID.ID, ';') as varchar(max))
	FROM TE_ISI_ID ID
	INNER JOIN hierarchie ON hierarchie.ID = ID.VORGESETZTERID and chemin not like cast(concat('%;', ID.ID, ';%') as varchar(max))
),
cssp_raw (id_oj, annee, mois, codeStadeAvancement, codeStatutPrescription, codeStatutAffaire)
as
(
	SELECT distinct
		cs.id_oj, 
		cast(cs.F7012_DT / 10000000000000 as int), 
		cast(cs.F7012_DT / 100000000000 as int) % 100,
		last_value(isnull(KAStadeAvancement.ExtKey, '')) over (partition by cs.id_oj, cast(cs.F7012_DT / 10000000000000 as int), cast(cs.F7012_DT / 100000000000 as int) % 100 order by cs.F7012_DT ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 
		last_value(cs.F7009) over (partition by cs.id_oj, cast(cs.F7012_DT / 10000000000000 as int), cast(cs.F7012_DT / 100000000000 as int) % 100 order by cs.F7012_DT ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 
		last_value(isnull(KAStatutAffaire.ExtKey, '')) over (partition by cs.id_oj, cast(cs.F7012_DT / 10000000000000 as int), cast(cs.F7012_DT / 100000000000 as int) % 100 order by cs.F7012_DT ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    FROM te_isi_c011 cs
	left outer join TE_ISI_KA KAStadeAvancement on KAStadeAvancement.KatNr = 1086 and KAStadeAvancement.Code = cs.F7007 and KAStadeAvancement.SpracheNr = 0 and KAStadeAvancement.KatSperre = 0 and KAStadeAvancement.LosKZ = 0
	left outer join TE_ISI_KA KAStatutAffaire on KAStatutAffaire.KatNr = 1109 and KAStatutAffaire.Code = cs.F7010 and KAStatutAffaire.SpracheNr = 0 and KAStatutAffaire.KatSperre = 0 and KAStatutAffaire.LosKZ = 0
),
cssp (id_oj, annee, mois, codeStadeAvancement, codeStatutPrescription, codeStatutAffaire, chgStadeAvancement, chgStatutPrescription, chgStatutAffaire)
as
(
	select 
		id_oj, 
		annee, 
		mois, 
		codeStadeAvancement, 
		codeStatutPrescription, 
		codeStatutAffaire, 
		case lag(codeStadeAvancement, 1, '') over (partition by id_oj order by annee, mois) when codeStadeAvancement then 0 else 1 end,
		case lag(codeStatutPrescription, 1, 0) over (partition by id_oj order by annee, mois) when codeStatutPrescription then 0 else 1 end,
		case lag(codeStatutAffaire, 1, '') over (partition by id_oj order by annee, mois) when codeStatutAffaire then 0 else 1 end
	from cssp_raw
)
select 
	calendrier.annee,
	calendrier.mois,
	oj.id,
	oj.Objekt,
	-- Prescriptions en cours
	CASE WHEN calendrier.annee = year(getdate()) - 1 and cssp.codeStatutPrescription = 1 and cssp.chgStatutPrescription = 1 then 1 else 0 end AS Prescription_en_cours_N1,
	CASE WHEN calendrier.annee = year(getdate()) and cssp.codeStatutPrescription = 1 and cssp.chgStatutPrescription = 1 then 1 else 0 end AS Prescription_en_cours_N,
 
	-- Prescriptions nouvelles
	CASE WHEN calendrier.annee = year(getdate()) - 1 and calendrier.annee = cast(oj.F7012 / 10000 as int) and calendrier.mois = cast(oj.F7012 / 100 as int) % 100 and isnull(cssp.codeStatutPrescription, 0) <> 1 then 1 else 0 end AS Prescription_nouvelle_N1,
	CASE WHEN calendrier.annee = year(getdate()) and calendrier.annee = cast(oj.F7012 / 10000 as int) and calendrier.mois = cast(oj.F7012 / 100 as int) % 100 and isnull(cssp.codeStatutPrescription, 0) <> 1 then 1 else 0 end AS Prescription_nouvelle_N,
 
	-- Prescriptions réussies
    CASE WHEN calendrier.annee = year(getdate()) - 1 and cssp.codeStatutPrescription = 2 and cssp.codeStatutAffaire = 'DETECTEE' AND cssp.codeStadeAvancement in ('INTENTION', 'AVANTPROJET') and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussieAttenteAO_N1,
    CASE WHEN calendrier.annee = year(getdate()) and cssp.codeStatutPrescription = 2 and cssp.codeStatutAffaire = 'DETECTEE' AND cssp.codeStadeAvancement in ('INTENTION', 'AVANTPROJET') and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussieAttenteAO_N,
 
    CASE WHEN calendrier.annee = year(getdate()) - 1 and cssp.codeStatutPrescription = 2 AND cssp.codeStadeAvancement = 'APPELOFFRE' and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussieEtude_N1,
    CASE WHEN calendrier.annee = year(getdate()) and cssp.codeStatutPrescription = 2 AND cssp.codeStadeAvancement = 'APPELOFFRE' and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussieEtude_N,
 
    CASE WHEN calendrier.annee = year(getdate()) - 1 and cssp.codeStatutPrescription = 2 AND cssp.codeStadeAvancement = 'ADJUDICATION' and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussieGagnee_N1,
    CASE WHEN calendrier.annee = year(getdate()) and cssp.codeStatutPrescription = 2 AND cssp.codeStadeAvancement = 'ADJUDICATION' and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussieGagnee_N,
 
    CASE WHEN calendrier.annee = year(getdate()) - 1 and cssp.codeStatutPrescription = 2 AND cssp.codeStadeAvancement = 'COMMANDE' and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussiePerdue_N1,
    CASE WHEN calendrier.annee = year(getdate()) and cssp.codeStatutPrescription = 2 AND cssp.codeStadeAvancement = 'COMMANDE' and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussiePerdue_N,
 
	-- Prescription non réussies
    CASE WHEN calendrier.annee = year(getdate()) - 1 and cssp.codeStatutPrescription = 3 and cssp.codeStatutAffaire = 'DETECTEE' AND cssp.codeStadeAvancement in ('INTENTION', 'AVANTPROJET') and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussieAttenteAO_N1,
    CASE WHEN calendrier.annee = year(getdate()) and cssp.codeStatutPrescription = 3 and cssp.codeStatutAffaire = 'DETECTEE' AND cssp.codeStadeAvancement in ('INTENTION', 'AVANTPROJET') and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussieAttenteAO_N,
 
    CASE WHEN calendrier.annee = year(getdate()) - 1 and cssp.codeStatutPrescription = 3 AND cssp.codeStadeAvancement = 'APPELOFFRE' and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussieEtude_N1,
    CASE WHEN calendrier.annee = year(getdate()) and cssp.codeStatutPrescription = 3 AND cssp.codeStadeAvancement = 'APPELOFFRE' and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussieEtude_N,
 
    CASE WHEN calendrier.annee = year(getdate()) - 1 and cssp.codeStatutPrescription = 3 AND cssp.codeStadeAvancement = 'ADJUDICATION' and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussieGagnee_N1,
    CASE WHEN calendrier.annee = year(getdate()) and cssp.codeStatutPrescription = 3 AND cssp.codeStadeAvancement = 'ADJUDICATION' and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussieGagnee_N,
 
    CASE WHEN calendrier.annee = year(getdate()) - 1 and cssp.codeStatutPrescription = 3 AND cssp.codeStadeAvancement = 'COMMANDE' and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussiePerdue_N1,
    CASE WHEN calendrier.annee = year(getdate()) and cssp.codeStatutPrescription = 3 AND cssp.codeStadeAvancement = 'COMMANDE' and cssp.chgStadeAvancement = 1 then 1 else 0 end AS reussiePerdue_N
 
from TE_ISI_OJ oj
cross join calendrier
left outer join cssp on cssp.id_oj = oj.id and cssp.annee = calendrier.annee and cssp.mois = calendrier.mois
INNER JOIN hierarchie ON hierarchie.id = oj.AD2Id
 
where 
-- Fitres sur l'affaire
	oj.Objektart = 0									-- Affaires Lot 3
and oj.LosKZ = 0										-- Affaires non supprimées
and oj.UpdL > (year(getdate()) - 1) * 10000000000000	-- Affaires depuis l'année dernière
and oj.AD2Id > 0										-- Droits
AND CAST(((hierarchie.RACINE / 100000) + ((hierarchie.RACINE % 100000) * 4294967296)) AS VARCHAR) = @DroitId
and (oj.F7032 = @TypeMarche or @TypeMarche is null)		-- Filtre sur le type de marché
and (isnull(@PlanAction, 0) = 0 or exists (				-- Filtre sur la présence de plan d'action
	select * from te_isi_c015 c015 where c015.DEL = 0 and c015.ID_OJ = oj.ID
))
and (@Solution is null or exists (						-- Filtre sur la solution prescrite
	select * from te_isi_c021 c021 where c021.DEL = 0 and c021.ID_OJ = oj.ID and c021.F7007 = @Solution
))
-- On ne prends que les affaires qui ont changé de statut sur la période
and exists (
	select * from te_isi_c011 c011 where c011.DEL = 0 and c011.ID_OJ = oj.id and c011.F7012_DT > year(getdate()) * 10000
)
order by oj.id, calendrier.annee, calendrier.mois;

**ectap** · 20/02/2019, 23h02

Bonjour,

Pour "récupérer, pour un critère de regroupement donné, la "dernière valeur" du statut, en prenant l'ordre de l'id comme critère chronologique", j'utiliserai plutôt ROW_NUMBER():

On trie chaque ensemble de lignes avec le même critère de regroupement suivant l'id décroissant
On numérote les lignes de chaque ensemble
On ne garde que les lignes numéro 1 (--> celles qui ont le plus grand Id dans chaque ensemble)

Code :

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

1
2
3
4
5
6
7
8
9
 
select nom, num
from
   (select nom, num
      , row_number() over (partition by nom order by id desc) as numr_ordr
   from test
   ) AS T_test_ordr
WHERE  numr_ordr=1
;

Bonne soirée

**Waldar** · 22/02/2019, 12h23

Envoyé par ectap

Pour "récupérer, pour un critère de regroupement donné, la "dernière valeur" du statut, en prenant l'ordre de l'id comme critère chronologique", j'utiliserai plutôt ROW_NUMBER()

Tout à fait d'accord, et ce sera la solution la plus performante.

**StringBuilder** · 23/02/2019, 10h40

Ben non, je souhaite pas :
- Connaître le dernier numéro de ligne
- Ni faire de sous-requête

La réponse de aieeeuuuuu avec les arguments complémentaies à ORDER BY répond exactement à ma question, et en plus sémantiquement c'est exactement ça : je veux la "dernière valeur", donc bien "last_value" et non pas "la valeur de la dernière ligne".

Sinon, finalement la demande a changé (en partie) sinon ça aurait été pas drôle.

Pour la postérité, voici la requête, qui tourne en moins de 1 seconde sur une table de plus de 300 000 lignes :

Code sql :

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
 
/*
X Nouvelle affaire = Date de détection
X Modifier : prendre la date de CREATION
 
X En cours = Date la plus récente de changement de statut "prescription" pour les affaires avec statut prescription "en cours"
X Modifier : prendre toutes les affaires au statut "en cours" à la fin du mois en question y compris si elle n'a pas changé de statut
 
Réussie: statut prescription. Réussie = 2 / Non réussie = 3
X => Attente AO = Statut Affaire DETECTEE + Stade d'avancement INTENTION ou AVANTPROJET
X => En étude = Statut Affaire ENETUDE
X => Gagnée = Statut Affaire GAGNEE
X => Perdue = Statut Affaire PERDUE
X Modifier : compter non pas le dernier changement, mais chaque changement dans le mois, mais une seule fois par valeur de statut
 
Filtres :
X Solution prescrite : filtrer sur produit
X Présence plan d'action : vérifier qu'il y en a au moins 1
X Type de marché : champ de l'entête OJ
 
X Droits
*/
 
declare @TypeMarche int;
declare @PlanAction bit;
declare @Solution int;
declare @DroitId bigint;
 
set @TypeMarche = null;			-- Null pour tous les avoir
set @PlanAction = 1;			-- Null pour tous les avoir
set @Solution = 16;				-- Null pour tous les avoir
-- 1176821039105				-- USER 1
-- 1224065679361				-- USER 2
-- 532575944705					-- USER 3
set @DroitId = 532575944705;
 
with calendrier (annee, mois, anneemois, date)
as
(
	-- On prends l'année en cours ainsi que l'année passée
	select year(getdate()) - 1, 1, (year(getdate()) - 1) * 100 + 1, DATEFROMPARTS(year(getdate()) - 1, 1, 1)
	union all
	select year(dateadd(m, 1, calendrier.date)), MONTH(dateadd(m, 1, calendrier.date)), year(dateadd(m, 1, calendrier.date)) * 100 + MONTH(dateadd(m, 1, calendrier.date)), dateadd(m, 1, calendrier.date)
	from calendrier
	where calendrier.date < dateadd(m, -1, getdate())
),
hierarchie (RACINE, ID, NAME, chemin) AS (
	SELECT ID.ID, ID.ID, ID.BEZEICHNUNG, cast(concat(';', ID.ID, ';') as varchar(max))
	FROM TE_ISI_ID ID
	UNION ALL
	SELECT hierarchie.RACINE, ID.ID, ID.BEZEICHNUNG, cast(concat(hierarchie.CHEMIN, ID.ID, ';') as varchar(max))
	FROM TE_ISI_ID ID
	INNER JOIN hierarchie ON hierarchie.ID = ID.VORGESETZTERID and chemin not like cast(concat('%;', ID.ID, ';%') as varchar(max))
),
cssp_finmois (id_oj, anneemois, codeStatutPrescription)
as
(
	-- On récupère le statut de prescription en fin de chaque mois pour savoir lesquelles sont "en cours"
	SELECT distinct
		cs.id_oj, 
		cast(cs.F7012_DT / 10000000000000 as int) * 100 + cast(cs.F7012_DT / 100000000000 as int) % 100,
		last_value(cs.F7009) over (partition by cs.id_oj, cast(cs.F7012_DT / 10000000000000 as int) * 100 + cast(cs.F7012_DT / 100000000000 as int) % 100 order by cs.F7012_DT ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    FROM te_isi_c011 cs
	where cs.DEL = 0
),
cssp_raw (id_oj, anneemois, reussieattenteao, reussieetude, reussiegagnee, reussieperdue, nonreussieattenteao, nonreussieetude, nonreussiegagnee, nonreussieperdue)
as
(
	-- On récupère la présence des différents status pour chaque mois
	select 
		c.id_oj, 
		cast(c.F7012_DT / 100000000000 as int), 
 
		-- Réussies
		max(case when c.F7009 = 2 and KAStatutAffaire.ExtKey = 'DETECTEE' AND KAStadeAvancement.ExtKey in ('INTENTION', 'AVANTPROJET') then 1 else 0 end), 
		max(case when c.F7009 = 2 and KAStatutAffaire.ExtKey = 'ENETUDE' then 1 else 0 end), 
		max(case when c.F7009 = 2 and KAStatutAffaire.ExtKey = 'GAGNEE' then 1 else 0 end), 
		max(case when c.F7009 = 2 and KAStatutAffaire.ExtKey = 'PERDUE' then 1 else 0 end), 
 
		-- Non Réussies
		max(case when c.F7009 = 3 and KAStatutAffaire.ExtKey = 'DETECTEE' AND KAStadeAvancement.ExtKey in ('INTENTION', 'AVANTPROJET') then 1 else 0 end), 
		max(case when c.F7009 = 3 and KAStatutAffaire.ExtKey = 'ENETUDE' then 1 else 0 end), 
		max(case when c.F7009 = 3 and KAStatutAffaire.ExtKey = 'GAGNEE' then 1 else 0 end), 
		max(case when c.F7009 = 3 and KAStatutAffaire.ExtKey = 'PERDUE' then 1 else 0 end)
	from te_isi_c011 c
	left outer join TE_ISI_KA KAStadeAvancement on KAStadeAvancement.KatNr = 1086 and KAStadeAvancement.Code = c.F7007 and KAStadeAvancement.SpracheNr = 0 and KAStadeAvancement.KatSperre = 0 and KAStadeAvancement.LosKZ = 0
	left outer join TE_ISI_KA KAStatutAffaire on KAStatutAffaire.KatNr = 1109 and KAStatutAffaire.Code = c.F7010 and KAStatutAffaire.SpracheNr = 0 and KAStatutAffaire.KatSperre = 0 and KAStatutAffaire.LosKZ = 0
	where c.DEL = 0
	group by c.id_oj, cast(c.F7012_DT / 100000000000 as int)
),
cssp (id_oj, anneemois, reussieattenteao, reussieetude, reussiegagnee, reussieperdue, nonreussieattenteao, nonreussieetude, nonreussiegagnee, nonreussieperdue, chgreussieattenteao, chgreussieetude, chgreussiegagnee, chgreussieperdue, chgnonreussieattenteao, chgnonreussieetude, chgnonreussiegagnee, chgnonreussieperdue)
as
(
	-- Pour chaque présence de valeur de statut mensuel, on vérifie s'il s'agit d'un changement par rapport au mois précédent ou non
	select 
		id_oj, 
		anneemois, 
		reussieattenteao, 
		reussieetude, 
		reussiegagnee, 
		reussieperdue, 
		nonreussieattenteao, 
		nonreussieetude, 
		nonreussiegagnee, 
		nonreussieperdue, 
		case lag(reussieattenteao, 1, 0) over (partition by id_oj order by anneemois) when reussieattenteao then 0 else 1 end chgreussieattenteao,
		case lag(reussieetude, 1, 0) over (partition by id_oj order by anneemois) when reussieetude then 0 else 1 end chgreussieetude,
		case lag(reussiegagnee, 1, 0) over (partition by id_oj order by anneemois) when reussiegagnee then 0 else 1 end chgreussiegagnee,
		case lag(reussieperdue, 1, 0) over (partition by id_oj order by anneemois) when reussieperdue then 0 else 1 end chgreussieperdue,
		case lag(nonreussieattenteao, 1, 0) over (partition by id_oj order by anneemois) when nonreussieattenteao then 0 else 1 end chgnonreussieattenteao,
		case lag(nonreussieetude, 1, 0) over (partition by id_oj order by anneemois) when nonreussieetude then 0 else 1 end chgnonreussieetude,
		case lag(nonreussiegagnee, 1, 0) over (partition by id_oj order by anneemois) when nonreussiegagnee then 0 else 1 end chgnonreussiegagnee,
		case lag(nonreussieperdue, 1, 0) over (partition by id_oj order by anneemois) when nonreussieperdue then 0 else 1 end chgnonreussieperdue
	from cssp_raw
)
select 
	ca.annee,
	ca.mois,
	oj.id,
	oj.Objekt,
	-- Prescriptions en cours
	isnull(CASE WHEN ca.annee = year(getdate()) - 1 and c1.codeStatutPrescription = 1 then 1 else 0 end, 0) AS Prescription_en_cours_N1,
	isnull(CASE WHEN ca.annee = year(getdate()) and c1.codeStatutPrescription = 1 then 1 else 0 end, 0) AS Prescription_en_cours_N,
 
	-- Prescriptions nouvelles
	isnull(CASE WHEN ca.annee = year(getdate()) - 1 and ca.annee = cast(oj.Neu / 10000000000000 as int) and ca.mois = cast(oj.Neu / 100000000000 as int) % 100 then 1 else 0 end, 0) AS Prescription_nouvelle_N1,
	isnull(CASE WHEN ca.annee = year(getdate()) and ca.annee = cast(oj.Neu / 10000000000000 as int) and ca.mois = cast(oj.Neu / 100000000000 as int) % 100 then 1 else 0 end, 0) AS Prescription_nouvelle_N,
 
	-- Prescriptions réussies
    isnull(CASE WHEN ca.annee = year(getdate()) - 1 then cssp.reussieattenteao * cssp.chgreussieattenteao else 0 end, 0) AS reussieAttenteAO_N1,
    isnull(CASE WHEN ca.annee = year(getdate()) then cssp.reussieattenteao * cssp.chgreussieattenteao else 0 end, 0) AS reussieAttenteAO_N,
 
    isnull(CASE WHEN ca.annee = year(getdate()) - 1 then cssp.reussieetude * cssp.chgreussieetude else 0 end, 0) AS reussieEtude_N1,
    isnull(CASE WHEN ca.annee = year(getdate()) then cssp.reussieetude * cssp.chgreussieetude else 0 end, 0) AS reussieEtude_N,
 
    isnull(CASE WHEN ca.annee = year(getdate()) - 1 then cssp.reussiegagnee * cssp.chgreussiegagnee else 0 end, 0) AS reussieGagnee_N1,
    isnull(CASE WHEN ca.annee = year(getdate()) then cssp.reussiegagnee * cssp.chgreussiegagnee else 0 end, 0) AS reussieGagnee_N,
 
    isnull(CASE WHEN ca.annee = year(getdate()) - 1 then cssp.reussieperdue * cssp.chgreussieperdue else 0 end, 0) AS reussiePerdue_N1,
    isnull(CASE WHEN ca.annee = year(getdate()) then cssp.reussieperdue * cssp.chgreussieperdue else 0 end, 0) AS reussiePerdue_N,
 
	-- Prescription non réussies
    isnull(CASE WHEN ca.annee = year(getdate()) - 1 then cssp.nonreussieattenteao * cssp.chgnonreussieattenteao else 0 end, 0) AS nonreussieAttenteAO_N1,
    isnull(CASE WHEN ca.annee = year(getdate()) then cssp.nonreussieattenteao * cssp.chgnonreussieattenteao else 0 end, 0) AS nonreussieAttenteAO_N,
 
    isnull(CASE WHEN ca.annee = year(getdate()) - 1 then cssp.nonreussieetude * cssp.chgnonreussieetude else 0 end, 0) AS nonreussieEtude_N1,
    isnull(CASE WHEN ca.annee = year(getdate()) then cssp.nonreussieetude * cssp.chgnonreussieetude else 0 end, 0) AS nonreussieEtude_N,
 
    isnull(CASE WHEN ca.annee = year(getdate()) - 1 then cssp.nonreussiegagnee * cssp.chgnonreussiegagnee else 0 end, 0) AS nonreussieGagnee_N1,
    isnull(CASE WHEN ca.annee = year(getdate()) then cssp.nonreussiegagnee * cssp.chgnonreussiegagnee else 0 end, 0) AS nonreussieGagnee_N,
 
    isnull(CASE WHEN ca.annee = year(getdate()) - 1 then cssp.nonreussieperdue * cssp.chgnonreussieperdue else 0 end, 0) AS nonreussiePerdue_N1,
    isnull(CASE WHEN ca.annee = year(getdate()) then cssp.nonreussieperdue * cssp.chgnonreussieperdue else 0 end, 0) AS nonreussiePerdue_N
 
from TE_ISI_OJ oj
cross join calendrier ca
left outer join cssp on cssp.id_oj = oj.id and cssp.anneemois = ca.anneemois
-- Pour chaque mois du calendrier, on va chercher le statut de prescription actif (c'est à dire la dernière valeur parmis les mois antérieurs)
left outer join cssp_finmois c1 on c1.anneemois = (select max(c2.anneemois) from cssp_finmois c2 where c2.anneemois <= ca.anneemois and c2.id_oj = oj.id) and c1.id_oj = oj.id
INNER JOIN hierarchie ON hierarchie.id = oj.AD2Id
 
where 
-- Fitres sur l'affaire
	oj.Objektart = 0									-- Affaires Lot 3
and oj.LosKZ = 0										-- Affaires non supprimées
and oj.UpdL > (year(getdate()) - 1) * 10000000000000	-- Affaires modifiée depuis l'année dernière
and oj.AD2Id > 0										-- Droits
AND CAST(((hierarchie.RACINE / 100000) + ((hierarchie.RACINE % 100000) * 4294967296)) AS VARCHAR) = @DroitId
and (oj.F7032 = @TypeMarche or @TypeMarche is null)		-- Filtre sur le type de marché
and (isnull(@PlanAction, 0) = 0 or exists (				-- Filtre sur la présence de plan d'action
	select * from te_isi_c015 c015 where c015.DEL = 0 and c015.ID_OJ = oj.ID
))
and (@Solution is null or exists (						-- Filtre sur la solution prescrite
	select * from te_isi_c021 c021 where c021.DEL = 0 and c021.ID_OJ = oj.ID and c021.F7007 = @Solution
))
-- On ne prends que les affaires qui ont changé de statut sur la période
and exists (
	select * from te_isi_c011 c011 where c011.DEL = 0 and c011.ID_OJ = oj.id and c011.F7012_DT > (year(getdate()) - 1) * 10000
)
order by oj.id, ca.anneemois;

LAST_VALUE() or not LAST_VALUE() ?

Langage SQL

Discussions similaires

Partager

Partager