question

kasimmohamed-6993 avatar image
0 Votes"
kasimmohamed-6993 asked MelissaMa-msft answered

Update column based on condition

Hi,

i have a Master table like below
90209-image.png
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
90227-image.png
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

sql-server-generalsql-server-transact-sql
image.png (8.5 KiB)
image.png (25.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered kasimmohamed-6993 commented

Hi @kasimmohamed-6993,

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:

90284-output.png
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.


output.png (12.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks alot mellisa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @kasimmohamed-6993,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.