How to show first row group by part id and compliance type based on priorities of Document type?

Question

I work on SQL Server 2014 and I face an issue - I can't get only one row based on part id and compliance type id and document type.

If I 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

My sample is here:

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)  
What I tried:  
  
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  

Expected result :

Answer 1

Hi,@ahmed salah
Below is the demo data you provide:

  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),  

The Row_Number of 'Web Page' is 2 in the demo data ,which does not meet your priority requirements.
So you may need to re-rank Row_number ,as I answered in your last thread:

;WITH CTE AS  
(  
 SELECT PartId,Regulation,Comp_Status,REVID ,Doc_Type,Document_Type,ComplianceTypeID,  
 ROW_NUMBER()OVER(PARTITION BY ComplianceTypeID ORDER BY CHARINDEX(Doc_Type+ ', ','Web Page,Coc,Contact, ')) AS Row_Number   
 FROM #FinalTableData  
)SELECT  ...  
 FROM   #TempTable MD  
 LEFT OUTER JOIN   
         CTE Rohs ON MD.PartID = Rohs.PartID   
                             AND Rohs.ComplianceTypeID = 1   
                             AND Rohs.Row_Number = 1  
 LEFT OUTER JOIN   
         CTE Reach ON MD.PartID = Reach.PartID   
                              AND Reach.ComplianceTypeID = 2   
                              AND Reach.Row_Number = 1  
 LEFT OUTER JOIN   
         CTE TSKA ON MD.PartID = TSKA.PartID   
                             AND TSKA.ComplianceTypeID = 11   
                             AND TSKA.Row_Number = 1  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.