SQL Server Performance Forum – Threads Archive
Compare Two rows
Hi All I have got one table "Table1" with 58 fields. There are two columns "FileID" and "FileDataID". "FileDataID" is unique and it is primary key. Four entries has to be done for the same FileID. And if any row of 4 rows has different data for that particular column, pick that particular column and its correspond data.
Plz give me the reply ASAP. Thankyou Aruna Mathew
Hi, Can you detail with one or two examples, as the question itself seems to be little complicated!!!
Aruna,
Please post T-SQL script and sample data with your requirment —————————————-
http://spaces.msn.com/members/dineshasanka
Table Name : FileData<br />Columns : FileDataID, FileID, Pro_Number, Invoice_Number, Customer_Reference1, <br /> Data_Entry_ID, Verifier_ID.<br />Primary Key: FileDataID<br />Foreign Key: FileID<br />Data: First Data Entry Operator enters the data, next it is checked by Quality Checker, next it is check by Quality Analyst.<br /><br />FileDataID FileID Pro_NumberInvoice_NumberCustomer_Reference1UserID<br /><br />(1) (1002) (P90087)(551209) (1809 W FRANKFLOR)(Saritha)<br />(2) (1002) (P90089)(551209) (1809 W FRANKFLOUR)(James)<br />(5) (1120) (P09967)(553489) (NO – NOMAD) (Hari)<br />(6) (1120) (P09967)(553489) (NO – NOMAD) (Anand)<br />(7) (1120) (P09967)(55348<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> (NO – NOMAD) (Sarala)<br /><br />Now in all entries of FileID 1002 and 1120, pick the column where the column data is not same for all the rows.<br />ex:- <br /><br />Output 1<br /><br />FileID:1002, DEUserID<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />aritha, QCUserID:James<br />FieldName DEValue QCValue QAValue FinalValue<br />(Pro_Number) (P90087) (P90089) (P90089)<br />(Customer_Reference1) (1809 W FRANKFLOR) (1809 W FRANKFLOUR) (1809 W FRANKFLOUR)<br /><br />Output 2<br /><br />FileID:1120, DEUserID:Hari, QCUserID:Anand, QAUserID<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />arala<br />FieldName DEValue QCValue QAValue FinalValue<br />(Invoice_Number) (553489) (553489) (55348<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> (55348<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br /><br /><br /><br /><br /><br /><hr noshade size="1">
Here is how to get no 4 same entries for each FileID. You can modify the query from here to get what you want. select *
from FileData
where FileID in (
Select FileID
from FileData
group by
FileID, Pro_Number, Invoice_Number, Customer_Reference1,
Data_Entry_ID, Verifier_ID
having count(*) < 4 )
order by FileID
]]>