SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 reverse lookup? (SELECT x FROM table) IN LIKE %xy%
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

northturton
Starting Member

4 Posts

Posted - 05/15/2014 :  07:26:30  Show Profile  Reply with Quote
Hi, I'm trying to fix a stored procedure within our production db.

We have a notes table where users can enter free format details against a record via our intranet portal...

We can also add notes to this table via a lookup table - to put items on hold and provide a pre-defined reason... the text is submitted into the note...

ironically, some users decide to edit the note, appending a date... to this pre-defined note

We have a lookup table, where a user selects a reason and this gets recorded in a free format note field

1 Reason1
2 Reason2
3 Reason3
4 Reason4

Thus if Reason4 is selected and added to the notes table, the note would be Reason4

however when a user edits this it becomes Reason4 (15/05/2014 for 1 week)

We have a stored procedure that works if the reason text is as expected... however we now have to plug that hole.

SELECT ProjectId,
(
SELECT ReasonText FROM OnHoldReasons
WHERE (ReasonText IN
(SELECT Note FROM Notes WHERE (NoteID IN
(SELECT MAX(NoteID) FROM Notes
WHERE (notes.PackageID = dbo.Package.PackageID) AND (dbo.Package.ProcessID = 8) AND (NoteTypeID IN (1,2,6)) AND
(Note IN (SELECT ReasonText FROM dbo.OnHoldReasons))
)) ))
) AS OnHoldID
FROM dbo.Package WHERE dbo.Package.Enabled = 1
AND ProjectID = 90

The problem is centered around
(Note IN (SELECT ReasonText FROM dbo.OnHoldReasons)

In effect, I need to reverse the check so that the ReasonText is somewhere within the note... but we have up to 10 Reasons !!!
akin to something like (SELECT ReasonText FROM dbo.OnHoldReasons) IN %NOTE%...

The reason text is variable length text, and should be at the start of the note... but cannot be guaranteed.



Any advice from SQL experts?

many thanks
Brian

btw, everyone may say this as an excuse but its true, I didn't design this database or how it works. I have to add things in and update it :-(

Edited by - northturton on 05/15/2014 08:23:02

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 05/15/2014 :  09:18:08  Show Profile  Visit webfred's Homepage  Reply with Quote
-- declare test-tables
declare @OnHoldReasons Table(ReasonText varchar(255))
declare @Notes Table(Note varchar(255), NoteID int, PackageID int, NoteTypeID int)
declare @Package Table(PackageID int, ProcessID int, Enabled int, ProjectID int)

-- insert some test-rows
insert @OnHoldReasons
select 'Reason1' union all
select 'Reason2' union all
select 'another Reason Number 666'

insert @Notes
select 'Reason2 2014/05/15', 500, 2000, 1 union all
select 'Reason2 2014/05/15', 501, 2000, 9 union all
select 'Reason2 2014/05/15', 502, 2000, 2 union all
select 'Reason1', 503, 1000, 1 union all
select 'Reason2', 504, 7000, 6 union all
select 'another Reason Number 666', 505, 6000, 2

insert @Package
select 2000, 8, 1, 90 union all
select 2000, 8, 0, 90 union all
select 1000, 8, 1, 10 union all
select 7000, 8, 1, 70

-- trying the solution
select 
p.ProjectId,
r.ReasonText as OnHoldID
from @Package p 
join @Notes n on p.PackageID = n.PackageID and 
                 n.NoteTypeID in (1,2,6) and
                 n.NoteID = (select max(n2.NoteID) 
                             from @Notes n2 
                             where p.PackageID = n2.PackageID and 
                                   n2.NoteTypeID in (1,2,6) )
join @OnHoldReasons r on n.Note like '%'+r.ReasonText+'%'
where p.Enabled=1 and ProjectID = 90



Too old to Rock'n'Roll too young to die.
Go to Top of Page

northturton
Starting Member

4 Posts

Posted - 05/15/2014 :  10:26:33  Show Profile  Reply with Quote
That's looking very promising - I translated your SELECT to fit my query (without your INSERT to tables) and its returned 8 records that I was originally missing due to the extra text the users added... where as previosly it was returning only 1 that fitted correctly...

However it's now missing the original record!

I'll play with your script in full to ascertain what I may have done wrong in translation.

many thanks for your time
Brian

Go to Top of Page

northturton
Starting Member

4 Posts

Posted - 05/15/2014 :  10:31:05  Show Profile  Reply with Quote
ahh, your test only returns 1 record, Reason2, despite Reason2 appearing within 4 records. I would hope to return all records where Reason2 appears.

Further down the line, I return counts of each individual reason - i.e. Reason1 = 1, Reason2=4, another Reaso Number 666=1
Go to Top of Page

northturton
Starting Member

4 Posts

Posted - 05/15/2014 :  10:39:32  Show Profile  Reply with Quote
Fixed it be eliminating a NoteTypeID of 1
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 05/15/2014 :  10:51:25  Show Profile  Visit webfred's Homepage  Reply with Quote
I gave my best without having table structure, sample data and wanted result in relation to the sample data.
Hope all is fine now :)



Too old to Rock'n'Roll too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000