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.
declare @t1 table (LOAN_CL varchar(30), EFF_DT date, DT_CH_PRC date, BASE_CCY_F bit, SIMILAR_F char(2));
insert into @t1
values
('Депозитный договор', convert(date, '19.03.2008', 104), convert(date, '01.01.1900', 104), 1, 'N'),
('Кредитный договор', convert(date, '22.03.2006', 104), convert(date, '01.01.1900', 104), 0, 'N'),
('Кредитный договор', convert(date, '17.11.2019', 104), convert(date, '01.01.1900', 104), 1, 'Y');
declare @t2 table (LOAN_CL varchar(30), EFF_DT varchar(30), DT_CH_PRC varchar(30), BASE_CCY_F bit, SIMILAR_F char(2), RVSR char(1));
insert into @t2
values
('Кредитный договор', '>=2019-03-01', null, 1, 'Y', 'Y'),
('Кредитный договор', '>=2019-03-01', null, 1, 'Y', 'Y'),
('Кредитный договор', '<2019-03-01', '<2019-03-01', 1, 'Y', 'N'),
('Кредитный договор', null, null, null,'#Y', 'N'),
('Кредитный договор', null, null, 0, null, 'N');
select * from @t1;
with a as
(
select
LOAN_CL,
SIMILAR_F,
cast('<cond ' + case
when charindex('>=', EFF_DT) > 0 then replace(EFF_DT, '>=', 'c = "ge">')
when charindex('<=', EFF_DT) > 0 then replace(EFF_DT, '<=', 'c = "le">')
when charindex('>', EFF_DT) > 0 then replace(EFF_DT, '>', 'c = "gt">')
when charindex('<', EFF_DT) > 0 then replace(EFF_DT, '<', 'c = "lt">')
else 'c = "eq">' + EFF_DT
end + '</cond>' as xml) as EFF_DT__cond,
cast('<cond ' + case
when charindex('>=', DT_CH_PRC) > 0 then replace(DT_CH_PRC, '>=', 'c = "ge">')
when charindex('<=', DT_CH_PRC) > 0 then replace(DT_CH_PRC, '<=', 'c = "le">')
when charindex('>', DT_CH_PRC) > 0 then replace(DT_CH_PRC, '>', 'c = "gt">')
when charindex('<', DT_CH_PRC) > 0 then replace(DT_CH_PRC, '<', 'c = "lt">')
else 'c = "eq">' + DT_CH_PRC
end + '</cond>' as xml) as DT_CH_PRC__cond
from
@t2
where
EFF_DT is not null or DT_CH_PRC is not null
),
b as
(
select
LOAN_CL,
SIMILAR_F,
EFF_DT__cond.value('cond[1]/@c', 'char(2)') as EFF_DT__cond,
try_cast(EFF_DT__cond.value('cond[1]', 'varchar(30)') as date) as EFF_DT__value,
DT_CH_PRC__cond.value('cond[1]/@c', 'char(2)') as DT_CH_PRC__cond,
try_cast(DT_CH_PRC__cond.value('cond[1]', 'varchar(30)') as date) as DT_CH_PRC__value
from
a
)
update t
set
SIMILAR_F = s.SIMILAR_F
from
@t1 t cross apply
(
select top (1)
b.SIMILAR_F
from
b
where
b.LOAN_CL = t.LOAN_CL and
(
b.EFF_DT__cond is null or
(b.EFF_DT__cond = 'ge' and t.EFF_DT >= b.EFF_DT__value) or
(b.EFF_DT__cond = 'le' and t.EFF_DT <= b.EFF_DT__value) or
(b.EFF_DT__cond = 'gt' and t.EFF_DT > b.EFF_DT__value) or
(b.EFF_DT__cond = 'lt' and t.EFF_DT < b.EFF_DT__value) or
(b.EFF_DT__cond = 'eq' and t.EFF_DT = b.EFF_DT__value)
) and
(
b.DT_CH_PRC__cond is null or
(b.DT_CH_PRC__cond = 'ge' and t.DT_CH_PRC >= b.DT_CH_PRC__value) or
(b.DT_CH_PRC__cond = 'le' and t.DT_CH_PRC <= b.DT_CH_PRC__value) or
(b.DT_CH_PRC__cond = 'gt' and t.DT_CH_PRC > b.DT_CH_PRC__value) or
(b.DT_CH_PRC__cond = 'lt' and t.DT_CH_PRC < b.DT_CH_PRC__value) or
(b.DT_CH_PRC__cond = 'eq' and t.DT_CH_PRC = b.DT_CH_PRC__value)
)
) s;
select * from @t1;
