Tuesday, October 5, 2010
Monday, October 4, 2010
Good SQL Tasks
I have two tables which contain the same records but those records are entered by different users and i want to calculate similairty between same records(by every comapring every single field) entered by different users, and if a field in one table is similar to corresponding field in the second table then i want to store 1 in a column in third table (Table_C).
For Example I have two tables Table_A and Table_B as below:
Table_A
----------------------
RecordID StudentID Dept BookID
1 123 CS 456
2 123 CS 345
3 223 TE 190
Table_B
----------------------
RecordID StudentID Dept BookID
1 123 CS 456
2 223 TE 345
3 223 TE 190
and i have another table Table_C in which is store the similarity between the similar fields in Table_A and Table_B. The Sturcutre of the table is as follows:
Table_C
----------------------
Sim_RecordID Sim_StudentID Sim_Dept SimBookID
1 1 1 1
1 0 0 1
1 1 1 1
Note: I want to comapre only those records in Table_A and Table_B where RecordID and StudentID are same in both tables. i.e. i want a query or simple stored procedure to compare all columns of Table_A with corresponding columns of Table_B where Table_A.RecorID = Table_B.RecordID and Table_A.StudentID = Table_B.StudentID and Store 1 if the fields are similar otherwise store 0 in Table_C in the corresponding field
For Example I have two tables Table_A and Table_B as below:
Table_A
----------------------
RecordID StudentID Dept BookID
1 123 CS 456
2 123 CS 345
3 223 TE 190
Table_B
----------------------
RecordID StudentID Dept BookID
1 123 CS 456
2 223 TE 345
3 223 TE 190
and i have another table Table_C in which is store the similarity between the similar fields in Table_A and Table_B. The Sturcutre of the table is as follows:
Table_C
----------------------
Sim_RecordID Sim_StudentID Sim_Dept SimBookID
1 1 1 1
1 0 0 1
1 1 1 1
Note: I want to comapre only those records in Table_A and Table_B where RecordID and StudentID are same in both tables. i.e. i want a query or simple stored procedure to compare all columns of Table_A with corresponding columns of Table_B where Table_A.RecorID = Table_B.RecordID and Table_A.StudentID = Table_B.StudentID and Store 1 if the fields are similar otherwise store 0 in Table_C in the corresponding field
Declare @vSQL varchar(max)
Declare @vCols varchar(max)
Create Table vTable1 (id int, StudentID int,
Dept varchar(10),BookID int)
Create Table vTable2 (id int, StudentID int,
Dept varchar(10),BookID int)
Insert into vTable1
Select 1,123,'CS',465 Union All
Select 2,123,'CS',345 Union All
Select 3,223,'TE',190
Insert into vTable2
Select 1,123,'CS',465 Union All
Select 2,223,'TE',345 Union All
Select 3,223,'TE',190
-- Get the column names from schema with case statements to get
--0 or 1 as result
-- Now, this will depend upon the columns of your actual tables
Select @vCols = Stuff((Select ',case when a.' + [name] +
' = b.'
+ [name] + ' then 1 else 0 end '
from sys.columns where Object_id = Object_id('vTable1')
for XML Path('')),1,1,'')
Select @vCols
-- Concatenate the @vCols with main sql
Set @vSQL = ' Select ' + @vCols + ' From vTable1 a
Inner Join vTable2 b on b.ID = a.ID '
Print @vSQL
Exec (@vSQL)
Drop table vTable1
Drop table vTable2
Subscribe to:
Posts
(
Atom
)