CODEWITHSUNDEEP
sqlsql-servercalculated-columns
Drafter-SQL
Drafter-SQL

Reputation: 51

SQL calculated field based of data in another table

I have two tables, Engineering table and Instrumentation table. In the Engineering table I have the columns and possible data below:

Tag                 |            Speed Control                       
PC-1234             |
ME-1235             |
BF-1236             |

In the Instrumentation Table I have the following columns and data

Function         |        Tag   
SC               |        1234    
SC               |        1235    
SC               |        1237

I want to automate the Speed Control column in the Engineering table to say Yes or No IF there is a line of data in the Instrumentation table with the function as SC and the Tag column have matching data with the number part of the Tag column in the Engineering table. So the results would like like the below:

Tag                 |            Speed Control                        
PC-1234             |             Yes    
ME-1235             |             Yes    
BF-1236             |             No

Please help with the best way to do this. Thanks in advance for any help.

Upvotes: 0

Views: 90

Answers (3)

B.Muthamizhselvi
B.Muthamizhselvi

Reputation: 642

Create a function and use a function in calculated columns

   create table instrumentation([Function]  varchar(200) null,  Tag varchar(200) null)  

  insert into instrumentation values('SC', '1234'),('SC', '1235'),('SC', '1237')


 create Function fn_Speed (@tag varchar(200))
 returns varchar(200)
 as
begin

declare @tagg varchar(200)= (select SUBSTRING(@tag, charindex('-', @tag)+1,10))
declare @result varchar(200)
--return  @tagg

If exists (
select  1 from instrumentation where tag =@tagg)
    select @result= 'True' 
else 
    select @result= 'False' 
return @result
end

  Create table engineering (tag varchar(200), Speed   as dbo.fn_Speed (tag) )

  insert into engineering(tag)values('PC-1234'),  ('ME-1235'), ('BF-1236')

Upvotes: 0

nbk
nbk

Reputation: 49410

as i don't know if the Tag column in engineers is always of the same format, i keep my query so that it can have similar design xxxxx-nnnnnnnnn with a minus between.

UPDATE [dbo].[Engineering] 
SET [Speed Control] =
CASE WHEN EXISTS ( SELECT 1 FROM [dbo].[Instrumentation] i WHERE RIGHT([dbo].[Engineering] .[tag],CHARINDEX('-', (REVERSE([dbo].[Engineering] .[tag]))) - 1) = CAST(i.[Tag] AS VARCHAR(10))) then 'YES' ELSE 'NO' END
WHERE [Speed Control]  IS NULL

result will be

enter image description here

Upvotes: 0

Charlieface
Charlieface

Reputation: 72501

You don't want a separate column in the Engineering table for this. You just need a view which you can query

CREATE VIEW EngineeringSpeedControl
AS

SELECT
  e.Tag,
  SpeedControl = CASE WHEN i.Tag IS NULL THEN 'No' ELSE 'Yes' END
FROM dbo.Engineering e
LEFT JOIN dbo.Instrumentation i
    ON i.Tag = RIGHT(e.Tag, LEN(e.Tag) - 3)
   AND i.[Function] = 'SC';

Unfortunately, due to the poor design of the tables, you need to muck around with string manipulation.

Ideally you would have the Engineering.Tag column split into separate parts, so you could just do a straight join

LEFT JOIN dbo.Instrumentation i
    ON i.Tag = e.Tag
   AND i.[Function] = 'SC';

Upvotes: 1

Related Questions