Thank you very much Vitaliy. You got it the desired result.

Latheesh, thanks for the reply.

Btw, how about if i will use 2 table. the desired result should be the same.thanks.

I modifed the code by Vitaliy and i got a duplicate records.

Create table #sample
(Item nvarchar(35), IdNum nvarchar(35))
Insert into #sample(Item,IdNum ) values('MAT3748','TRP005196603')
Insert into #sample(Item,IdNum) values('MAT3751','TRP005196603')
Insert into #sample(Item,IdNum) values('TX3001710DR','TRP005196603')
Insert into #sample(Item,IdNum) values('FRU300DRD','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3745','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3748','TRP005207420')
Insert into #sample(Item,IdNum) values('FRUA300DRD','TRP005207421')
Insert into #sample(Item,IdNum) values('MAT3771','TRP005207421')
Insert into #sample(Item,IdNum) values('MAT3774','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3824','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3977','TRP005207420')
Insert into #sample(Item,IdNum) values('CRR3000412iPH5','TRP005224362')

Create table #Sample2
( IdNum nvarchar(35))
Insert into #sample2(IdNum) values('TRP005196603')
Insert into #sample2(IdNum) values('TRP005207420')
Insert into #sample2(IdNum) values('TRP005207421')
Insert into #sample2(IdNum) values('TRP005207420')
Insert into #sample2(IdNum) values('TRP005224362')

Select * ,
isNull((
	SELECT CAST(MAX(Case When Item like 'FRUA%' THEN 'FRUA'
     When Item like 'FRUB%' THEN 'FRUB'
     When Item like 'TX3%' THEN 'TX'
     When Item like 'FRU%' THEN 'FRU'
     ELSE null END) as Varchar(20)) AS GetType 
    FROM #sample x WHERE x.IdNum = s.IDNum
	GROUP BY IDNum
),'Non-FRU') as REMARKS
from #sample2 s 
Inner Join #sample a
On a.IdNum = s.IdNum 
order by s.IdNum 

• Edited by Lenoj 23 hours 22 minutes ago

Sorry Guys. Just modified the sample DDL based on actual data. thanks.

Create table #Sample2  --Main Table
( IdNum nvarchar(35), itemID nvarchar(35), Line nvarchar(35))
Insert into #sample2(IdNum,itemID,line) values('TRP005196603','SKU001','Line1')
Insert into #sample2(IdNum,itemID,line) values('TRP005207420','SKU002','Line2')
Insert into #sample2(IdNum,itemID,line) values('TRP005207421','SKU003','Line3')
Insert into #sample2(IdNum,itemID,line) values('TRP005207420','SKU002','Line2')
Insert into #sample2(IdNum,itemID,line) values('TRP005224362','SKU004','Line3')



Create table #sample
(Item nvarchar(35), IdNum nvarchar(35))
Insert into #sample(Item,IdNum ) values('MAT3748','TRP005196603')
Insert into #sample(Item,IdNum) values('MAT3751','TRP005196603')
Insert into #sample(Item,IdNum) values('TX3001710DR','TRP005196603')
Insert into #sample(Item,IdNum) values('FRU300DRD','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3745','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3748','TRP005207420')
Insert into #sample(Item,IdNum) values('FRUA300DRD','TRP005207421')
Insert into #sample(Item,IdNum) values('MAT3771','TRP005207421')
Insert into #sample(Item,IdNum) values('MAT3774','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3824','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3977','TRP005207420')
Insert into #sample(Item,IdNum) values('CRR3000412iPH5','TRP005224362')

Hi,

I have a query that i would like to evaluate the Itemid. if the itemid has a prefix of FRU , I would like to create a new column contain FRU with all the same IDnum. another sample if the itemid has a prefix of TX, I will consider this as TS and also with a prefix of FRUA. if it doesnt have this prefix i will consider as NON-FRU. see below DDL  and sample desired result. thank you in advance.

Create table #sample
(Item nvarchar(35), IdNum nvarchar(35))
Insert into #sample(Item,IdNum) values('MAT3748','TRP005196603')
Insert into #sample(Item,IdNum) values('MAT3751','TRP005196603')
Insert into #sample(Item,IdNum) values('TX3001710DR','TRP005196603')
Insert into #sample(Item,IdNum) values('FRU300DRD','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3745','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3748','TRP005207420')
Insert into #sample(Item,IdNum) values('FRUA300DRD','TRP005207421')
Insert into #sample(Item,IdNum) values('MAT3771','TRP005207421')
Insert into #sample(Item,IdNum) values('MAT3774','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3824','TRP005207420')
Insert into #sample(Item,IdNum) values('MAT3977','TRP005207420')
Insert into #sample(Item,IdNum) values('CRR3000412iPH5','TRP005224362')

Select Item,
	 IdNum
from #sample
Group  by IdNum 

Sample Desired Result

Item----------	IdNum---------	REMARKS
MAT3748-------	TRP005196603--	TX
MAT3751-------	TRP005196603--	TX
TX3001710DR---	TRP005196603--	TX
FRU300DRD-----	TRP005207420--	FRU
MAT3745-------	TRP005207420--	FRU
MAT3748-------	TRP005207420--	FRU
FRUA300DRD----	TRP005207421--	FRUA
MAT3771-------	TRP005207421--	FRUA
MAT3774-------	TRP005207420--	NON-FRU
MAT3824-------	TRP005207420--	NON-FRU
MAT3977-------	TRP005207420--	NON-FRU
CRR3000412iPH5-	TRP005224362--	NON-FRU

• Edited by Lenoj Wednesday, March 25, 2015 5:17 AM

this is what i have done so far. could not get the exact result. Is this possible to check from IDnum if the corresponding ItemID has a prefix of FRU,FRUA,TXT then replicate all IDnum that has the following prefix.

Select
     Item ,
	 IdNum,
    Case When Item like '%FRUA%' THEN 'FRUA'
     When Item like '%FRUB%' THEN 'FRUB'
     When Item like '%TS3%' THEN 'TS'
     When Item like '%FRU%' THEN 'FRU'
     ELSE 'NON-FRU' END AS GetType
from #sample
Group  by IdNum, Item  
order by IdNum 

• Edited by Lenoj Wednesday, March 25, 2015 5:55 AM

Hi,

Are you sure that

MAT3774-------	TRP005207420--	NON-FRU

NON-FRU ?

We have an Item

FRU300DRD-----	TRP005207420--	FRU

With the same IdNum

Select * ,
isNull((
	SELECT CAST(MAX(Case When Item like 'FRUA%' THEN 'FRUA'
     When Item like 'FRUB%' THEN 'FRUB'
     When Item like 'TX3%' THEN 'TX'
     When Item like 'FRU%' THEN 'FRU'
     ELSE null END) as Varchar(20)) AS GetType FROM #sample x WHERE x.IdNum = s.IDNum
	GROUP BY IDNum
),'Non-FRU') as REMARKS
from #sample s 

• Edited by Vitaliy Lukashev Wednesday, March 25, 2015 6:36 AM
• Marked as answer by Lenoj 23 hours 35 minutes ago

Glad that it helps

Insert into #sample2(IdNum,itemID,line) values('TRP005207420','SKU002','Line2')
Insert into #sample2(IdNum,itemID,line) values('TRP005207420','SKU002','Line2')

FYI: The same rows. So you got a duplicate records.

• Edited by Vitaliy Lukashev 19 hours 47 minutes ago

Hi Vitaliy,

Here is the final query. I notice it takes more than 20 minutes to display the result which is not quite good. This table INVENTTRANS where it takes time to process which this table is a transaction.

Declare @timezoneOffset int
Set @timezoneOffset=8
SELECT s.PRODID, s.ASUPRODLINEID, s.ITEMID, 
		isNull((
		SELECT CAST(MAX(Case When ITEMID  like 'FRUA%' THEN 'FRUA'
			When ITEMID like 'FRUB%' THEN 'FRUB'
			When ITEMID like 'TX3%' THEN 'TX'
			When ITEMID like 'FRU%' THEN 'FRU'
			ELSE null END) as Varchar(20)) AS GetType 
		FROM  INVENTTRANS x with (nolock)
		WHERE 
			x.TRANSTYPE=8 AND x.DATAAREAID='mpa' AND x.TRANSREFID = s.PRODID
	    GROUP BY TRANSREFID
        ),'Non-FRU') as REMARKS
FROM PRODTABLE s with (nolock)
  inner join prodpool pp with (nolock) on s.dataareaid = pp.dataareaid and s.prodpoolid = pp.prodpoolid 
  inner join inventdim ivd  with (nolock) on s.dataareaid = ivd.dataareaid and s.inventdimid = ivd.inventdimid  
WHERE  
	   s.dataareaid =  
	   AND s.PRODSTATUS IN   
	   AND s.prodpoolid IN 
	   AND pt.asuprodlineid = CASE WHEN  
	   AND ivd.inventlocationid IN 
	   AND pp.ASUPOOLGROUP = 
	   AND DATEADD(HOUR,convert(int,@timezoneOffset), s.ASURAFDATETIME) BETWEEN '2015-03-04 12:00 AM' AND  '2015-03-05 12:00 AM'
ORDER BY s.PRODID 

Few things:

1. Make sure you have right indexes on the table to support the query execution.

2. Please share the execution plan to help further(only query would not help too much)

3. NOLOCK - Please remove this hint, this would lead you to dirty reads, not at all a good practice.

Hi, Lenoj!

You can rewrite query, to use cross apply and look the execution plan. I need to know a lot of things to optimize query.

Try rebuild statistics.