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

Question

question

ahmedsalah-1628 asked Jan 11, '22 LiHongMSFT-3908 answered Jan 12, '22

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

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 :

sql-server-general sql-server-transact-sql

image.png (4.6 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-01-12T06:33:30.113Z

LiHongMSFT-3908 answered Jan 12, '22

Hi,@ahmedsalah-1628
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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question