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),
PartFeatureNumber int
)
insert into #partsfeature(PartId,FeatureName,FeatureValue,PartFeatureNumber)
values
(1211,'AC','5V',1),
(2421,'grail','51V',2),
(6211,'compress','33v',3)
my issue Done For Part id 3900 it take wrong
Part Feature Number 7 and Correct Must be 2
Because Feature name and Feature Value Exist
So it Must Take Same Part Feature Number Exist
on Table #partsfeature as Part Feature Number 2 .
correct will be as Below
+--------+--------------+---------------+-------------
| PartID | FeatureName | FeatureValue | PartFeatureNumber
+--------+--------------+---------------+-------------
| 3900 | grail | 51V | 2
+--------+--------------+---------------+-------
what i try
what I try is
insert into #partsfeature(PartId,FeatureName,FeatureValue,PartFeatureNumber)
select PartId,FeatureName,FeatureValue,
PartFeatureNumber = dense_rank() over (order by FeatureName,FeatureValue)
+ (select max(PartFeatureNumber) from #partsfeature)
from
(
values
(3900,'grail','51V',NULL),
(5442,'compress','30v',NULL),
(7791,'AC','59V',NULL),
(8321,'Angit','50V',NULL)
) s (PartId,FeatureName,FeatureValue,PartFeatureNumber)
Expected Result For Parts Inserted
PartIdFeatureNameFeatureValuePartFeatureNumber
7791AC 59V 4
8321Angit 50V 5
5442compress 30v 6
3900grail 51V 2
June 12, 2022 at 11:12 am
I've read this about five times now, yet still I do not understand what the problem is.
Can you try explaining it again, please?
In particular, this:
my issue Done For Part id 3900 it take wrong Part Feature Number 7 and Correct Must be 2 Because Feature name and Feature Value Exist
According to the sample data you provided, there are no duplicates on (FeatureName, FeatureValue), so I do not understand this comment.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 12, 2022 at 11:38 am
for the Part 3900
I have feature name grail and feature value 51V it exist on table before insert and take technology id 2
so when insert same feature name with value it must take yechnology id exist on table else apply dense rank
i don't lookup for partid i looking for feature name and value both
June 12, 2022 at 11:42 am
for the Part 3900
I have feature name grail and feature value 51V it exist on table before insert and take technology id 2
so when insert same feature name with value it must take yechnology id exist on table else apply dense rank
i don't lookup for partid i looking for feature name and value both
'TechnologyId' does not appear in your earlier posts. What is it?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 12, 2022 at 12:12 pm
my table as below
create table #partsfeature
(
PartId int,
FeatureName varchar(300),
FeatureValue varchar(300),
PartFeatureNumber int
)
insert into #partsfeature(PartId,FeatureName,FeatureValue,PartFeatureNumber)
values
(1211,'AC','5V',1),
(2421,'grail','51V',2),
(6211,'compress','33v',3)
when make insert for data as below
insert into #partsfeature(PartId,FeatureName,FeatureValue,PartFeatureNumber)
select PartId,FeatureName,FeatureValue,
PartFeatureNumber = dense_rank() over (order by FeatureName,FeatureValue)
+ (select max(PartFeatureNumber) from #partsfeature)
from
(
values
(3900,'grail','51V',NULL),
(5442,'compress','30v',NULL),
(7791,'AC','59V',NULL),
(8321,'Angit','50V',NULL)
) s (PartId,FeatureName,FeatureValue,PartFeatureNumber)
grail feature name and 51V feature value exist
June 12, 2022 at 12:54 pm
OK, so your table contains (2421,'grail','51V',2) and you try to insert (3900,'grail','51V',NULL) and for some reason, this is an issue.
After, inserting (3900,'grail','51V',NULL), what would you like the NULL to be replaced by? What is the logic?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 12, 2022 at 12:56 pm
after insert part id below
(3900,'grail','51V',NULL)
Null will be 2 because grail feature name and value 51V exist before with technologyid 2
June 12, 2022 at 1:01 pm
after insert part id below
(3900,'grail','51V',NULL)
Null will be 2 because grail feature name and value 51V exist before with technologyid 2
OK. And what would NULL become if they did not already exist?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 12, 2022 at 1:16 pm
if Feature name and Feature value not exist on table #partsfeature then i will apply dense rank to take new number for technology id
if Feature name and Feature value exist on table #partsfeature then it will take number that exist for feature name and feature value
June 12, 2022 at 1:33 pm
Try this:
INSERT #partsfeature
(
PartId
,FeatureName
,FeatureValue
,PartFeatureNumber
)
SELECT s.PartId
,s.FeatureName
,s.FeatureValue
,PartFeatureNumber = ISNULL(x.PartFeatureNumber,DENSE_RANK() OVER (ORDER BY s.FeatureName, s.FeatureValue) +
(
SELECT MAX(PartFeatureNumber) FROM #partsfeature
))
FROM
(
VALUES
(3900, 'grail', '51V', NULL)
,(5442, 'compress', '33v', NULL)
,(7791, 'AC', '59V', NULL)
,(8321, 'Angit', '50V', NULL)
) s (PartId, FeatureName, FeatureValue, PartFeatureNumber)
OUTER APPLY
(
SELECT TOP (1) p.PartFeatureNumber
FROM #partsfeature p
WHERE p.FeatureName = s.FeatureName
AND p.FeatureValue = s.FeatureValue
) x;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
yes exactly
thanks
June 19, 2022 at 8:02 am
This was removed by the editor as SPAM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply