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
|
CREATE TABLE #TMP_Flux
( [col1] varchar(50)
, col2varchar(50)
, [col3] varchar(50)
, [col4] varchar(50)
, [col5] varchar(50)
, [col6] varchar(50)
, [col7] int)
-- TMP Insertion
INSERT INTO #TMP_Flux (col1,col2,col3,col4, col5, col6, col7)
SELECT col1,col2,col3,col4, col5, col6, col7
FROM OPENXML (@idoc, '/root/row', 2)
WITH
(
col1 varchar(50) '@val1',
col2 varchar(50) '@val2',
col3 varchar(50) '@val3',
col4 varchar(50) '@val4' ,
col5 varchar(50) '@val5',
col6 varchar(50) '@val6',
col7 int '@val7'
) as inputTable
-- TMP Index Creation
CREATE NONCLUSTERED INDEX IX_TMP_Flux_2
ON #TMP_Flux ([col5],col4)
-- Selection of cashflow
SELECT DISTINCT
C.c1
, C.c2
, C.c3
, EC.c1
, C.c4
, C.c5
, C.c6
, inputTable.col1
, inputTable.col2
, ET.c1
FROM #TMP_Flux as inputTable
LEFT JOIN t1 CW WITH(NOLOCK) ON CW.c5 = inputTable.c5 AND CW.c4 = inputTable.c4 AND CW.c6 = inputTable.c6
LEFT JOIN t2 T ON T.c3 = CW.c3
LEFT JOIN t3 ET WITH (NOLOCK) ON ET.TradeId = T.Id
LEFT JOIN t4 C WITH (NOLOCK)ON C.TradeId = T.Id
LEFT JOIN t5 EC WITH (NOLOCK) ON EC.CId = C.Id
LEFT JOIN t6 B WITH(NOLOCK) ON B.CId = C.Id AND B.SId = inputTable.c4
where inputTable.c2 = '' AND CW.CType = 'Portfolio'
AND ET.RunId = @CId
AND ET.Gop = inputTable.c5
AND EC.RunId = @CId
AND C.c3 IN ('BAU','STATIC')
AND C.CType NOT LIKE '%winding' AND C.CCode = inputTable.CCurr
AND B.Id is not null |
