· 8 years ago · Feb 21, 2018, 06:16 PM
1with PolicyDtls as
2( select
3 MasterPolicyNumber,
4 NewRenewal,
5 LimitUSD,
6 LimitTransactionalCurrency,
7 AttachmentType, TransactionalCurrency,
8 ISNULL(AttachmentUSD, 0) + ISNULL(SIRUSD,0) + ISNULL(DeductibleUSD,0) as AttachmentUSD,
9 ISNULL(AttachmentTransactionalCurrency, 0) + ISNULL(SIRTransactionalCurrency,0) + ISNULL(DeductibleTransactionalCurrency,0) as AttachmentTransactional
10
11 from [LimitAttachment_TransCurrency]
12
13 )
14
15
16 select
17 ref.Region,
18 curr.MasterPolicyNumber,
19 previous.MasterPolicyNumber as PriorMasterPolicyNumber,
20 curr.TransactionalCurrency,
21 curr.LimitUSD,
22 previous.LimitUSD as prevLimitUSD,
23 curr.AttachmentUSD,
24 previous.AttachmentUSD as prevAttachUSD,
25 curr.LimitTransactionalCurrency,
26 previous.LimitTransactionalCurrency as prevLimitTransactional,
27 curr.AttachmentTransactional,
28 previous.AttachmentTransactional as prevAttachTrans
29
30
31
32 from Renewal_XREF ref
33 left join PolicyDtls curr on curr.MasterPolicyNumber = ref.MasterPolicyNumber
34 left join PolicyDtls previous on previous.MasterPolicyNumber = ref.Prior_MasterPolicyNumber
35
36 order by ref.SubmissionNumber
37
38--===== If the test table already exists, drop it
39 IF OBJECT_ID('TempDB..#mytable') IS NOT NULL
40 DROP TABLE #mytable
41
42 --===== Create the test table with
43 CREATE TABLE #mytable
44 (
45 Region nvarchar(300),
46 MasterPolicyNumber nvarchar(300),
47 PriorPolicyNumber nvarchar(300),
48 TransactionalCurrency nvarchar(100),
49 LimitUSD float,
50 prevLimitUSD float,
51 AttachmentUSD float,
52 prevAttachUSD float,
53 LimitTransCurrency float,
54 prevLimitTrans float,
55 AttachTrans float,
56 prevAttachTrans float
57
58 )
59
60 SET DATEFORMAT DMY
61
62 --===== Insert the test data into the test table
63 INSERT INTO #mytable (Region, MasterPolicyNumber, PriorPolicyNumber, TransactionalCurrency, LimitUSD, prevLimitUSD, AttachmentUSD, prevAttachUSD, LimitTransCurrency, prevLimitTrans,AttachTrans, prevAttachTrans )
64
65
66 SELECT 'Australasia', '47-ZEN-004336-02', '47-ZEN-004336-01', 'AUD', '45751200', '45706200', '1906.3', '1904425', '60000000', '60000000', '2500', '2500000' UNION ALL
67 SELECT 'Australasia', '47-ZEN-004336-02', '47-ZEN-004336-01', 'AUD', '45751200', '20246095.67', '1906.3', '0', '60000000', '20246095.67', '2500', '0' UNION ALL
68 SELECT 'Australasia', '47-ZEN-004336-02', '47-ZEN-004336-01', 'AUD', '45751200', '104813.45', '1906.3', '0', '60000000', '98618.25', '2500', '0' UNION ALL
69 SELECT 'Australasia', '47-ZEN-004336-02', '47-ZEN-004336-01', 'USD', '10184230.59', '45706200', '0', '1904425', '10184230.59', '60000000', '0', '2500000' UNION ALL
70 SELECT 'Australasia', '47-ZEN-004336-02', '47-ZEN-004336-01', 'USD', '10184230.59', '20246095.67', '0' , '0', '10184230.59', '20246095.67', '0', '0' UNION ALL
71 SELECT 'Australasia', '47-ZEN-004336-02', '47-ZEN-004336-01', 'USD', '10184230.59', '104813.45', '0', '0', '10184230.59', '98618.25', '0', '0' UNION ALL
72 SELECT 'Australasia', '47-ZEN-004336-02', '47-ZEN-004336-01', 'EUR', '1674061.66', '45706200', '0', '1904425', '1425704.02', '60000000', '0', '2500000' UNION ALL
73 SELECT 'Australasia', '47-ZEN-004336-02', '47-ZEN-004336-01', 'EUR', '1674061.66', '20246095.67', '0', '0' , '1425704.02', '20246095.67', '0', '0' UNION ALL
74 SELECT 'Australasia', '47-ZEN-004336-02', '47-ZEN-004336-01', 'EUR', '1674061.66', '104813.45', '0', '0', '1425704.02', '98618.25', '0', '0'
75
76
77 select *from #mytable
78
79--===== If the test table already exists, drop it
80 IF OBJECT_ID('TempDB..#mytable') IS NOT NULL
81 DROP TABLE #mytable
82
83 --===== Create the test table with
84 CREATE TABLE #mytable
85 (
86 Region nvarchar(300),
87 MasterPolicyNumber nvarchar(300),
88 PriorPolicyNumber nvarchar(300),
89 TransactionalCurrency nvarchar(100),
90 LimitUSD float,
91 prevLimitUSD float,
92 AttachmentUSD float,
93 prevAttachUSD float,
94 LimitTransCurrency float,
95 prevLimitTrans float,
96 AttachTrans float,
97 prevAttachTrans float
98
99 )
100
101 SET DATEFORMAT DMY
102
103 --===== Insert the test data into the test table
104 INSERT INTO #mytable (Region, MasterPolicyNumber, PriorPolicyNumber, TransactionalCurrency, LimitUSD, prevLimitUSD, AttachmentUSD, prevAttachUSD, LimitTransCurrency, prevLimitTrans,AttachTrans, prevAttachTrans )
105
106
107 SELECT 'Australasia', '47-ZEN-004336-02', '47-ZEN-004336-01', 'AUD', '45751200', '45706200', '1906.3', '1904425', '60000000', '60000000', '2500', '2500000' UNION ALL
108 SELECT 'Australasia', '47-ZEN-004336-02', '47-ZEN-004336-01', 'USD', '10184230.59', '20246095.67', '0' , '0', '10184230.59', '20246095.67', '0', '0' UNION ALL
109
110 SELECT 'Australasia', '47-ZEN-004336-02', '47-ZEN-004336-01', 'EUR', '1674061.66', '104813.45', '0', '0', '1425704.02', '98618.25', '0', '0'
111
112
113 select *from #mytable