0

I work on SQL server 2014 I face issue I can't get only one row

based on part id and compliance type id and document type .

so if have part id 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,
Row_Number int,
Regulation nvarchar(300),
Comp_Status  nvarchar(100),
REVID   int,
Doc_Type nvarchar(20),
Document_Type int,
ComplianceTypeID int
)
insert into FinalTableData

values
(35819351,1,'RoHS (2015/863)','Compliant with Exemption',340434330,'Contact',1362938,1),
(35819351,2,'RoHS (2015/863)','Compliant',288530768,'Web Page',1232162,1),
(35819351,3,'RoHS (2015/863)','NotCompliant',288539070,'Coc',1232160,1),
(35819351,1,'REACH 2021 (219)','Compliant',340434330,'Contact',1362938,2),
(35819351,1,'TSCA','Compliant',340434352,'CoC',1232160,11),
(35819351,2,'TSCA','Compliant',340434330,'Contact',1362938,11)

expected result

enter image description here

what I try

CREATE TABLE #TempTable
(
PartId int
)
insert into #TempTable(PartId)
select 35819351

SELECT 
md.partid,
rohs.ComplianceTypeID as RohsCompliance,
reach.ComplianceTypeID as reachCompliance,
    
            

           
            Rohs.Doc_Type AS Rohs_SourceType,     
            
            Reach.Comp_Status AS SVHCStatus,
            case when Rohs.Regulation like '%2015%' then Rohs.Comp_Status  else 'Unknown (Old Version Status)' end AS RohsRegulation,
            case when Reach.Regulation like '%219%' then Reach.Comp_Status  else 'Unknown (Old Version Status)' end AS ReachRegulation,
        
            Reach.Doc_Type AS Reach_SourceType,
            case when Reach.REVID is null then 9070 else Reach.REVID end  AS Reach_Revision_ID,
            case when TSKA.REVID is null then  7050 else TSKA.REVID end  AS TSKA_Revision_ID,
            TSKA.Comp_Status AS TSKAStatus ,
            TSKA.Doc_Type AS TSKA_SourceType 

        
        FROM #TempTable MD

        LEFT OUTER JOIN FinalTableData Rohs ON MD.PartID =Rohs.PartID AND Rohs.ComplianceTypeID=1 AND Rohs.Row_Number=1
        LEFT OUTER JOIN FinalTableData Reach ON MD.PartID =Reach.PartID AND Reach.ComplianceTypeID=2 AND Reach.Row_Number=1
        LEFT OUTER JOIN FinalTableData TSKA ON MD.PartID =TSKA.PartID AND TSKA.ComplianceTypeID=11 AND TSKA.Row_Number=1
Improve this question
6
  • How could the same part be both compliant and not compliant with the hazardous substance restriction (RoHS) at the same time? Or even compliant and compliant with exemption at the same time? Or is this some sort of history table and you want the latest status per part?
    – J.D.
    Jan 10, 2022 at 19:50
  • same part with same compliance and i will check if part have multi doument type conact and web page and coc i will select only one tha hve high priority as web page
    – ahmed elbarbary
    Jan 10, 2022 at 20:32
  • group by parid,compliancetypeid,and documenttype web page as first option then coc then contact so how to do that please
    – ahmed elbarbary
    Jan 10, 2022 at 20:41
  • So priority is based on Document_Type?
    – J.D.
    Jan 10, 2022 at 22:26
  • sorry for late yes
    – ahmed elbarbary
    Jan 11, 2022 at 6:19

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.