Update column based on condition

Question

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

1. In TxnTable FormatString 'CValue' should match with master table 'Value'
2. In TxnTable FormatString 'Value' should be between Master Table 'LimitFrom' and 'LimitTo' Column
3. 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

Answer 1

Hi @kasim mohamed ,

Welcome to Microsoft Q&A!

You could create one function as below:

CREATE FUNCTION [dbo].[fn_SplitString]   
(   
    @string     NVARCHAR(MAX),   
    @delimiter  CHAR(1)   
)   
RETURNS @output TABLE  
(  
    [Id]        INT,  
    [Start]     INT,  
    [End]       INT,  
    [Length]    INT,  
    [Data]      NVARCHAR(MAX)  
)   
BEGIN   
    DECLARE @count INT, @start INT, @end INT  
    SELECT  @count = 1, @end = 0,  
            @start = CHARINDEX(@delimiter, @string)  
    WHILE   @start > 0 BEGIN   
  
        SELECT  @end = CHARINDEX(@delimiter, @string, @start + 1)  
  
        INSERT INTO @output ([Id], [Start], [End], [Length], [Data])  
        VALUES (@count, @start, @end, @end - @start - 1,   
                SUBSTRING(@string, @start + 1, @end - @start - 1))   
  
        SELECT  @start = CHARINDEX(@delimiter, @string, @end + 1),   
                @count = @count + 1  
    END   
    RETURN   
END  

Then call this function as below:

 ;with cte as (  
 select A.ID,B.data,ROW_NUMBER() over (partition by a.id order by (select null)) rn from #TxnTable A   
 CROSS APPLY [dbo].[fn_SplitString](FormatString,'"') B)  
 ,cte1 as (select * from   
 (select id,data,rn from cte)s  
 pivot  
 (max(data) for rn in ([1],[2],[3])) p)  
 update a  
 set a.Comment=   
 case when b.cvalue=c.[1] and c.[2] between b.LimitFrom and b.limitto   
 and cast(a.givendate as date) =FORMAT(cast(c.[3] as date) ,'yyyy-dd-MM')  
 then 'Matched' else 'MisMatched' end  
 from #TxnTable a  
 left join #MasterTable b on a.id=b.id  
 left join cte1 c on c.id=a.id  

select * from #TxnTable  

Output:

If above is not working, please provide more sample data and expected output.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @kasim mohamed ,

Thanks for your confirmation.

Please also refer below without using function:

;with cte as (  
select *  
,CASE WHEN CHARINDEX('CValue=',FormatString)>0 THEN   
SUBSTRING(FormatString,CHARINDEX('CValue=',FormatString)+len('CValue="'),  
CHARINDEX('"',FormatString,CHARINDEX('CValue=',FormatString)+len('CValue="'))-(CHARINDEX('CValue=',FormatString)+len('CValue="')))   
ELSE null END CValue  
,CASE WHEN CHARINDEX(' Value=',FormatString)>0 THEN   
SUBSTRING(FormatString,CHARINDEX(' Value=',FormatString)+len(' Value="'),  
CHARINDEX('"',FormatString,CHARINDEX(' Value=',FormatString)+len(' Value="'))-(CHARINDEX(' Value=',FormatString)+len(' Value="')))   
ELSE null END Value,  
CASE WHEN CHARINDEX('U=',FormatString)>0 THEN   
SUBSTRING(FormatString,CHARINDEX('U=',FormatString)+len('U="'),  
CHARINDEX('"',FormatString,CHARINDEX('U=',FormatString)+len('U="'))-(CHARINDEX('U=',FormatString)+len('U="')))   
ELSE null END U  
from #TxnTable)  
update a  
set a.Comment=case when a.cvalue=b.CValue and a.Value between b.LimitFrom and b.limitto   
  and a.givendate =FORMAT(cast(a.U as date) ,'yyyy-dd-MM')  
  then 'Matched' else 'MisMatched' end  
from cte a  
left join #MasterTable b on a.ID=b.ID  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.