Hi Experts !
I have two tables one is StaticData (Static table data) and other is DynamicData(table)
1.Staticdata(table) having column1 which is the codelist and column2 is the results and itemname are the names.
2. DynamicData(table) having column1 or calculate column are the lookup values for staticdata(table) column1 (codelist values). suppose the value of codelist(staticdata table) 1413 column1, it look up the column of dynamicdata(table) value of column(calculate) 14.13 change to 1413 to match with staticdata column1 vaue 1413 and fetch the results = value 5 column(column2 staticdata table ) based on itemnames. The results screenshot is attached.
Create table #StaticData (StartDate date,
Column1 int,
Column2 int,
Column3 varchar(10),
ItemName varchar(10))
insert into #StaticData values
('1/1/2020', 1413 , 5,'mm', 'Item1'),
('1/1/2020', 1890 , 20,'mm', 'Item2'),
('1/1/2020', 1920 , 52 ,'mm', 'Item3'),
('1/1/2020', 1263 , 70 ,'mm', 'Item1'),
('1/1/2020', 1480 , 33 ,'mm', 'Item2'),
('1/1/2020', 1469, 66 ,'mm', 'Item3'),
('1/1/2020', 1770 , 700 ,'mm', 'Item1'),
('1/1/2020', 15121 , 330 ,'mm', 'Item2'),
('1/1/2020', 2121, 30 ,'mm', 'Item3')
Select * from #StaticData
Create table #DynamicData (StartDate date,
Column1 int,
Column3 varchar(10),
ItemName varchar(10))
insert into #DynamicData values
('3/1/2020', 14125 ,'mm', 'Item1'),
('3/1/2020', 1885 ,'mm', 'Item2'),
('3/1/2020', 1916 ,'mm', 'Item3'),
('4/1/2020', 12631 ,'mm', 'Item1'),
('4/1/2020', 14799 ,'mm', 'Item2'),
('4/1/2020', 14691, 'mm', 'Item3')
Select *, [Calculate]=Round(column1*0.001,2) from #DynamicData