Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Связка таблиц по нескольким полям для проставления необходимого флага  [new]
AVRomanenko
Member

Откуда:
Сообщений: 75
Здравствуйте. Имеется две таблицы.
В одной таблице хранится информация по клиентам (около 30 полей). Вторая таблица состоит из полей (5 полей, которые есть в первой таблице), в которых прописаны условия, по которым необходимо проставить один из флагов(6 поле из таблицы 2) в первую таблицу.


структура таблицы 1:
LOAN_CL EFF_DT DT_CH_PRC BASE_CCY_F SIMILAR_F
Депозитный договор 19.03.2008 01.01.1900 1 N
Кредитный договор 22.03.2006 01.01.1900 0 N
Кредитный договор 17.11.2019 01.01.1900 1 Y


структура таблицы 2: (знаки >= и < условия для значений соответствующих полей из таблицы 1, символ # означает логическое НЕ)
LOAN_CLEFF_DTDT_CH_PRCBASE_CCY_FSIMILAR_FRVSR
Кредитный договор>=2019-03-011YY
Кредитный договор>=2019-03-011YY
Кредитный договор<2019-03-01<2019-03-011YN
Кредитный договор#YN
Кредитный договор0N


помогите, пожалуйста, разработать корректный алгоритм, который будет сравнивать все условия и выставлять в первую таблицу в конце соответствующий флаг из 6го поля 2й таблицы.

своей фантазии хватило только на такой вариант, но он некорректно отрабатывает. очевидно, даже алгоритм сравнения не очень правильный.
select
case
when 
CR_SA.LOAN_CL = RVSR.LOAN_CL 
and (CR_SA.EFF_DT >= cast(replace(replace(replace(RVSR.EFF_DT, '>',''), '<',''),'=','') as date) 
or CR_SA.DT_CH_PRC >= cast(replace(replace(replace(RVSR.DT_CH_PRC, '>',''), '<',''),'=','') as date)) 
and CR_SA.BASE_CCY_F = RVSR.BASE_CCY_F 
and CR_SA.SIMILAR_F = RVSR.SIMILAR_F 
then 'Y' else 'N' end RVSR_F

from table_1 CR_SA

left join table_2 RVSR
on  CR_SA.LOAN_CL = RVSR.LOAN_CL 
and (CR_SA.EFF_DT >= cast(replace(replace(replace(RVSR.EFF_DT, '>',''), '<',''),'=','') as date)
or CR_SA.DT_CH_PRC >= cast(replace(replace(replace(RVSR.DT_CH_PRC, '>',''), '<',''),'=','') as date))
and CR_SA.BASE_CCY_F = RVSR.BASE_CCY_F
and CR_SA.SIMILAR_F = RVSR.SIMILAR_F


так же в case пробовал для полей с датами искать по первым двум символам вместо сравнения
left(CR_SA.EFF_DT, 2) = '>='

но тоже никакого толка.
26 июн 19, 17:31    [21915730]     Ответить | Цитировать Сообщить модератору
 Re: Связка таблиц по нескольким полям для проставления необходимого флага  [new]
iap
Member

Откуда: Москва
Сообщений: 46838
Без кавычек 2019-03-01 = 2015, и это целое число, а не дата.
Преобразование в дату этого числа даст 9 июля 1905 года.

Заключайте даты в одиночные кавычки.
26 июн 19, 18:16    [21915749]     Ответить | Цитировать Сообщить модератору
 Re: Связка таблиц по нескольким полям для проставления необходимого флага  [new]
AVRomanenko
Member

Откуда:
Сообщений: 75
iap
Без кавычек 2019-03-01 = 2015, и это целое число, а не дата.
Преобразование в дату этого числа даст 9 июля 1905 года.

Заключайте даты в одиночные кавычки.


С этим нет никаких проблем, строка в дату конвертируется корректно.
27 июн 19, 09:04    [21915922]     Ответить | Цитировать Сообщить модератору
 Re: Связка таблиц по нескольким полям для проставления необходимого флага  [new]
court
Member

Откуда:
Сообщений: 1784
AVRomanenko,

посмотри здесь 13088610
имхо, лучший выход
27 июн 19, 09:38    [21915942]     Ответить | Цитировать Сообщить модератору
 Re: Связка таблиц по нескольким полям для проставления необходимого флага  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29049
AVRomanenko
В одной таблице хранится информация по клиентам (около 30 полей).
А тип EFF_DT какой, date, или строка?

AVRomanenko
строка в дату конвертируется корректно.
Вы это проверяли? Я бы всё таки использовал CONVERT, и явное указание формата

AVRomanenko
условия, по которым необходимо проставить один из флагов(6 поле из таблицы 2) в первую таблицу.
Нужно проставить поле 6 из таблицы 2 в таблицу 1, но вы зачем то вычисляете RVSR_F.

AVRomanenko
своей фантазии хватило только на такой вариант, но он некорректно отрабатывает. очевидно, даже алгоритм сравнения не очень правильный.
В нём даже не учитываются условия >, <, и т.д.

Я бы сделал подзапрос из table_2, в котором разложил бы поля EFF_DT и DT_CH_PRC на даты и условия
Это и отладить легко, как отдельный запрос.

А потом уже сделал джойн этого подзапроса с table_1, со связкой типа
(RVSR.EFF_DT_condition = '>' and CR_SA.EFF_DT > RVSR.EFF_DT_date)
or
(RVSR.EFF_DT_condition = '>=' and CR_SA.EFF_DT >= RVSR.EFF_DT_date)
or
(RVSR.EFF_DT_condition = '<' and CR_SA.EFF_DT < RVSR.EFF_DT_date)
or
...
Писать лень, запрос получится на несколько страниц :-)
27 июн 19, 09:51    [21915949]     Ответить | Цитировать Сообщить модератору
 Re: Связка таблиц по нескольким полям для проставления необходимого флага  [new]
court
Member

Откуда:
Сообщений: 1784
court
AVRomanenko,

посмотри здесь 13088610
имхо, лучший выход

т.е. что имеется в виду: вот эту табличку
AVRomanenko
структура таблицы 2: (знаки >= и < условия для значений соответствующих полей из таблицы 1, символ # означает логическое НЕ)
LOAN_CLEFF_DTDT_CH_PRCBASE_CCY_FSIMILAR_FRVSR
Кредитный договор>=2019-03-011YY
Кредитный договор>=2019-03-011YY
Кредитный договор<2019-03-01<2019-03-011YN
Кредитный договор#YN
Кредитный договор0N

нужно привести к такому виду (в СТЕ например), и затем джойнить с таб1, - EFF_DT и EFF_DT по between, остальное по like
LOAN_CLEFF_DT_minEFF_DT_maxDT_CH_PRC_minDT_CH_PRC_maxBASE_CCY_FSIMILAR_FRVSR
Кредитный договор2019-03-013000-01-011900-01-013000-01-011YY
Кредитный договор1900-01-013000-01-011900-01-013000-01-011YY
Кредитный договор1900-01-012019-03-011900-01-012019-03-011YN
Кредитный договор1900-01-013000-01-011900-01-013000-01-01%[^Y]N
Кредитный договор1900-01-013000-01-011900-01-013000-01-010%N
27 июн 19, 09:55    [21915952]     Ответить | Цитировать Сообщить модератору
 Re: Связка таблиц по нескольким полям для проставления необходимого флага  [new]
invm
Member

Откуда: Москва
Сообщений: 8750
AVRomanenko,

+
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;
27 июн 19, 10:22    [21915973]     Ответить | Цитировать Сообщить модератору
 Re: Связка таблиц по нескольким полям для проставления необходимого флага  [new]
AVRomanenko
Member

Откуда:
Сообщений: 75
alexeyvg,
автор
Вы это проверяли? Я бы всё таки использовал CONVERT, и явное указание формата
да, проверял, все хорошо с этим.

автор
Нужно проставить поле 6 из таблицы 2 в таблицу 1, но вы зачем то вычисляете RVSR_F.

в таблице 2 6 поле - это флаг, который должен получиться, если все 5 условий удовлетворены.
если на примере исходных таблиц, то в первой таблице напротив 3й записи должен быть флаг Y (так как все 5 столбцов прошли условие), в остальных строках N.
автор
В нём даже не учитываются условия >, <, и т.д.

их не нужно учитывать.
27 июн 19, 10:33    [21915980]     Ответить | Цитировать Сообщить модератору
 Re: Связка таблиц по нескольким полям для проставления необходимого флага  [new]
AVRomanenko
Member

Откуда:
Сообщений: 75
invm,
Спасибо за скрипт!
Вы не правильно поняли, в таблицу 1 не надо делать UPDATE поля SIMILAR_F, а просто добавить в SELECT в конце флаг (если 5 полей таблицы 1 прошли 5 условий таблицы 2, то проставить флаг (а какой именно указан в 6м поле таблицы 2, этот флаг для наглядности). соответственно, в первой таблице должен быть Y напротив 3й строки, N напротив остальных строк).
27 июн 19, 10:37    [21915984]     Ответить | Цитировать Сообщить модератору
 Re: Связка таблиц по нескольким полям для проставления необходимого флага  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6711
AVRomanenko,

имо практичнее написать CLR функцию в такой ситуации.
27 июн 19, 12:06    [21916072]     Ответить | Цитировать Сообщить модератору
 Re: Связка таблиц по нескольким полям для проставления необходимого флага  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29049
AVRomanenko
автор
В нём даже не учитываются условия >, <, и т.д.

их не нужно учитывать.
Ээээ, тогдя я не понял условия задачи, что означает:
AVRomanenko
знаки >= и < условия для значений соответствующих полей из таблицы 1
27 июн 19, 17:12    [21916451]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить