Click here to Skip to main content
15,176,561 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Hi,

i have a Master table like below
SQL
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
SQL
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


What I have tried:

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?
Posted
Updated 22-Apr-21 1:12am
v2

1 solution

The best way would be to re-think your TxnTable and separate out the elements of FormatString into separate columns. If possible, don't do it in the first place, but if that is not possible you should create another temporary table along the lines of
SQL
create table #TxnTable2 (ID float, T nvarchar(10), CValue nvarchar(10), [Value] float, U date, GivenDate Date, Comment NVARCHAR(10))
If you have SQL Server 2016 or better then you can use the STRING_SPLIT[^] function along with the REPLACE[^] function (to remove the ;T=, CValue= and the quotation marks)

If you don't do that then your joins are going to end up looking like this ...
SQL
update a 
set Comment = 'Matched'
from #TxnTable a
INNER JOIN #MasterTable b on b.CValue = LEFT(SUBSTRING(FormatString, CHARINDEX('CValue="', FormatString) + 8, LEN(formatstring)), CHARINDEX('"',SUBSTRING(FormatString, CHARINDEX('CValue="', FormatString) + 8, LEN(formatstring))) - 1)
where CHARINDEX('CValue="', FormatString) > 0
I've only done one of them because it upsets me to do more.

The update becomes trivial after that. You can either set everything to MisMatched and then correct the matching entries e.g.
SQL
update #TxnTable2 set Comment = 'MisMatched'
update a 
set Comment = 'Matched'
from #TxnTable2 a
INNER JOIN #MasterTable b ON a.CValue =b.CValue
where a.[Value] between b.LimitFrom and b.LimitTo
and a.U = a.GivenDate
Or you can use a CASE statement e.g.
update a 
set Comment = CASE WHEN a.[Value] between b.LimitFrom and b.LimitTo AND a.U = a.GivenDate
				THEN 'Matched'
			  ELSE 'MisMatched' END
from #TxnTable2 a
INNER JOIN #MasterTable b ON a.CValue =b.CValue
It's largely down to personal preferences, I personally think the first version is easier to read.

Couple of other points,
- use NULL instead of 'NULL' when splitting out your values
- be consistent in your date formats and use ISO 8601 where possible. E.g. U="05/03/2021" should be either U="2021-03-05" or U="2021-05-03"
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900