How to get parts that not have same features count on table settings by code typ

Question

How to get parts that not have same features count on table settings by code typ

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
December 24, 2020 at 2:18 am

#3823701
How to get parts that not have same features count on table settings by codetypeid and PLID ?
I work on sql server 2012 . if face issue I can't get parts that not have all features on table settings
so I will count distinct features from table settings and compare it with count features per part
if count features per part on table part feature <>count distinct features on table settings by PLID and code type
then display it .
as example part 9010 have only one feature 15000160 from table settings and not have feature 15000171
then display it .
as example part 9041 have only one feature 15000171 from table settings and not have feature 15000160
then display it .
part id 7731 have two features 15000171,15000160 as part settings have two features then no need
to display it because it have full features
```
so How to write query do that

 create table #settings
 (
 SettingId int,
 PLID  INT,
 CodeType int,
 Code nvarchar(50),
 featureKey int
 )
 insert into #settings 
 values 
 (1,2890,798541,'Ear99',NULL),
 (1,2890,798541,'Ear70',15000160),
 (1,2890,798541,'Ear99',15000171),
 (1,2890,798541,'e900841',15000160)
    
 create table #partFeature
 (
 PartId int,
 FeatureKey int
 )
 insert into #partFeature 
 values
 (9010,15000160),
 (7070,15000171) ,
 (9041,15000171) ,
 (2030,15000160) ,
 (5871,15000160) ,
 (5871,15000171) ,
 (7731,15000160) ,
 (7731,15000171)

result I need

 PartId    FeatureKey
 9010    15000160
 7070    15000171
 9041    15000171
 2030    15000160
```

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24675 More actions · Answer 1

I see multiple ways to handle this. My approach would be to count the number of unique feature keys in the #settings table and store that in a variable so you don't need to look it up multiple times.

Next, you use a CTE or nested select to grab the distinct partID and FeatureKey from partFeature, as well as a count of distinct feature keys. Something along the lines of:

WITH [cte]
AS
(
SELECT
[datas].[PartId]
, [datas].[FeatureKey]
, COUNT(1) OVER (PARTITION BY
 [datas].[PartId]
) AS [featureCount]
 FROM
(
SELECTDISTINCT
[PartId]
, [FeatureKey]
 FROM[#partFeature]
) AS [datas]
)
 SELECT
[cte].[PartId]
, [cte].[FeatureKey]
  FROM[cte]
  WHERE [cte].[featureCount] < @TotalFeatureCount;

Where @TotalFeatureCount is generated from something like

DECLARE @TotalFeatureCount INT
SELECT @TotalFeatureCount = COUNT(DISTINCT featurekey)
FROM #settings

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.