No_ and Code combined make the row unique. G_L Filter must be split, looping through the No and Code.
The table has data such as in the attached file:
Example Exp Categories.xlsx
The filter looks like this example: 50000..51000|60000..69999|70000|79999
the ".." means from and to and the | means or.
I have a split function, but I can only use it when I hardcode the No_ and Code like this:
declare @ParGLList Varchar(8000) set @ParGLList = (select [Filter] from @table1 where [Code] = 'OTHER' and [No_]= 'AV-FND009' ) declare @TempTable Table(Code varchar(200),No_ varchar(20), BeginValue varchar(100),EndValue varchar (100)) insert into @TempTable (Code, No_, BeginValue, EndValue) select ' ', ' ', case when replace(SUBSTRING(Value1, 1, CHARINDEX('.', Value1)),'.','')=' ' then Value1 else replace(SUBSTRING(Value1, 1, CHARINDEX('.', Value1)),'.','') end as First_val, replace(SUBSTRING(Value1, CHARINDEX('.', Value1) + 1, 100),'.','') Second_val from dbo.Split(@ParGLList, '|')
The result of the script above looks like this:
Code No_ BeginValue EndValue
Other AV-FND009 5215 5217
Other AV-FND009 5220 5235
Other AV-FND009 5237 5237
Other AV-FND009 5240 5240
I would like to loop through the original table and create a new table such as the result script above, without hardcoding each No, Code. That will enable me to compare the values in another table to the "between" BeginValue and EndValue
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.