ORDER BY date du jour n-1

**iKeyyZ** · 03/05/2019, 09h45

Bonjour, je souhaiterai obtenir une condition qui affiche les résultats de la requête compris pour N-1 entre le 01.10.2017 à la date du jour N-1.
Voici la requête (en rouge ce qu'il faut probablement modifier) :

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
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
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
SELECT "DATA"."Exercice","DATA"."Type de doc", "DATA"."DocNum","DATA"."DocDate", "DATA"."CardCode", "DATA"."CardName", "DATA"."SlpName","DATA"."ItemCode","DATA"."ItmsGrpCod", "DATA"."ItmsGrpNam","DATA"."LineNum",SUM("DATA"."CA HT Récurrent"),SUM("DATA"."CA HT Prestation"),SUM("DATA"."CA HT One Shot")
FROM(

SELECT 'N-1' "Exercice",'Facture' "Type de doc",T0."DocNum",T0."DocDate", T0."CardCode", T0."CardName", T2."SlpName",T6."ItemCode",T6."ItmsGrpCod", T8."ItmsGrpNam",T4."LineNum",CASE 
WHEN coalesce(T0."DiscPrcnt",0) = 0 then T4."LineTotal"
ELSE T4."LineTotal" -  (T4."LineTotal" *  T0."DiscPrcnt"/100)
END "CA HT Récurrent",0 "CA HT Prestation",0 "CA HT One Shot" 
FROM OINV T0  
LEFT JOIN OSLP T2 ON T2."SlpCode" = T0."SlpCode"
LEFT JOIN INV1 T4 ON T4."DocEntry"=T0."DocEntry"
LEFT JOIN OITM T6 ON T6."ItemCode"=T4."ItemCode"
LEFT JOIN OITB T8 ON T8."ItmsGrpCod"=T6."ItmsGrpCod"
WHERE T0."CANCELED" = 'N'  and T4."LineTotal"<>0 and T8."ItmsGrpNam" IS NOT NULL AND T8."U_OSTYPECOMM" = 'Rec'AND CASE WHEN EXTRACT(MONTH FROM T0."DocDate") BETWEEN 01 and 09 
AND EXTRACT(YEAR FROM T0."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN 1
WHEN EXTRACT(MONTH FROM T0."DocDate") BETWEEN 10 AND 12
AND EXTRACT(YEAR FROM T0."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 2
THEN 1
ELSE 0
END = 1
 

UNION

SELECT 'N-1' "Exercice",'Avoir' "Type de doc", T1."DocNum", T1."DocDate", T1."CardCode", T1."CardName",T3."SlpName",T7."ItemCode",T7."ItmsGrpCod",T9."ItmsGrpNam",T5."LineNum",CASE 
WHEN coalesce(T1."DiscPrcnt",0) = 0 then -T5."LineTotal"
ELSE -(T5."LineTotal" -  (T5."LineTotal" *  T1."DiscPrcnt" /100))
END "CA HT Récurrent",0 "CA HT Prestation",0 "CA HT One Shot"
FROM ORIN T1 
LEFT JOIN OSLP T3 ON T3."SlpCode"=T1."SlpCode"
LEFT JOIN RIN1 T5 ON T5."DocEntry"=T1."DocEntry"
LEFT JOIN OITM T7 ON T7."ItemCode"=T5."ItemCode"
LEFT JOIN OITB T9 ON T9."ItmsGrpCod"=T7."ItmsGrpCod"
WHERE T1."CANCELED" ='N' and T5."LineTotal"<>0 and T9."ItmsGrpNam" IS NOT NULL AND T9."U_OSTYPECOMM" = 'Rec' AND CASE WHEN EXTRACT(MONTH FROM T1."DocDate") BETWEEN 01 and 09 
AND EXTRACT(YEAR FROM T1."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN 1
WHEN EXTRACT(MONTH FROM T1."DocDate") BETWEEN 10 AND 12
AND EXTRACT(YEAR FROM T1."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 2
THEN 1
ELSE 0
END = 1



UNION

SELECT 'N-1' "Exercice",'Facture' "Type de doc",T0."DocNum",T0."DocDate", T0."CardCode", T0."CardName", T2."SlpName",T6."ItemCode",T6."ItmsGrpCod", T8."ItmsGrpNam",T4."LineNum",0 "CA HT Récurrent",CASE 
WHEN coalesce(T0."DiscPrcnt",0) = 0 then T4."LineTotal"
ELSE T4."LineTotal" -  (T4."LineTotal" *  T0."DiscPrcnt"/100)
END "CA HT Prestation",0 "CA HT One Shot"
FROM OINV T0  
LEFT JOIN OSLP T2 ON T2."SlpCode" = T0."SlpCode"
LEFT JOIN INV1 T4 ON T4."DocEntry"=T0."DocEntry"
LEFT JOIN OITM T6 ON T6."ItemCode"=T4."ItemCode"
LEFT JOIN OITB T8 ON T8."ItmsGrpCod"=T6."ItmsGrpCod"
WHERE T0."CANCELED" = 'N' and T4."LineTotal"<>0 and T8."ItmsGrpNam" IS NOT NULL AND T8."U_OSTYPECOMM" = 'Pre' AND CASE WHEN EXTRACT(MONTH FROM T0."DocDate") BETWEEN 01 and 09 
AND EXTRACT(YEAR FROM T0."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN 1
WHEN EXTRACT(MONTH FROM T0."DocDate") BETWEEN 10 AND 12
AND EXTRACT(YEAR FROM T0."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 2
THEN 1
ELSE 0
END = 1
 

UNION

SELECT 'N-1' "Exercice",'Avoir' "Type de doc", T1."DocNum", T1."DocDate", T1."CardCode", T1."CardName",T3."SlpName",T7."ItemCode",T7."ItmsGrpCod",T9."ItmsGrpNam",T5."LineNum",0 "CA HT Récurrent",CASE 
WHEN coalesce(T1."DiscPrcnt",0) = 0 then -T5."LineTotal"
ELSE -(T5."LineTotal" -  (T5."LineTotal" *  T1."DiscPrcnt" /100))
END "CA HT Prestation",0 "CA HT One Shot"
FROM ORIN T1 
LEFT JOIN OSLP T3 ON T3."SlpCode"=T1."SlpCode"
LEFT JOIN RIN1 T5 ON T5."DocEntry"=T1."DocEntry"
LEFT JOIN OITM T7 ON T7."ItemCode"=T5."ItemCode"
LEFT JOIN OITB T9 ON T9."ItmsGrpCod"=T7."ItmsGrpCod"
WHERE T1."CANCELED" ='N' and T5."LineTotal"<>0 and T9."ItmsGrpNam" IS NOT NULL AND T9."U_OSTYPECOMM" = 'Pre' AND CASE WHEN EXTRACT(MONTH FROM T1."DocDate") BETWEEN 01 and 09 
AND EXTRACT(YEAR FROM T1."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN 1
WHEN EXTRACT(MONTH FROM T1."DocDate") BETWEEN 10 AND 12
AND EXTRACT(YEAR FROM T1."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 2
THEN 1
ELSE 0
END = 1



UNION

SELECT 'N-1' "Exercice",'Facture' "Type de doc",T0."DocNum",T0."DocDate", T0."CardCode", T0."CardName", T2."SlpName",T6."ItemCode",T6."ItmsGrpCod", T8."ItmsGrpNam",T4."LineNum",0 "CA HT Récurrent",0 "CA HT Prestation",CASE 
WHEN coalesce(T0."DiscPrcnt",0) = 0 then T4."LineTotal"
ELSE T4."LineTotal" -  (T4."LineTotal" *  T0."DiscPrcnt"/100)
END "CA HT One Shot" 
FROM OINV T0  
LEFT JOIN OSLP T2 ON T2."SlpCode" = T0."SlpCode"
LEFT JOIN INV1 T4 ON T4."DocEntry"=T0."DocEntry"
LEFT JOIN OITM T6 ON T6."ItemCode"=T4."ItemCode"
LEFT JOIN OITB T8 ON T8."ItmsGrpCod"=T6."ItmsGrpCod"
WHERE T0."CANCELED" = 'N'and T4."LineTotal"<>0 and T8."ItmsGrpNam" IS NOT NULL AND T8."U_OSTYPECOMM" = 'Neg' AND CASE WHEN EXTRACT(MONTH FROM T0."DocDate") BETWEEN 01 and 09 
AND EXTRACT(YEAR FROM T0."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN 1
WHEN EXTRACT(MONTH FROM T0."DocDate") BETWEEN 10 AND 12
AND EXTRACT(YEAR FROM T0."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 2
THEN 1
ELSE 0
END = 1
 

UNION

SELECT  'N-1' "Exercice",'Avoir' "Type de doc", T1."DocNum", T1."DocDate", T1."CardCode", T1."CardName",T3."SlpName",T7."ItemCode",T7."ItmsGrpCod",T9."ItmsGrpNam",T5."LineNum",0 "CA HT Récurrent",0 "CA HT Prestation",CASE 
WHEN coalesce(T1."DiscPrcnt",0) = 0 then -T5."LineTotal"
ELSE -(T5."LineTotal" -  (T5."LineTotal" *  T1."DiscPrcnt" /100))
END "CA HT One Shot"
FROM ORIN T1 
LEFT JOIN OSLP T3 ON T3."SlpCode"=T1."SlpCode"
LEFT JOIN RIN1 T5 ON T5."DocEntry"=T1."DocEntry"
LEFT JOIN OITM T7 ON T7."ItemCode"=T5."ItemCode"
LEFT JOIN OITB T9 ON T9."ItmsGrpCod"=T7."ItmsGrpCod"
WHERE T1."CANCELED" ='N' and T5."LineTotal"<>0 and T9."ItmsGrpNam" IS NOT NULL AND T9."U_OSTYPECOMM" = 'Neg' AND CASE WHEN EXTRACT(MONTH FROM T1."DocDate") BETWEEN 01 and 09 
AND EXTRACT(YEAR FROM T1."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN 1
WHEN EXTRACT(MONTH FROM T1."DocDate") BETWEEN 10 AND 12
AND EXTRACT(YEAR FROM T1."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 2
THEN 1
ELSE 0
END = 1

UNION

SELECT 'N' "Exercice",'Facture' "Type de doc",T0."DocNum",T0."DocDate", T0."CardCode", T0."CardName", T2."SlpName",T6."ItemCode",T6."ItmsGrpCod", T8."ItmsGrpNam",T4."LineNum",CASE 
WHEN coalesce(T0."DiscPrcnt",0) = 0 then T4."LineTotal"
ELSE T4."LineTotal" -  (T4."LineTotal" *  T0."DiscPrcnt"/100)
END "CA HT Récurrent",0 "CA HT Prestation",0 "CA HT One Shot" 
FROM OINV T0  
LEFT JOIN OSLP T2 ON T2."SlpCode" = T0."SlpCode"
LEFT JOIN INV1 T4 ON T4."DocEntry"=T0."DocEntry"
LEFT JOIN OITM T6 ON T6."ItemCode"=T4."ItemCode"
LEFT JOIN OITB T8 ON T8."ItmsGrpCod"=T6."ItmsGrpCod"
WHERE T0."CANCELED" = 'N'  and T4."LineTotal"<>0 and T8."ItmsGrpNam" IS NOT NULL AND T8."U_OSTYPECOMM" = 'Rec'AND CASE WHEN EXTRACT(MONTH FROM T0."DocDate") BETWEEN 01 and 09 
AND EXTRACT(YEAR FROM T0."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE)
THEN 1
WHEN EXTRACT(MONTH FROM T0."DocDate") BETWEEN 10 AND 12
AND EXTRACT(YEAR FROM T0."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN 1
ELSE 0
END = 1
 
UNION

SELECT 'N' "Exercice",'Avoir' "Type de doc", T1."DocNum", T1."DocDate", T1."CardCode", T1."CardName",T3."SlpName",T7."ItemCode",T7."ItmsGrpCod",T9."ItmsGrpNam",T5."LineNum",CASE 
WHEN coalesce(T1."DiscPrcnt",0) = 0 then -T5."LineTotal"
ELSE -(T5."LineTotal" -  (T5."LineTotal" *  T1."DiscPrcnt" /100))
END "CA HT Récurrent",0 "CA HT Prestation",0 "CA HT One Shot"
FROM ORIN T1 
LEFT JOIN OSLP T3 ON T3."SlpCode"=T1."SlpCode"
LEFT JOIN RIN1 T5 ON T5."DocEntry"=T1."DocEntry"
LEFT JOIN OITM T7 ON T7."ItemCode"=T5."ItemCode"
LEFT JOIN OITB T9 ON T9."ItmsGrpCod"=T7."ItmsGrpCod"
WHERE T1."CANCELED" ='N' and T5."LineTotal"<>0 and T9."ItmsGrpNam" IS NOT NULL AND T9."U_OSTYPECOMM" = 'Rec' AND CASE WHEN EXTRACT(MONTH FROM T1."DocDate") BETWEEN 01 and 09 
AND EXTRACT(YEAR FROM T1."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE)
THEN 1
WHEN EXTRACT(MONTH FROM T1."DocDate") BETWEEN 10 AND 12
AND EXTRACT(YEAR FROM T1."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN 1
ELSE 0
END = 1

UNION

SELECT 'N' "Exercice",'Facture' "Type de doc",T0."DocNum",T0."DocDate", T0."CardCode", T0."CardName", T2."SlpName",T6."ItemCode",T6."ItmsGrpCod", T8."ItmsGrpNam",T4."LineNum",0 "CA HT Récurrent",CASE 
WHEN coalesce(T0."DiscPrcnt",0) = 0 then T4."LineTotal"
ELSE T4."LineTotal" -  (T4."LineTotal" *  T0."DiscPrcnt"/100)
END "CA HT Prestation",0 "CA HT One Shot"
FROM OINV T0  
LEFT JOIN OSLP T2 ON T2."SlpCode" = T0."SlpCode"
LEFT JOIN INV1 T4 ON T4."DocEntry"=T0."DocEntry"
LEFT JOIN OITM T6 ON T6."ItemCode"=T4."ItemCode"
LEFT JOIN OITB T8 ON T8."ItmsGrpCod"=T6."ItmsGrpCod"
WHERE T0."CANCELED" = 'N' and T4."LineTotal"<>0 and T8."ItmsGrpNam" IS NOT NULL AND T8."U_OSTYPECOMM" = 'Pre'AND CASE WHEN EXTRACT(MONTH FROM T0."DocDate") BETWEEN 01 and 09 
AND EXTRACT(YEAR FROM T0."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE)
THEN 1
WHEN EXTRACT(MONTH FROM T0."DocDate") BETWEEN 10 AND 12
AND EXTRACT(YEAR FROM T0."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN 1
ELSE 0
END = 1
 
UNION

SELECT  'N' "Exercice",'Avoir' "Type de doc", T1."DocNum", T1."DocDate", T1."CardCode", T1."CardName",T3."SlpName",T7."ItemCode",T7."ItmsGrpCod",T9."ItmsGrpNam",T5."LineNum",0 "CA HT Récurrent",CASE 
WHEN coalesce(T1."DiscPrcnt",0) = 0 then -T5."LineTotal"
ELSE -(T5."LineTotal" -  (T5."LineTotal" *  T1."DiscPrcnt" /100))
END "CA HT Prestation",0 "CA HT One Shot"
FROM ORIN T1 
LEFT JOIN OSLP T3 ON T3."SlpCode"=T1."SlpCode"
LEFT JOIN RIN1 T5 ON T5."DocEntry"=T1."DocEntry"
LEFT JOIN OITM T7 ON T7."ItemCode"=T5."ItemCode"
LEFT JOIN OITB T9 ON T9."ItmsGrpCod"=T7."ItmsGrpCod"
WHERE T1."CANCELED" ='N' and T5."LineTotal"<>0 and T9."ItmsGrpNam" IS NOT NULL AND T9."U_OSTYPECOMM" = 'Pre'AND CASE WHEN EXTRACT(MONTH FROM T1."DocDate") BETWEEN 01 and 09 
AND EXTRACT(YEAR FROM T1."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE)
THEN 1
WHEN EXTRACT(MONTH FROM T1."DocDate") BETWEEN 10 AND 12
AND EXTRACT(YEAR FROM T1."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN 1
ELSE 0
END = 1

UNION

SELECT 'N' "Exercice",'Facture' "Type de doc",T0."DocNum",T0."DocDate", T0."CardCode", T0."CardName", T2."SlpName",T6."ItemCode",T6."ItmsGrpCod", T8."ItmsGrpNam",T4."LineNum",0 "CA HT Récurrent",0 "CA HT Prestation",CASE 
WHEN coalesce(T0."DiscPrcnt",0) = 0 then T4."LineTotal"
ELSE T4."LineTotal" -  (T4."LineTotal" *  T0."DiscPrcnt"/100)
END "CA HT One Shot" 
FROM OINV T0  
LEFT JOIN OSLP T2 ON T2."SlpCode" = T0."SlpCode"
LEFT JOIN INV1 T4 ON T4."DocEntry"=T0."DocEntry"
LEFT JOIN OITM T6 ON T6."ItemCode"=T4."ItemCode"
LEFT JOIN OITB T8 ON T8."ItmsGrpCod"=T6."ItmsGrpCod"
WHERE T0."CANCELED" = 'N'and T4."LineTotal"<>0 and T8."ItmsGrpNam" IS NOT NULL AND T8."U_OSTYPECOMM" = 'Neg' AND CASE WHEN EXTRACT(MONTH FROM T0."DocDate") BETWEEN 01 and 09 
AND EXTRACT(YEAR FROM T0."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE)
THEN 1
WHEN EXTRACT(MONTH FROM T0."DocDate") BETWEEN 10 AND 12
AND EXTRACT(YEAR FROM T0."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN 1
ELSE 0
END = 1
 

UNION

SELECT  'N' "Exercice",'Avoir' "Type de doc", T1."DocNum", T1."DocDate", T1."CardCode", T1."CardName",T3."SlpName",T7."ItemCode",T7."ItmsGrpCod",T9."ItmsGrpNam",T5."LineNum",0 "CA HT Récurrent",0 "CA HT Prestation",CASE 
WHEN coalesce(T1."DiscPrcnt",0) = 0 then -T5."LineTotal"
ELSE -(T5."LineTotal" -  (T5."LineTotal" *  T1."DiscPrcnt" /100))
END "CA HT One Shot"
FROM ORIN T1 
LEFT JOIN OSLP T3 ON T3."SlpCode"=T1."SlpCode"
LEFT JOIN RIN1 T5 ON T5."DocEntry"=T1."DocEntry"
LEFT JOIN OITM T7 ON T7."ItemCode"=T5."ItemCode"
LEFT JOIN OITB T9 ON T9."ItmsGrpCod"=T7."ItmsGrpCod"
WHERE T1."CANCELED" ='N' and T5."LineTotal"<>0 and T9."ItmsGrpNam" IS NOT NULL AND T9."U_OSTYPECOMM" = 'Neg' AND CASE WHEN EXTRACT(MONTH FROM T1."DocDate") BETWEEN 01 and 09 
AND EXTRACT(YEAR FROM T1."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE)
THEN 1
WHEN EXTRACT(MONTH FROM T1."DocDate") BETWEEN 10 AND 12
AND EXTRACT(YEAR FROM T1."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN 1
ELSE 0
END = 1

) "DATA"

GROUP BY "DATA"."Exercice","DATA"."Type de doc","DATA"."DocNum","DATA"."DocDate", "DATA"."CardCode", "DATA"."CardName", "DATA"."SlpName","DATA"."ItemCode","DATA"."ItmsGrpCod", "DATA"."ItmsGrpNam","DATA"."LineNum"
ORDER BY "DATA"."DocDate"

**escartefigue** · 03/05/2019, 11h09

Même réponse que dans l'autre sujet : vos restrictions sur les colonnes des tables OUTER transforment les jointures en INNER JOIN

Si vos deux ensembles sont distincts, remplacez UNION par UNION ALL, ce sera plus rapide

De plus, un peu de mise en forme serait bienvenue pour faciliter la lecture et donc la compréhension de la requête.
Par exemple :

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
SELECT  "DATA"."Exercice"
      , "DATA"."Type de doc"
      , "DATA"."DocNum"
      , "DATA"."DocDate"
      , "DATA"."CardCode" 
      , "DATA"."CardName"
      , "DATA"."SlpName"
      , "DATA"."ItemCode"
      , "DATA"."ItmsGrpCod"
      , "DATA"."ItmsGrpNam"
      , "DATA"."LineNum"
      , SUM("DATA"."CA HT Récurrent")
      , SUM("DATA"."CA HT Prestation")
      , SUM("DATA"."CA HT One Shot")
FROM(SELECT  'N-1' "Exercice"
           , 'Facture' "Type de doc"
           , T0."DocNum"
           , T0."DocDate"
           , T0."CardCode"
           , T0."CardName"
           , T2."SlpName"
           , T6."ItemCode"
           , T6."ItmsGrpCod"
           , T8."ItmsGrpNam"
           , T4."LineNum"
           , CASE WHEN coalesce(T0."DiscPrcnt",0) = 0 
                  then T4."LineTotal"
                  ELSE T4."LineTotal" -  (T4."LineTotal" *  T0."DiscPrcnt"/100)
             END "CA HT Récurrent"
           , 0 "CA HT Prestation"
           , 0 "CA HT One Shot" 
     FROM OINV T0  
     LEFT JOIN OSLP T2 
       ON T2."SlpCode" = T0."SlpCode"
     LEFT JOIN INV1 T4 
       ON T4."DocEntry"=T0."DocEntry"
     LEFT JOIN OITM T6 
       ON T6."ItemCode"=T4."ItemCode"
     LEFT JOIN OITB T8 
       ON T8."ItmsGrpCod"=T6."ItmsGrpCod"
     WHERE T0."CANCELED" = 'N'  
       and T4."LineTotal"<>0 
       and T8."ItmsGrpNam" IS NOT NULL 
       AND T8."U_OSTYPECOMM" = 'Rec'
       AND CASE WHEN EXTRACT(MONTH FROM T0."DocDate") BETWEEN 01 and 09 
                 AND EXTRACT(YEAR FROM T0."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 1
                THEN 1
                WHEN EXTRACT(MONTH FROM T0."DocDate") BETWEEN 10 AND 12
                 AND EXTRACT(YEAR FROM T0."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 2
                THEN 1
                ELSE 0
           END = 1
 
     UNION ...

**iKeyyZ** · 03/05/2019, 11h11

Ca ce n'est pas grave, ce que je souhaiterai c'est que à la place que le N-1 soit entre le 01.10.2017 au 30.09.2018 soit entre le 01.10.2017 à la date du jour N-1

**escartefigue** · 03/05/2019, 11h20

Envoyé par iKeyyZ

Ca ce n'est pas grave, ce que je souhaiterai c'est que à la place que le N-1 soit entre le 01.10.2017 au 30.09.2018 soit entre le 01.10.2017 à la date du jour N-1

vous pouvez répéter la question

**iKeyyZ** · 03/05/2019, 11h23

Actuellement ma condition sur la requête affiche les résultats compris entre le 01.10.2017 et le 30.09.2018 (Pour N-1). Ce que je souhaiterai c'est que la condition affiche les résultats compris entre le 01.10.2017 et la date du jour - 1an (par exemple pour aujourd'hui entre le 01.10.2017 et le 03.05.2018)

**escartefigue** · 03/05/2019, 11h43

Selon le SGBD, la syntaxe varie, mais ce serait donc quelque chose comme

WHERE ma_colonne_date BETWEEN '2017-10-01' and current_date - 1 year - 1 days

**iKeyyZ** · 03/05/2019, 11h50

Enfaite la date d'une année est compris entre le 01.10.année au 30.09.année et non pas du 01.01.année au 31.12.année. C'est pour cela que dans la requête il y a la moitié des sous requêtes pour l'année N c'est à dire du 01.10.2018 au 30.09.2019 mais qui affiche jusqu'à la date du jour ce qui est logique. J'aimerai donc faire un comparatif avec l'année N-1 c'est à dire du 01.10.2017 au 30.09.2018 mais j'aimerai que la requête affiche les résultats de la requête entre le 01.10.2017 à la date du jour actuelle de l'année dernière et ainsi de suite. c'est pour cela que dans ta réponse je ne peux pas mettre

Code :

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

 BETWEEN '2017-10-01'

car lorsque nous serons arrivé au 01.10.2019 il faudra alors que ça affiche celle de l'année N actuelle qui sera donc N-1 lorsque nous serons le 01.10.2019.

**escartefigue** · 03/05/2019, 12h52

Bien sur, mais vous pouvez remplacer cette valeur en dur par

select cast(extract(year from current date - 2 year) !! '-10-01' as date)

Code à adapter en fonction de votre SGBD

**iKeyyZ** · 03/05/2019, 13h04

Ce n'est pas possible de remédier à ce problème en passant par la ?

Code :

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

1
2
3
4
5
6
7
8
WHERE T1."CANCELED" ='N' and T5."LineTotal"<>0 and T9."ItmsGrpNam" IS NOT NULL AND T9."U_OSTYPECOMM" = 'Neg' AND CASE WHEN EXTRACT(MONTH FROM T1."DocDate") BETWEEN 01 and 09 
AND EXTRACT(YEAR FROM T1."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN 1
WHEN EXTRACT(MONTH FROM T1."DocDate") BETWEEN 10 AND 12
AND EXTRACT(YEAR FROM T1."DocDate") = EXTRACT(YEAR FROM CURRENT_DATE) - 2
THEN 1
ELSE 0
END = 1

**escartefigue** · 03/05/2019, 13h17

Mes réponses précédentes correspondent à ce besoin :

Envoyé par iKeyyZ

mais j'aimerai que la requête affiche les résultats de la requête entre le 01.10.2017 à la date du jour actuelle de l'année dernière et ainsi de suite. c'est pour cela que dans ta réponse je ne peux pas mettre

Code :

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

 BETWEEN '2017-10-01'

car lorsque nous serons arrivé au 01.10.2019 il faudra alors que ça affiche celle de l'année N actuelle qui sera donc N-1 lorsque nous serons le 01.10.2019.

Insérez ma solution (combinaison de mes deux réponses précédentes) dans le WHERE de votre requête en adaptant -2 et -1 selon l'année à filtrer

ORDER BY date du jour n-1

Langage SQL

Discussions similaires

Partager

Partager