Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Search string one table to another

Author  Topic 

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-03-25 : 02:34:45
Hi I have a requirements to search string from one table to another. I will check if the corresponding IDnun from #sample2 table has a prefix of FRU,FRUA,TX or of else NON-FRU in #sample1. Thanks in advance.

Below is the DDL.


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')


sample desired result:
IDNUM-------REMARKS
---------------------
TRP005196603--TX
TRP005207421--FRUA
TRP005207420--FRU
TRP005224362--NON-FRU



with my Query, i get the 3 prefix but the problem will be the NON-FRU. if I include the 'FRU' in else even those IDNum that have those Prefix still filled by 'FRU'.

select b.IdNum,
case when a.item like '%TX%' then 'TX'
when a.Item like '%FRUA%' then 'FRUA'
when a.Item like '%FRUB%' then 'FRUB' END as Remarks
from #Sample2 b
Inner Join #sample a
On a.IdNum = b.IdNum
Group by b.IdNum, a.Item


stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-25 : 06:24:10
Not quite elegant solution , but is still in dev ..


;WITH CTE
AS
(
select
b.IdNum,
case when a.item like '%TX%' then 'TX'
when a.Item like '%FRUA%' then 'FRUA'
when a.Item like '%FRU[0-9]%' then 'FRU'
--else 'NON-FRU'
END as Remarks
from #Sample2 b
Inner Join #sample a
On a.IdNum = b.IdNum
Group by b.IdNum, a.Item
)

select
A.IdNum
,Remarks
FROM
CTE AS A
WHERE
Remarks IS NOT NULL
UNION ALL

SELECT
DISTINCT
A.IdNum
,'NON-FRU'
FROM
#Sample2 AS A
LEFT JOIN CTE AS B
ON A.IdNum = B.IdNum
AND B.Remarks IS NOT NULL
WHERE
B.IdNum IS NULL



sabinWeb MCP
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-25 : 06:24:27
the output:

IdNum Remarks
TRP005196603 TX
TRP005207420 FRU
TRP005207421 FRUA
TRP005224362 NON-FRU



sabinWeb MCP
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-25 : 06:35:18
the same output , using Row Number


;WITH CTE
AS
(
select
b.IdNum,
case when a.item like '%TX%' then 'TX'
when a.Item like '%FRUA%' then 'FRUA'
when a.Item like '%FRU[0-9]%' then 'FRU'
else 'NON-FRU'
END as Remarks
,case when a.item like '%TX%' then 1
when a.Item like '%FRUA%' then 2
when a.Item like '%FRU[0-9]%' then 3
else 4
END AS OrderField

from #Sample2 b
INNER Join #sample a
On a.IdNum = b.IdNum
Group by b.IdNum, a.Item
)

SELECT
IdNum
,Remarks
FROM
(
select
A.IdNum
,Remarks
,ROW_NUMBER() OVER(PARTITION BY A.IdNum ORDER BY OrderField) AS RN
FROM
CTE AS A
)Sample3
WHERE
RN = 1


result :

IdNum Remarks
TRP005196603 TX
TRP005207420 FRU
TRP005207421 FRUA
TRP005224362 NON-FRU



sabinWeb MCP
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-03-26 : 03:01:14
Thank you very much stepson. This is what i'm looking for. Here is the final query based on your codes. I notice when I run the codes it takes time to generate which is quite not good. I try to filter a one day data it takes more than 5 minutes, how come when i filter a month month data i think maybe more than 30 mintus. I think the table Inventtrans as a lot of data. this is a trnasction table.


declare @timezoneOffset int
set @timezoneOffset=8
;WITH CTE
AS
(
select
s.PRODID,
case when x.ITEMID like '%TX%' then 'TX'
when x.ITEMID like '%FRUA%' then 'FRUA'
when x.ITEMID like '%FRUB%' then 'FRUB'
when x.ITEMID like '%FRU[0-9]%' then 'FRU'
else 'NON-FRU'
END as Remarks
,case when x.ITEMID like '%TX%' then 1
when x.ITEMID like '%FRUA%' then 2
when x.ITEMID like '%FRUB%' then 3
when x.ITEMID like '%FRU[0-9]%' then 4
else 5
END AS OrderField
FROM dbo.PRODTABLE s with (nolock)
INNER Join dbo.INVENTTRANS x with (nolock)
On x.TRANSREFID = s.PRODID AND x.TRANSTYPE=8 AND x.DATAAREAID='tap'
inner join dbo.prodpool pp with (nolock) on s.dataareaid = pp.dataareaid and s.prodpoolid = pp.prodpoolid
inner join dbo.inventdim ivd with (nolock) on s.dataareaid = ivd.dataareaid and s.inventdimid = ivd.inventdimid
WHERE
s.dataareaid = 'tap'
AND s.PRODSTATUS in (5,7)
AND s.prodpoolid IN
AND s.asuprodlineid IN
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'
GROUP by s.PRODID , x.ITEMID
)

SELECT
PRODID
,Remarks
FROM
(
select
A.PRODID
,Remarks
,ROW_NUMBER() OVER(PARTITION BY A.PRODID ORDER BY OrderField) AS RN
FROM
CTE AS A
) Sample3
WHERE
RN = 1


Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-26 : 04:26:12
One remark is related to this :

AND DATEADD(HOUR,convert(int,@timezoneOffset), s.ASURAFDATETIME) BETWEEN '2015-03-04 12:00 AM' AND '2015-03-05 12:00 AM'


It is better to move the calculation on right side :

AND s.ASURAFDATETIME >= DATEADD(HOUR, - convert(int,@timezoneOffset), '2015-03-04 12:00 AM')
AND s.ASURAFDATETIME <= DATEADD(HOUR,- convert(int,@timezoneOffset), '2015-03-05 12:00 AM')


Also take a look / show us the execution plan for better solutions, ideas.
May be need to add some indexes, see the existing ones ...




sabinWeb MCP
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-03-29 : 21:33:10
This is the error i got when i'm running the query with execution plan.
By the way, Its okey if I will use cross apply in my query.

Msg 262, Level 14, State 4, Line 3

SHOWPLAN permission denied in database 'xxxxxx'.

Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-30 : 00:51:48
You don't have permission related to ShowPlan

GRANT SHOWPLANT TO USER_XXX
GO


sabinWeb MCP
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-30 : 00:53:54
GRANT SHOWPLANT TO USER_XXX
GO


sabinWeb MCP
Go to Top of Page

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-03-30 : 12:34:53
[code]
DECLARE @timezoneOffset int
SET @timezoneOffset=8

;WITH CTE
AS
(
SELECT
s.PRODID,
MIN(case
when x.ITEMID like '%TX%' then '1TX'
when x.ITEMID like '%FRUA%' then '2FRUA'
when x.ITEMID like '%FRUB%' then '3FRUB'
when x.ITEMID like '%FRU[0-9]%' then '4FRU'
else '5NON-FRU'
END
) as Sort_and_Remarks
FROM dbo.PRODTABLE s with (nolock)
INNER Join dbo.INVENTTRANS x with (nolock)
On x.TRANSREFID = s.PRODID AND x.TRANSTYPE=8 AND x.DATAAREAID='tap'
inner join dbo.prodpool pp with (nolock) on s.dataareaid = pp.dataareaid and s.prodpoolid = pp.prodpoolid
inner join dbo.inventdim ivd with (nolock) on s.dataareaid = ivd.dataareaid and s.inventdimid = ivd.inventdimid
WHERE
s.dataareaid = 'tap'
AND s.PRODSTATUS in (5,7)
AND s.prodpoolid IN
AND s.asuprodlineid IN
AND ivd.inventlocationid in
AND pp.ASUPOOLGROUP =
AND s.ASURAFDATETIME >= DATEADD(HOUR, -CONVERT(int,@timezoneOffset), '20150304')
AND s.ASURAFDATETIME < DATEADD(HOUR, -CONVERT(int,@timezoneOffset), '20150305')
GROUP by s.PRODID
)

SELECT
PRODID
,SUBSTRING(Remarks, 2, 10) AS Remarks
FROM CTE
--ORDER BY PRODID

[/code]
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-03-30 : 20:33:42
This is what i got when i run the query.
I dont have the permission to a database.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
Msg 262, Level 14, State 4, Line 5
SHOWPLAN permission denied in database 'xxxxx'.
Go to Top of Page
   

- Advertisement -