Requête ou vue avec variables

Version imprimable

Bonjour,

Je crée une vue afin de stocker les soldes comptables mensuelles des comptes pour les utiliser ultérieurement dans des états Crystal Reports.
Le script que j'utilise ressemble à ceci :

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
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
/****** 2021-01 ******/
SELECT '2021' AS ANNEE, 5 EXO, 1 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2021-01-01'
UNION
/****** 2021-02 ******/
SELECT '2021' AS ANNEE, 5 EXO, 2 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2021-01-02'
UNION
/****** 2021-03 ******/
SELECT '2021' AS ANNEE, 5 EXO, 3 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2021-01-03'
UNION
/****** 2021-04 ******/
SELECT '2021' AS ANNEE, 5 EXO, 4 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2021-01-04'
UNION
/****** 2021-05 ******/
SELECT '2021' AS ANNEE, 5 EXO, 5 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
  INNER JOIN [BDD].[SCHEMA].[GACCOUNT] T2 ON T1.ACC_0=T2.ACC_0 
  WHERE COMPTE_0='MACRITERE' AND DATECRITURE_0<'2021-01-05' 
 
UNION
/****** 2021-06 ******/
SELECT '2021' AS ANNEE, 5 EXO, 6 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2021-01-06'
UNION
/****** 2021-07 ******/
SELECT '2021' AS ANNEE, 5 EXO, 7 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2021-01-07'
UNION
/****** 2021-08 ******/
SELECT '2021' AS ANNEE, 5 EXO, 8 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2021-01-08'
UNION
/****** 2021-09 ******/
SELECT '2021' AS ANNEE, 5 EXO, 9 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2021-01-09'
UNION
/****** 2021-10 ******/
SELECT '2021' AS ANNEE, 5 EXO, 10 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2021-01-10'
UNION
/****** 2021-11 ******/
SELECT '2021' AS ANNEE, 5 EXO, 11 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2021-01-11'
UNION
/****** 2021-12 ******/
SELECT '2021' AS ANNEE, 5 EXO, 12 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2021-01-12'
 
  UNION
 
/****** 2022-01 ******/
SELECT '2022' AS ANNEE, 6 EXO, 1 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2022-01-01'
UNION
/****** 2022-02 ******/
SELECT '2022' AS ANNEE, 6 EXO, 2 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2022-01-02'
UNION
/****** 2022-03 ******/
SELECT '2022' AS ANNEE, 6 EXO, 3 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2022-01-03'
UNION
/****** 2022-04 ******/
SELECT '2022' AS ANNEE, 6 EXO, 4 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2022-01-04'
UNION
/****** 2022-05 ******/
SELECT '2022' AS ANNEE, 6 EXO, 5 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
  INNER JOIN [BDD].[SCHEMA].[GACCOUNT] T2 ON T1.ACC_0=T2.ACC_0 
  WHERE COMPTE_0='MACRITERE' AND DATECRITURE_0<'2022-01-05' 
 
UNION
/****** 2022-06 ******/
SELECT '2022' AS ANNEE, 6 EXO, 6 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2022-01-06'
UNION
/****** 2022-07 ******/
SELECT '2022' AS ANNEE, 6 EXO, 7 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2022-01-07'
UNION
/****** 2022-08 ******/
SELECT '2022' AS ANNEE, 6 EXO, 8 AS PERIODE
SUM(T1.MONTANT) AS SOLDEBLED
  FROM [BDD].[SCHEMA].[ECRITURE] 
WHERE  COMPTE_0='MACRITERE' AND DATECRITURE_0<'2022-01-08'

Je pense qu'il est possible d'utiliser l'année 2021 et 2022 en tant que variable et les dates remplacées par une fonction "Début du mois".

Pourriez-vous m'indiquer comment faire SVP ?

10/01/2022, 14h54
tatayo
Bonjour,
Je ne sais pas comment tu calcules la colonne EXO, mais pour le reste tu peux extraire la donnée directement de l'année:
Code:

1 2 3 4 5 SELECT datepart(year,dateecriture_0) AS ANNEE, 5 as EXO, datepart(month,dateecriture_0) AS PERIODE SUM(T1.MONTANT) AS SOLDEBLED FROM [BDD].[SCHEMA].[ECRITURE] WHERE COMPTE_0='MACRITERE'
Il ne reste plus qu'à déterminer la règle de calcul de EXO (datepart(year,dateecriture_0) - 2016 ?).

Tatayo.
10/01/2022, 15h24
James Daniel
Merci pour ce retour,

En fait, le plus gros souci c'est de réduire les lignes de code.

Serait-il possible d'envisager une boucle pour la PERIODE, et utiliser une fonction début du mois ?

Citation:

au lieu d'utiliser <'2021-01-02'
ensuite au lieu d'utiliser <'2021-01-03'
au lieu d'utiliser <'2021-01-04'
Code:

1 2 /* une boucle du genre for i=1 to 12*/
10/01/2022, 16h10
tatayo

Je ne comprends pas pourquoi tu veux passer par une boucle.
Si les colonnes ANNEE et PERIODE sont calculées, tu n'as pas besoin de boucle.
Si tu ne ceux qu'une plage de date, il suffit ici de filtrer non pas dans la vue, mais lors de son interrogation.

Ou alors il nous manque une donnée ici, ou je n'ai pas bien compris la demande.

Tatayo.
11/01/2022, 06h27
James Daniel

Rebonjour,

La condition de la date est "inférieure". Pour le solde de 31/05/2021 par exemple, il faudrait parcourir toutes les écritures depuis le début jusqu'au 31/05/2021. Si on suppose que le début des écritures dans la base est au 01/03/2019, alors il faut parcourir depuis 01/03/2019 jusqu'à 31/05/2021.

Prendre par période pour une année n'est pas bon car il ne prend que les mouvements de 01/05/2021 au 31/05/2021 si l'on considère l'hypothèse ci-dessus.

En prenant une boucle, on aurait, pour les écritures avec condition "<" à la date indiquée :
- < 01/03/2021 = solde à fin février 2021 = début mars 2021
- < 01/04/2021 = solde à fin mars 2021 = début avril 2021
- < 01/05/2021 = solde à fin avril 2021 = début mai 2021
- < 01/06/2021 = solde à fin mai 2021 = début juin 2021
et ainsi de suite

C'est dans ce sens que j'ai pensé à une fonction "début du mois" ou "fin de mois".
11/01/2022, 08h57
tatayo
Citation:

Envoyé par James Daniel

En prenant une boucle, on aurait, pour les écritures avec condition "<" à la date indiquée :
- < 01/03/2021 = solde à fin février 2021 = début mars 2021
- < 01/04/2021 = solde à fin mars 2021 = début avril 2021
- < 01/05/2021 = solde à fin avril 2021 = début mai 2021
- < 01/06/2021 = solde à fin mai 2021 = début juin 2021

Sauf que dans ton exemple, la condition que tu nous donnes ne correspond pas à ce que tu veux.
< 01/06/2021 va prendre toutes les lignes dont la date est antérieure au 01/06/2021, donc les mois de juin, mai, avril... Il faut prendre une date min ET une date max pour avoir une période, ou filtrer sur l'année et le mois.

Comme je le disais, tu n'as pas besoin d'une boucle.
La vue va calculer toutes les colonnes, pour toutes les dates.
Si tu veux une période particulière, il suffit de borner la requête lors de son interrogation.
Par exemple pour avoir le mois de mai 2021:
Code:

1 2 3 4 select * from LaVue where ANNEE = 2021 and periode = 6
Et si tu veux toute l'année 2021:
Code:

1 2 3 4 select * from LaVue where ANNEE = 2021
Si tu ajoutes une colonne avec la date dans la vue, tu peux borner sur une période de plusieurs mois, à cheval sur 2 année:
Code:

1 2 3 4 select * from LaVue where LaDate between '2021/08/01' and '2022/07/01'
Tatayo.
11/01/2022, 09h55
James Daniel

Rebonjour,

Citation:

Je crée une vue afin de stocker les soldes comptables mensuelles des comptes pour les utiliser ultérieurement dans des états Crystal Reports.

J'ai déjà essayé une requête utilisant uniquement l'année et le mois, mais le solde n'est pas bon : c'est le solde de chaque période (année + mois)
Alors que la formule du solde, c'est de faire la somme d'un compte depuis la première écriture.

Le résultat attendu sera un solde mensuel. En prenant l'hypothèse que la première écriture date du 01/03/2019.
Le résultat attendu devra avoir 35 lignes d'enregistrements :
- Pour 2019 : 10 lignes (mars à décembre)
- Pour 2020 : 12 lignes
- Pour 2021 : 12 lignes
- Pour 2022 : 01 ligne (janvier)

Faites une somme cumulative :

Code:

1
2
3
4
5
6
7
8
9
10
  select COMPTE_0
       , dateadd(MONTH, datediff(MONTH, 0, dateecriture_0), 0) as mois_ecriture
       , sum(MONTANT) as solde_mensuel
       , sum(sum(MONTANT)) over(partition by COMPTE_0
                                    order by dateadd(MONTH, datediff(MONTH, 0, dateecriture_0), 0) asc
                                        rows between unbounded preceding and current row
                               ) as solde_global
    from [BDD].[SCHEMA].[ECRITURE]
group by COMPTE_0
       , dateadd(MONTH, datediff(MONTH, 0, dateecriture_0), 0)