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
Hi,
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
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 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
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
; 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 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
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