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
|
declare @CodeProduit varchar (20)
declare @Label varchar (150)
declare @Devise char (3)
declare @DateVL datetime
declare @COURS Varchar(30)
declare @NAV Varchar(50)
declare @Typ Varchar (1)
declare @Isin varchar (20)
set @isin = '?'
IF OBJECT_ID(N'tempdb..#tblResultat', N'U') IS NOT NULL
drop table #tblResultat
Create table #tblResultat (
[id] int IDENTITY (1,1) Primary Key,
[Libelle] [varchar](150) NOT NULL,
[Valeur] [varchar](150) NULL)
declare VL_cursor cursor for
select
codeproduit as CodeProduit,
Label as Label ,
Devise as Devise,
date_cours as DateVL,
case when SPREAD <> 0 then cast(cast(round(SPREAD,2) as money) as varchar(30)) else cast(cast(round(Cours,2) as money) as varchar(30)) end as COURS,
cast(cast(round(NAV,2) as money) as Varchar(50))as NAV,
strType as Typ
from #Cours
order by codeproduit ,
date_cours
open VL_cursor
fetch next from VL_cursor
into @CodeProduit, @Label, @Devise, @DateVL,@COURS, @NAV
set @Isin = '?'
while @@FETCH_STATUS = 0
begin
if @Isin <> @CodeProduit begin --Les 4 premières lignes d'insertion
insert into #tblSSIS (Libelle,Valeur) values ('CODE', @CodeProduit)
insert into #tblSSIS (Libelle,Valeur) values ('LABEL', @Label)
insert into #tblSSIS (Libelle,Valeur) values ('CUR', @Devise)
insert into #tblSSIS (Libelle,Valeur) values ('TYPE', @Typ)
--insert into #tblSSIS (Libelle,Valeur) values ( 'NAV', convert(numeric,@NAV))
If @NAV <> '0.00' begin
insert into #tblSSIS (Libelle,Valeur) values ( 'NAV', convert(numeric,@NAV))
end
end --de la première insertion
set @Isin = @CodeProduit -- insertion des dates et des cours aux dites dates
insert into #tblSSIS (Libelle,Valeur) values (case when day(@DateVL) < 10 then ('0' + cast(day(@DateVL) as char(1))) else cast(day(@DateVL) as char(2))end
+
'/'
+
case when month(@DateVL) < 10 then ('0' + cast(month(@DateVL) as char(1))) else cast(month(@DateVL) as char(2)) end
+
'/'
+
cast(year(@DateVL) as char(4))
,case when @COURS = '0.00' then '' else @COURS end)
fetch next from Cours_cursor
into @CodeProduit, @Label, @Devise, @DateVL, @COURS, @NAV , @Typ
end
close Cours_cursor
deallocate Cours_cursor
SELECT
Libelle ,
Valeur
from #tblSSIS
order by id
IF OBJECT_ID(N'tempdb..#tblResultat', N'U') IS NOT NULL
drop table #tblResultat |
