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 : 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
/****** 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 ?