Part Id 3900 take wrong technology id as 7 and it must Be 2 because Feature Name and Value Exist?

[ahmedsa2018]

I work on sql server 2017 I have table #partsfeature already exist as below

create table #partsfeature
(
PartId int,
FeatureName varchar(300),
FeatureValue varchar(300),
TechnologyId int
)
insert into #partsfeature(PartId,FeatureName,FeatureValue,TechnologyId)
values
(1211,'AC','5V',1),
(2421,'grail','51V',2),
(6211,'compress','33v',3)
my issue Done For Part id 3900 it take wrong

Technology Id 7 and Correct Must be 2

Because Feature name and Feature Value Exist

So it Must Take Same TechnologyId Exist

on Table #partsfeature as Technology Id 2 .

correct will be as Below

+--------+--------------+---------------+-------------
| PartID | FeatureName | FeatureValue | TechnologyId
+--------+--------------+---------------+-------------
| 3900 | grail | 51V | 2
+--------+--------------+---------------+-------
what I try is

 insert into #partsfeature(PartId,FeatureName,FeatureValue,TechnologyId)
select  PartId,FeatureName,FeatureValue,
        TechnologyId  = dense_rank() over (order by FeatureName,FeatureValue)
                      + (select max(TechnologyId) from #partsfeature)
from    
(
        values
        (3900,'grail','51V',NULL),
        (5442,'compress','30v',NULL),
        (7791,'AC','59V',NULL),
        (8321,'Angit','50V',NULL)
) s (PartId,FeatureName,FeatureValue,TechnologyId)

Expected Result For Parts Inserted

PartId FeatureName FeatureValue TechnologyId
7791 AC 59V 4
8321 Angit 50V 5
5442 compress 30v 6
3900 grail 51V 2

 

[mikrom]

I got this

with
mytable1 (PartId, FeatureName, FeatureValue) as (
  values (1211,'AC','5V'),
         (2421,'grail','51V'),
         (6211,'compress','33v'),
         (3900,'grail','51V')
), 
mytable2 (PartId, FeatureName, FeatureValue, TechnologyId) as (
  select 
    PartId,
    FeatureName,
    FeatureValue,
    dense_rank() over (order by FeatureName,FeatureValue) as TechnologyId
  from
    mytable1
)    
select * from mytable2
;

Result:

PARTID FEATURENAME  FEATUREVALUE  TECHNOLOGYID
6211   compress	    33v                      1
2421   grail	    51V                      2
3900   grail	    51V                      2
1211   AC	    5V                       3

and

with
mytable1 (PartId, FeatureName, FeatureValue) as (
  values (1211,'AC','5V'),
         (2421,'grail','51V'),
         (6211,'compress','33v'),
         (3900,'grail','51V')
), 
mytable2 (PartId, FeatureName, FeatureValue, TechnologyId) as (
  select 
    PartId,
    FeatureName,
    FeatureValue,
    dense_rank() over (order by FeatureName,FeatureValue) as TechnologyId
  from
    mytable1
)    
select 
  PartId,
  FeatureName,
  FeatureValue,
  dense_rank() over (order by upper(FeatureName),upper(FeatureValue)) 
  + (select max(TechnologyId) from mytable2)
  as TechnologyId  
from
(
  values 
    (7791,'AC','59V'),
    (5442,'compress','30v'),
    (8321,'Angit','50V')  
) s (PartId,FeatureName,FeatureValue)
;

Result:

PARTID FEATURENAME  FEATUREVALUE  TECHNOLOGYID
7791   AC           59V                      4
8321   Angit        50V	                     5
5442   compress     30v                      6

It seems to work as you expected

 

[ahmedsa2018]

THANK YOU FOR REPLY

part 3900 is missing and i ask this question for issue i face on that part

this part id 3900 have feature name and feature value exist on table #partsfeature

so it will take technology id 2 so how to modify my query to include part id 3900 with technology id 2

so result expected is

CODE
PARTID FEATURENAME FEATUREVALUE TECHNOLOGYID
7791 AC 59V 4
8321 Angit 50V 5
5442 compress 30v 6
3900 grail 51V 2 missing and it will take 2 because feature name and feature value exist before

 

[mikrom]

This is what I tried:

1) First I create the master table mytable1 and populate it with some data

create or replace table mytable1 (
  PartId int, 
  FeatureName  varchar(10), 
  FeatureValue varchar(5), 
  TechnologyId int
)
;

insert into mytable1
  values (1211,'AC','5V', 1),
         (2421,'grail','51V', 2),
         (6211,'compress','33v', 3)
;

select * from mytable1
;

Result is table mytable1 with these data

PARTID FEATURENAME  FEATUREVALUE  TECHNOLOGYID
1211   AC           5V            1
2421   grail        51V           2
6211   compress     33v           3

2) Now I create from additional data
[pre]
(3900,'grail','51V'),
(5442,'compress','30v'),
(7791,'AC','59V'),
(8321,'Angit','50V')
[/pre]
the temporary table mytable2 with the same structure as master table mytable1.
The value of the last column TechnologyId should be taken from mytable1 if exists, or computed new.

create table mytable2 as (
with
mydata (PartId, FeatureName, FeatureValue) as (
  values 
    (3900,'grail','51V'),  
    (5442,'compress','30v'),
    (7791,'AC','59V'),
    (8321,'Angit','50V')  

), 
temp1 (PartId, FeatureName, FeatureValue, TechnologyId) as (
  select 
    t2.PartId,
    t2.FeatureName,
    t2.FeatureValue,
    t1.TechnologyId  
  from 
  mydata t2 
  left join 
  (select distinct FeatureName, FeatureValue, TechnologyId from mytable1) t1
  on t2.FeatureName = t1.FeatureName and t2.FeatureValue = t1.FeatureValue    
),
temp2 (PartId, FeatureName, FeatureValue, TechnologyId) as (
  select 
    PartId,
    FeatureName,
    FeatureValue,
    case 
      when (TechnologyId IS NULL) then
        dense_rank() over (order by upper(FeatureName), upper(FeatureValue)) 
        + (select max(TechnologyId) from mytable1)
      else TechnologyId
    end
  from temp1
)
select * from temp2
) with data
;

select * from mytable2
;

The result is the table mytable2 with these data

PARTID FEATURENAME  FEATUREVALUE  TECHNOLOGYID
7791   AC           59V           4
8321   Angit        50V           5
5442   compress     30v           6
3900   grail        51V           2

3) At end, I can insert the new data from mytable2 into mytable1

insert into mytable1
 select * from mytable2
;
select * from mytable1
  order by TechnologyId
;

All data are now in the table mytable1

PARTID FEATURENAME  FEATUREVALUE  TECHNOLOGYID
1211   AC           5V            1
2421   grail        51V           2
3900   grail        51V           2
6211   compress     33v           3
7791   AC           59V           4
8321   Angit        50V           5
5442   compress     30v           6

 

[mikrom]

btw. temp2 CTE is not needed when using COALESCE making the create statement shorter:

create table mytable2 as (
with
mydata (PartId, FeatureName, FeatureValue) as (
  values 
    (3900,'grail','51V'),  
    (5442,'compress','30v'),
    (7791,'AC','59V'),
    (8321,'Angit','50V')  

), 
temp (PartId, FeatureName, FeatureValue, TechnologyId) as (
  select 
    t2.PartId,
    t2.FeatureName,
    t2.FeatureValue,
    coalesce(t1.TechnologyId,         
      dense_rank() over (order by upper(t2.FeatureName), upper(t2.FeatureValue)) 
      + (select max(TechnologyId) from mytable1)
    )   
  from 
  mydata t2 
  left join 
  (select distinct FeatureName, FeatureValue, TechnologyId from mytable1) t1 
  on t2.FeatureName = t1.FeatureName and t2.FeatureValue = t1.FeatureValue    
)
select * from temp
) with data
;