Hi,
i have a Master table like below
create table #MasterTable (ID float, CValue varchar(10), LimitFrom float, LimitTo float)
insert into #MasterTable values (84450, 'ABC', 1, 200)
insert into #MasterTable values (84451, 'DFE', 12, 20)
insert into #MasterTable values (82040, 'XYZ', 2.5, 6.5)
insert into #MasterTable values (82565, 'AS', 42.1, 62.5)
insert into #MasterTable values (82565, 'RBS', 1, 50)
select * from #MasterTable
drop table #MasterTable
and TxnTable like below
create table #TxnTable (ID float, FormatString NVARCHAR(100), GivenDate Date, Comment NVARCHAR(10))
insert into #TxnTable values (84450, ';T=Result CValue="ABC" Value="181" U="06/03/2021";', '2021-03-06 00:00:00.000', '');
insert into #TxnTable values (84451, 'NULL', '2021-03-06 00:00:00.000', '');
insert into #TxnTable values (82040, ';T=Result CValue="XYZ" Value="4.6" U="02/03/2021";', '2021-03-02 00:00:00.000', '');
insert into #TxnTable values (83036, ';T=Result CValue="A1C" Value="7.2" U="06/03/2021";', '2021-03-02 00:00:00.000', '');
insert into #TxnTable values (82565, ';T=Result CValue="CR" Value="3.2" U="06/03/2021";', '2021-03-06 00:00:00.000', '');
insert into #TxnTable values (1234, ';T=Result CValue="CR" Value="3.2" U="06/03/2021";', '2021-03-06 00:00:00.000', '');
insert into #TxnTable values (82947, ';T=Result CValue="RBS" Value="98" U="05/03/2021";', '2021-03-05 00:00:00.000', '');
select * from #TxnTable
drop table #TxnTable
i need to update the TxnTable Comment as 'Matched' or 'MisMatched' based on below condition
In TxnTable FormatString 'CValue' should match with master table 'Value'
In TxnTable FormatString 'Value' should be between Master Table 'LimitFrom' and 'LimitTo' Column
In TxnTable FormatString 'U' Date should match with TxnTable 'GivenDate' Column
if the above condition met update comment as 'Matched' else update 'MisMatched'
what is the best way to write query this situation?
Thanks in Advance
