Get nearest value(TABLE2) for a value in TABLE1

Hi,



From TABLE1 i need to compare M column values with M1,M2,M3,M4,M5,M6 in TABLE2 by STUDENTDEPARTMENT wise and need to get nearest value and display the columnname of that nearest value.
i need the results that are in FINALRESULTTABLE

Please provide proper ddl and dml
Create table xyz
Insert into xyz and all.

Help us help you

Thanks harish.But i cannot do union all for 100M's.In my original database i have 100 M's.

Even if you have 100 M's
You can use scripting to generate a script
Of union all of 100 M's

Another thing is to put all M's
Into a temp table with a single row
For each M ....
Then see my script to join with ..temp table

Cursor is another option

I will try to think of a straight forward way

Any experts. .please help

:slightly_smiling_face::slightly_smiling_face::+1::+1:

Dont know how to generate script for union all of 100 M's

hi mannesravya

here i am doing looping ... while @counter < 7 ... this is becuase there are only 6M's
you can change this to .....while @counter < 101 ... becuase you have 100 M's

drop create data ...
drop table #table1 
 go 

 create table #table1 
(
department  varchar(100) , 
M1  decimal(10,2) , 
M2  decimal(10,2) , 
M3  decimal(10,2) , 
M4  decimal(10,2) , 
M5  decimal(10,2) , 
M6  decimal(10,2)  
)
go 

insert into #table1 select 'IT', 2.34,3.86,4.95,5.88,6.99,8.10
 insert into #table1 select 'CSE',1.22,2.51,6.77,7.90,8.71,9.10
 insert into #table1 select 'MEC',3.22,4.87,5.91,5.92,8.73,9.88
 insert into #table1 select 'BIO',1.99,2.78,3.93,5.66,7.88,9.91
 go 

drop table #table2 
go 


create table #table2 
(
StudentDepartment  varchar(100) , 
StudentName  varchar(100) , 
StudentIdentity  int , 
M  decimal(10,2) 
)
go 


 

 insert into #table2 select 'IT','PARUL',1,5.26
 insert into #table2 select 'IT','PUSPA',2,4.34
 insert into #table2 select 'CSE','KAVITHA',3,3.22
 insert into #table2 select 'CSE','RANI',4,1.18
 insert into #table2 select 'MEC','LAXMI',5,1.17
 insert into #table2 select 'MEC','DEVI',6,2.30
 insert into #table2 select 'MEC','PRAVALIKA',7,2.51
 insert into #table2 select 'BIO','RAHUL',8,8.86
 insert into #table2 select 'BIO','SAHITHI',9,9.45 
  go 

select 'data' , * from #table1  
go 

select 'data', * from #table2
go
Script to populate M's .....
DROP TABLE #temp123 

go 

CREATE TABLE #temp123 
  ( 
     department VARCHAR(100), 
     m          VARCHAR(100), 
     mvalue     DECIMAL(10, 2) 
  ) 

go 

DECLARE @counter INT = 1 
DECLARE @sql VARCHAR(100) = '' 

WHILE @counter < 7 
  BEGIN 
      SET @sql = 'insert into #temp123 select department,''M' 
                 + Cast(@counter AS VARCHAR(10)) + ''',m' 
                 + Cast(@counter AS VARCHAR) + ' from #table1' 

      EXEC( @sql) 

      SET @sql = '' 
      SET @counter = @counter + 1 
  END 

go

 SELECT * 
FROM   #temp123 

go

SQL ....
; WITH cte_ok 
     AS (SELECT a.department, 
                b.studentname, 
                b.studentidentity, 
                Min(Abs(b.m - c.mvalue)) AS diff 
         FROM   #table1 a 
                JOIN #table2 b 
                  ON a.department = b.studentdepartment 
                JOIN #temp123 c 
                  ON a.department = c.department 
         GROUP  BY a.department, 
                   b.studentname, 
                   b.studentidentity), 
     cte_final 
     AS (SELECT a.department, 
                b.studentname, 
                b.studentidentity, 
                Abs(b.m - c.mvalue) AS diff, 
                c.m, 
                c.mvalue 
         FROM   #table1 a 
                JOIN #table2 b 
                  ON a.department = b.studentdepartment 
                JOIN #temp123 c 
                  ON a.department = c.department) 
SELECT 'my output', 
       a.department, 
       a.studentname, 
       a.studentidentity, 
       c.m      AS M, 
       a.mvalue AS NearestValue, 
       a.m      AS MColumn 
FROM   cte_final a 
       JOIN cte_ok b 
         ON a.department = b.department 
            AND a.studentidentity = b.studentidentity 
            AND a.diff = b.diff 
       JOIN #table2 c 
         ON a.studentidentity = c.studentidentity 
ORDER  BY a.studentidentity 

go

Your results aren't consistent. I can't figure out the match logic you are using.

could you please explain what you mean by not consistent !! thank you

i am trying diff
1 2 3 4

M = 3.3

then 1-3.3 = - 2.3
2 - 3.3 = - 1.3
3 - 3.3 = .3
4 - 3.3 = .7

abs value = 2.3 , 1.3 , .3 , .7

out of these min = .3 so the corresponding M is nearest ...

My comment was to the original poster.

Their results don't match your results because sometimes they match to a higher value and sometimes to a lower value.

@harishgg1: note that your results don't match the original posted "expected results" for the reason that the original post uses inconsistent matching. I can't tell what the exact rules are for matching.

Yup Scott

I noticed that too

I thought that it might be a mistake
From the original poster

Hi mannesravya

If you don't like recursion
They are called ... Common table expressions
In SQL server tsql ..

One option
Is create temp tables
And join the temp tables. .

Directly t SQL means
I will try ..
Does not look easy. . .

+++++++++++++++++++++++
if you want to do this temp tables way

drop create data .. and temp tables
DROP TABLE #table1 

go 

CREATE TABLE #table1 
  ( 
     department VARCHAR(100), 
     m1         DECIMAL(10, 2), 
     m2         DECIMAL(10, 2), 
     m3         DECIMAL(10, 2), 
     m4         DECIMAL(10, 2), 
     m5         DECIMAL(10, 2), 
     m6         DECIMAL(10, 2) 
  ) 

go 

INSERT INTO #table1 
SELECT 'IT', 
       2.34, 
       3.86, 
       4.95, 
       5.88, 
       6.99, 
       8.10 

INSERT INTO #table1 
SELECT 'CSE', 
       1.22, 
       2.51, 
       6.77, 
       7.90, 
       8.71, 
       9.10 

INSERT INTO #table1 
SELECT 'MEC', 
       3.22, 
       4.87, 
       5.91, 
       5.92, 
       8.73, 
       9.88 

INSERT INTO #table1 
SELECT 'BIO', 
       1.99, 
       2.78, 
       3.93, 
       5.66, 
       7.88, 
       9.91 

go 

DROP TABLE #temp123 

go 

CREATE TABLE #temp123 
  ( 
     department VARCHAR(100), 
     m          VARCHAR(100), 
     mvalue     DECIMAL(10, 2) 
  ) 

go 

DECLARE @counter INT = 1 
DECLARE @sql VARCHAR(100) = '' 

WHILE @counter < 7 
  BEGIN 
      SET @sql = 'insert into #temp123 select department,''M' 
                 + Cast(@counter AS VARCHAR(10)) + ''',m' 
                 + Cast(@counter AS VARCHAR) + ' from #table1' 

      EXEC( @sql) 

      SET @sql = '' 
      SET @counter = @counter + 1 
  END 

go 

DROP TABLE #table2 

go 

CREATE TABLE #table2 
  ( 
     studentdepartment VARCHAR(100), 
     studentname       VARCHAR(100), 
     studentidentity   INT, 
     m                 DECIMAL(10, 2) 
  ) 

go 

INSERT INTO #table2 
SELECT 'IT', 
       'PARUL', 
       1, 
       5.26 

INSERT INTO #table2 
SELECT 'IT', 
       'PUSPA', 
       2, 
       4.34 

INSERT INTO #table2 
SELECT 'CSE', 
       'KAVITHA', 
       3, 
       3.22 

INSERT INTO #table2 
SELECT 'CSE', 
       'RANI', 
       4, 
       1.18 

INSERT INTO #table2 
SELECT 'MEC', 
       'LAXMI', 
       5, 
       1.17 

INSERT INTO #table2 
SELECT 'MEC', 
       'DEVI', 
       6, 
       2.30 

INSERT INTO #table2 
SELECT 'MEC', 
       'PRAVALIKA', 
       7, 
       2.51 

INSERT INTO #table2 
SELECT 'BIO', 
       'RAHUL', 
       8, 
       8.86 

INSERT INTO #table2 
SELECT 'BIO', 
       'SAHITHI', 
       9, 
       9.45 

go 

SELECT 'data', 
       * 
FROM   #table1 

go 

SELECT 'data', 
       * 
FROM   #table2 

go 

DROP TABLE #cte_ok 

go 

DROP TABLE #cte_final 

go 

SELECT a.department, 
       b.studentname, 
       b.studentidentity, 
       Min(Abs(b.m - c.mvalue)) AS diff 
INTO   #cte_ok 
FROM   #table1 a 
       JOIN #table2 b 
         ON a.department = b.studentdepartment 
       JOIN #temp123 c 
         ON a.department = c.department 
GROUP  BY a.department, 
          b.studentname, 
          b.studentidentity 

SELECT a.department, 
       b.studentname, 
       b.studentidentity, 
       Abs(b.m - c.mvalue) AS diff, 
       c.m, 
       c.mvalue 
INTO   #cte_final 
FROM   #table1 a 
       JOIN #table2 b 
         ON a.department = b.studentdepartment 
       JOIN #temp123 c 
         ON a.department = c.department
SQL ...
SELECT 'my output', 
       a.department, 
       a.studentname, 
       a.studentidentity, 
       c.m      AS M, 
       a.mvalue AS NearestValue, 
       a.m      AS MColumn 
FROM   #cte_final a 
       JOIN #cte_ok b 
         ON a.department = b.department 
            AND a.studentidentity = b.studentidentity 
            AND a.diff = b.diff 
       JOIN #table2 c 
         ON a.studentidentity = c.studentidentity 
ORDER  BY a.studentidentity 

go

Used loops to solve this issue