Lookup values of Static Table code list to dynamic table fetch result column

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

image

Thank you for the consumable test data.
It is not clear exactly what you want.
Ignoring the StartDate, the following will inefficiently produce your results by casting to strings:

WITH JoinConds
AS
(
SELECT D.StartDate, D.Column1, D.Column3, D.ItemName, X.Calculate
	,CAST(CAST(X.Calculate * 10000 AS int) AS varchar(20)) AS JoinCond
FROM #DynamicData D
	CROSS APPLY( VALUES( ROUND(D.Column1 * 0.001, 2) ) ) X (Calculate)
)
SELECT C.StartDate, C.Column1, C.Column3, C.ItemName, C.Calculate
	,S.Column2 AS Result
FROM JoinConds C
	JOIN #StaticData S
		ON C.ItemName = S.ItemName
			AND C.JoinCond LIKE CAST(S.Column1 AS varchar(20)) + '%';
1 Like

The math doesn't make sense. How can 14125 be compared to 1413, yet 1885 be compared with 1890? Your calculation show 14.13 and then 1.890. If we multiply by 100 we get 1413 and 189, so what are we comparing?

Extermely Sorry ! :zipper_mouth_face: :thinking:

Please find the revised #Dynamicdata to be matched with #Staticdata table.

Drop table #StaticData
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

Drop table #DynamicData
Create table #DynamicData (StartDate date,
				Column1 int,
				Column3 varchar(10),
				ItemName varchar(10))

insert into #DynamicData values
('3/1/2020', 1413 ,'mm', 'Item1'),
('3/1/2020', 1890,'mm', 'Item2'),
('3/1/2020', 1920 ,'mm', 'Item3'),
('4/1/2020', 1263 ,'mm', 'Item1'),
('4/1/2020', 1480 ,'mm', 'Item2'),
('4/1/2020', 2121, 'mm', 'Item3')


Select * from #DynamicData

seems like a straight join. I don't know why you were multiplying by .001 and rounding in your original post

select d.*, s.Column2
  from #DynamicData d
	join #StaticData s
		on d.Itemname = s.Itemname
		and d.Column1 = s.Column1
  1. Sir i was thinking that the #Dynamicdata value 14125 last two digits changed to nearest value which will be 3 = 1413 then matched with #Staticdata value 1413 ,same case for value 1885 changed last two integer with 90 =1890 will be matched with #staticdata value 1890

2.Sir the issue was that the static data was in mm (storage unit) and dynamic data storage is mm . but matching required upto two decimal points... if we multiple Round(14125*0.001,2) will give the results to 14.13 in meters and the statictable value is 1413 here my bad doesnt consider cannot be matched even multiple by 100.

I have no idea if the point 1 can handle ? other wise ask vendor to provide the data every last two integers changed to the nearest value.

are you rounding always to 4 digits?

Yes sir...

it's ugly, not very efficient and I still don't understand the logic behind it, but it returns what you want.

Select d.* , s.Column2
 from #StaticData s
	join (Select *, [Calculate]=cast(left(Cast(Round(column1*0.001,2)  * 1000 as varchar(10)),4) as int)
			from #DynamicData) d
		on s.ItemName = d.ItemName
		and s.Column1 = d.Calculate
2 Likes

mike01 and Ifor Thank you!