· 8 years ago · Feb 20, 2018, 04:02 PM
1with PolicyDtls as
2 ( select
3 MasterPolicyNumber,
4 PolicyNumber,
5 NewRenewal,
6 TransactionalCurrency,
7 LimitUSD,
8 AttachmentType,
9 ISNULL(AttachmentUSD, 0) + ISNULL(SIR_USD,0) + ISNULL(DeductibleUSD,0) as AttachmentUSD
10 from Policy_Test
11 )
12
13
14 select
15 ref.Region,
16 ref.MasterPolicyNumber,
17 previous.MasterPolicyNumber as PriorMasterPolicyNumber,
18 curr.TransactionalCurrency,
19 curr.LimitUSD,
20 previous.LimitUSD as prevLimitUSD,
21 curr.AttachmentUSD,
22 previous.AttachmentUSD as prevAttachUSD,
23 case when curr.LimitUSD = previous.LimitUSD and curr.AttachmentUSD = previous.AttachmentUSD then 'Renewed Same Layer'
24 when (curr.LimitUSD <> previous.LimitUSD OR curr.AttachmentUSD <> previous.AttachmentUSD )
25 and previous.AttachmentUSD IS NOT NULL
26 then 'Renewed Different Layer'
27 else 'Renewed Unknown'
28 end as 'Renewal Layer',
29 curr.AttachmentType
30
31 from Actuarial.Rptng.Renewal_XREF ref
32 left join PolicyDtls curr on curr.MasterPolicyNumber = ref.MasterPolicyNumber
33 left join PolicyDtls previous on previous.MasterPolicyNumber = ref.Prior_MasterPolicyNumber
34 where
35 curr.NewRenewal = 'Renewal'
36 and curr.MasterPolicyNumber = '49-NPR-000013-02'
37 order by curr.PolicyNumber
38
39--===== If the test table already exists, drop it
40IF OBJECT_ID('TempDB..#mytable') IS NOT NULL
41 DROP TABLE #mytable
42
43--===== Create the test table with
44CREATE TABLE #mytable
45(
46 Region nvarchar(300),
47 MasterPolicyNumber nvarchar(300),
48 PriorPolicyNumber nvarchar(300),
49 TransactionalCurrency nvarchar(100),
50 LimitUSD float,
51 prevLimitUSD float,
52 AttachmentUSD float,
53 prevAttachUSD float,
54 RenewedLayer nvarchar(300),
55 AttachmentType nvarchar(100)
56)
57
58SET DATEFORMAT DMY
59
60--===== Insert the test data into the test table
61INSERT INTO #mytable (Region, MasterPolicyNumber, PriorPolicyNumber, TransactionalCurrency, LimitUSD, prevLimitUSD, AttachmentUSD, prevAttachUSD, RenewedLayer, AttachmentType)
62 SELECT 'EUR', '47-ACA-000001-02', '47-ACA-000001-01', 'EUR', '7105.8', '6218.6', '32763', '23273', 'Renewed Differed Layer', 'Excess'
63
64
65 select *from #mytable
66
67--===== If the test table already exists, drop it
68IF OBJECT_ID('TempDB..#TransDetails') IS NOT NULL
69 DROP TABLE #TransDetails
70
71--===== Create the test table with
72CREATE TABLE #TransDetails
73(
74 Region nvarchar(300),
75 MasterPolicyNumber nvarchar(300),
76 PriorPolicyNumber nvarchar(300),
77 TransactionalCurrency nvarchar(100),
78 LimitTrans float,
79 prevLimitTrans float,
80 AttachmentTrans float,
81 prevAttachTrans float,
82 RenewedLayer nvarchar(300),
83 AttachmentType nvarchar(100)
84)
85
86SET DATEFORMAT DMY
87
88--===== Insert the test data into the test table
89INSERT INTO #TransDetails(Region, MasterPolicyNumber, PriorPolicyNumber, TransactionalCurrency, LimitTrans, prevLimitTrans, AttachmentTrans, prevAttachTrans, RenewedLayer, AttachmentType)
90 SELECT 'EUR', '47-ACA-000001-02', '47-ACA-000001-01', 'EUR', '8000', '8000', '3000', '3000', 'Renewed Same Layer', 'Excess'
91
92
93 select *from #TransDetails
94
95--===== If the test table already exists, drop it
96IF OBJECT_ID('TempDB..#mytable') IS NOT NULL
97 DROP TABLE #mytable
98
99--===== Create the test table with
100CREATE TABLE #mytable
101(
102 Region nvarchar(300),
103 MasterPolicyNumber nvarchar(300),
104 PriorPolicyNumber nvarchar(300),
105 TransactionalCurrency nvarchar(100),
106 LimitUSD float,
107 prevLimitUSD float,
108 AttachmentUSD float,
109 prevAttachUSD float,
110 RenewedLayer nvarchar(300),
111 AttachmentType nvarchar(100)
112)
113
114SET DATEFORMAT DMY
115
116--===== Insert the test data into the test table
117INSERT INTO #mytable (Region, MasterPolicyNumber, PriorPolicyNumber, TransactionalCurrency, LimitUSD, prevLimitUSD, AttachmentUSD, prevAttachUSD, RenewedLayer, AttachmentType)
118 SELECT 'EUR', '47-ACA-000001-02', '47-ACA-000001-01', 'EUR', '7105.8', '6218.6', '32763', '23273', 'Renewed Same Layer', 'Excess'
119
120
121 select *from #mytable