May 9, 2014 at 9:00 am
I am trying to insert a bunch of data based on a ranger.
Someone passes me starting range = OD1-2000, ending range = OD1-2050.
So I am trying to figure out how to create OD1-2000, OD1-2001, OD1-2003, OD1-2004......
The other hard part is they won't all be in this format. They could pass 11548-5-100 to 11548-5-200.
Just seeing if anybody has any thoughts.
May 9, 2014 at 9:14 am
cdl_9009 (5/9/2014)
I am trying to insert a bunch of data based on a ranger.Someone passes me starting range = OD1-2000, ending range = OD1-2050.
So I am trying to figure out how to create OD1-2000, OD1-2001, OD1-2003, OD1-2004......
The other hard part is they won't all be in this format. They could pass 11548-5-100 to 11548-5-200.
Just seeing if anybody has any thoughts.
Just a little string manipulation and a tally table makes short work of this.
declare @Start varchar(20) = 'OD1-2000', @End varchar(20) = 'OD1-2050';
select @Start = '11548-5-100', @End = '11548-5-200';
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select left(@Start, len(@start) - charindex('-', reverse(@Start)) + 1) + CAST(N as varchar)
from cteTally
where N >= CAST(right(@Start, charindex('-', reverse(@Start)) - 1) as int)
and N <= CAST(right(@End, charindex('-', reverse(@End)) - 1) as int)
Read up about tally tables here. http://qa.sqlservercentral.com/articles/62867/[/url]
In my system I actually have a view named cteTally so I don't have to write this cte all the time. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2014 at 10:23 am
Thank you. I will have to research this so I understand what this does. Then I might come up with more intelligent questions.
May 9, 2014 at 10:30 am
cdl_9009 (5/9/2014)
Thank you. I will have to research this so I understand what this does. Then I might come up with more intelligent questions.
I can try to explain portions you don't understand. What part(s) are confusing and we can tackle those first.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2014 at 12:27 pm
I am trying to go line by line to see what is going on. Like what is the WITH statement doing? I believe that is part of making the cteTally(N) table.
Also trying to look at how to I use this to loop though all the ranges I have to create that list and put that into like a temp table to process later. At the moment I am new to this put works pretty slick with what I can see.
May 9, 2014 at 12:36 pm
cdl_9009 (5/9/2014)
I am trying to go line by line to see what is going on. Like what is the WITH statement doing? I believe that is part of making the cteTally(N) table.Also trying to look at how to I use this to loop though all the ranges I have to create that list and put that into like a temp table to process later. At the moment I am new to this put works pretty slick with what I can see.
WITH is how we declare a common table expression (CTE). http://msdn.microsoft.com/en-us/library/ms175972.aspx. In the code I posted there are several cte's one after the other.
You don't need to loop. Looping in sql is painfully slow. Notice how the code I posted does not have any looping at all yet it returns all the values.
If you have a number of range sets a good approach would possibly be to use an Inline Table Valued Function (itvf). http://technet.microsoft.com/en-us/library/ms189294%28v=sql.105%29.aspx. Make sure that if you do this your function has 1 and only 1 line of code or you will run into some really awful performance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 9, 2014 at 12:47 pm
Sean's code will work just fine as a join to a table that contains the ranges.
An iTVF as suggested by him, might get some performance improvements because you can't never be too fast.
Be sure to fully understand CTEs as they're a great tool available. They're like subqueries on steroids. 😀
Here's a modification of Sean's code working along with a table.
CREATE TABLE #Ranges(
RangeStart varchar(20),
RangeEnd varchar(20));
INSERT #Ranges
VALUES('OD1-2000', 'OD1-2020'),
('11548-5-100', '11548-5-150');
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
SELECT LEFT( RangeStart, LEN(RangeStart) - CHARINDEX('-', REVERSE(RangeStart)) + 1) + CAST(N as varchar)
FROM #Ranges r
JOIN cteTally t
ON N >= CAST(right(RangeStart, charindex('-', reverse(RangeStart)) - 1) as int)
and N <= CAST(right(RangeEnd, charindex('-', reverse(RangeEnd)) - 1) as int)
GO
DROP TABLE #Ranges
May 12, 2014 at 1:20 pm
Okay. Still learning about CTE's. How would you change the string to not take off the leading zero. So my value is OD1-0200 TO OD1-0220.
It makes OD1-200, OD1-201 but I am trying to added the 0 back.
Still trying to understand the WITH entire statement, E1-4 and the selects it does. But been messing with the CHARINDEX since I think i will have to added my 0(zero) back to that somehow.
May 12, 2014 at 1:44 pm
You could append enough zeros and use a RIGHT() function when integrating the n value.
RIGHT( '0000' + CAST(N as varchar), 4)
Here's a great explanation on how the tally table (CTE in this case) works. It might help you to understand a bit more. http://qa.sqlservercentral.com/Forums/FindPost1569639.aspx
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply