I work on sql server 2014 I face issue I can't get only one row based on partid and compliance type id
and document type
so if have partid as 3581935 and compliance type id 1 and document type
Web Page OR Coc OR Contact
then first priority will be Web Page
second priority Coc
third priority Contact
so my sample as below :
create table FinalTableData(PartId int,Regulation nvarchar(300),
Comp_Status nvarchar(100),REVID int,Doc_Type nvarchar(20),
Document_Type int,ComplianceTypeID int)
insert into FinalTableData values(35819351,'RoHS (2015/863)','Compliant with Exemption',340434330,'Contact',1362938,1),
(35819351,'RoHS (2015/863)','Compliant',288530768,'Web Page',1232162,1),
(35819351,'RoHS (2015/863)','NotCompliant',288539070,'Coc',1232160,1),
(35819351,'REACH 2021 (219)','Compliant',340434330,'Contact',1362938,2),
(35819351,'TSCA','Compliant',340434352,'CoC',1232160,11),
(35819351,'TSCA','Compliant',340434330,'Contact',1362938,11)
my expected result :
| PartId | Regulation | Comp_Status | REVID | Doc_Type | Document_Type | ComplianceTypeID |
| 35819351 | RoHS (2015/863) | Compliant | 288530768 | Web Page | 1232162 | 1 |
| 35819351 | REACH 2021 (219) | Compliant | 340434330 | Contact | 1362938 | 2 |
| 35819351 | TSCA | Compliant | 340434352 | CoC | 1232160 | 11 |
Muhammad Imran AnsariPosted Jan 10, 2022, 8:32 PM