February 6, 2014 at 10:49 am
This problem seemed straight forward at first but is proving to be a challenge.
What I'm trying to do is trying to come up with a new range when given a start range and ranges to exclude
Here's an example.
Starting Range(Item, Start, End)
1,100,300
Range to exclude(Item,Start,End)
1,110,120
Resulting ranges
1,100,109
1,121,300
It also starts to get more tricky when the excluded ranges extend beyond the beginning and end of the starting range.
Anybody have any suggestions on how to solve this?
Here's some sql that will create the ranges
declare @ranges table(Item int
,Start smallint
,[End] smallint)
declare @excludeRanges table(Item int
,Start smallint
,[End] smallint)
insert @ranges(Item, Start, [End])
values(1,100,300)
,(2,100,300)
insert @excludeRanges(Item, Start, [End])
values(1,90,110)
,(1,200,250)
,(1,290,310)
,(2,110,120)
,(2,150,155)
/*
Result :
1,111,199
1,251,289
2,100,109
2,121,149
2,156,300
*/
February 6, 2014 at 11:06 am
Maybe something like this could work.
WITH cteTally AS(
--Use TOP to return only the number of rows needed for all ranges
SELECT TOP((SELECT MAX([End]) FROM @ranges) - (SELECT MIN([Start]) FROM @ranges) + 1)
--this will create all numbers from the start of the lower range to the end of the max range
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + (SELECT MIN([Start]) FROM @ranges) - 1 n
FROM master.sys.all_columns --use a table with enough rows (for additional rows use a cross join or consider a memory only option)
),
Grouped AS( --Need a CTE to use a window function value in the group by
SELECT Item,
n,
--This will generate sets of values to differentiate where the gaps are. Value minus a consecutive row counter (see example below)
n - ROW_NUMBER() OVER( PARTITION BY Item ORDER BY n) grouper
FROM cteTally t
JOIN @ranges r ON t.n BETWEEN r.Start AND r.[End] --Join to get a set of values for each item
AND NOT EXISTS(
SELECT *
FROM @excludeRanges e
WHERE e.Item = r.Item
AND t.n BETWEEN e.Start AND e.[End]) --exclude the excluded ranges from the set of values
)
SELECT Item, MIN(n) Start, MAX(n) [End] --just get the min and max values grouped by the Item and the constructed column
FROM Grouped
GROUP BY Item, grouper
ORDER BY Item, Start
/* Example of n - ROW_NUMBER() OVER( PARTITION BY Item ORDER BY n)
NValueROW_NUMBERGrouper
110
220
330
541
651
1064
1174
*/
February 6, 2014 at 11:25 am
WOW!!! That is fantastic!
Thanks!
February 6, 2014 at 11:43 am
You're welcome.
But, do you understand how does it work?
February 6, 2014 at 11:54 am
Not yet. That's what I'm doing right now. I'm executing different parts independently to help understand what you did. If you can break down how you solved it would be great! I'm sure I can eventually understand what you did. 🙂
February 6, 2014 at 12:41 pm
I added some comments to the original code that might help you to understand what's going on.
February 6, 2014 at 12:54 pm
Hey Luis I don't know if you can see what I've just seen on this funny little mobly but you my friend may just be the first! Whether you are or not, I reckon you deserve to be. Bloody well done mate! 🙂 🙂
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 6, 2014 at 1:05 pm
ChrisM@home (2/6/2014)
Hey Luis I don't know if you can see what I've just seen on this funny little mobly but you my friend may just be the first! Whether you are or not, I reckon you deserve to be. Bloody well done mate! 🙂 🙂
I assume you mean the green highlighting and the "This worked for the OP" thing? I saw that a couple times earlier today and wondered what it was all about. Seems we have some new undocumented features.
_______________________________________________________________
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/
February 6, 2014 at 1:10 pm
Sean Lange (2/6/2014)
ChrisM@home (2/6/2014)
Hey Luis I don't know if you can see what I've just seen on this funny little mobly but you my friend may just be the first! Whether you are or not, I reckon you deserve to be. Bloody well done mate! 🙂 🙂I assume you mean the green highlighting and the "This worked for the OP" thing? I saw that a couple times earlier today and wondered what it was all about. Seems we have some new undocumented features.
Or maybe it's documented and available only to OPs. Should we ask a question to confirm it? I haven't done it in a while.
February 6, 2014 at 1:12 pm
Sean Lange (2/6/2014)
ChrisM@home (2/6/2014)
Hey Luis I don't know if you can see what I've just seen on this funny little mobly but you my friend may just be the first! Whether you are or not, I reckon you deserve to be. Bloody well done mate! 🙂 🙂I assume you mean the green highlighting and the "This worked for the OP" thing? I saw that a couple times earlier today and wondered what it was all about. Seems we have some new undocumented features.
Looks like it. I'm not sure if I like it as it stands, it might get competitive - and many of us do "fluffing", asking for more info so that someone else can benefit from it. I know you do. One thing for sure mate - I'd be happy as a pig in sugar if you were first too. Lets call it a dead heat 🙂 Here in the UK Luis' post was the first I saw tagged, and I was following a couple of posts on the way home from work 3 hours ago.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 6, 2014 at 1:49 pm
ChrisM@home (2/6/2014)
Sean Lange (2/6/2014)
ChrisM@home (2/6/2014)
Hey Luis I don't know if you can see what I've just seen on this funny little mobly but you my friend may just be the first! Whether you are or not, I reckon you deserve to be. Bloody well done mate! 🙂 🙂I assume you mean the green highlighting and the "This worked for the OP" thing? I saw that a couple times earlier today and wondered what it was all about. Seems we have some new undocumented features.
Looks like it. I'm not sure if I like it as it stands, it might get competitive - and many of us do "fluffing", asking for more info so that someone else can benefit from it. I know you do. One thing for sure mate - I'd be happy as a pig in sugar if you were first too. Lets call it a dead heat 🙂 Here in the UK Luis' post was the first I saw tagged, and I was following a couple of posts on the way home from work 3 hours ago.
hehe by no means was I suggesting that I was first. I haven't seen that on any that of my posts. Luis is the first person I can name though where I saw it, therefore as of now Luis is in the lead by a nose. 😉
_______________________________________________________________
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/
February 6, 2014 at 1:50 pm
Luis Cazares (2/6/2014)
Sean Lange (2/6/2014)
ChrisM@home (2/6/2014)
Hey Luis I don't know if you can see what I've just seen on this funny little mobly but you my friend may just be the first! Whether you are or not, I reckon you deserve to be. Bloody well done mate! 🙂 🙂I assume you mean the green highlighting and the "This worked for the OP" thing? I saw that a couple times earlier today and wondered what it was all about. Seems we have some new undocumented features.
Or maybe it's documented and available only to OPs. Should we ask a question to confirm it? I haven't done it in a while.
I sadly can't remember the last time I started a new question. Not so sure that is a good thing. :hehe:
_______________________________________________________________
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/
February 7, 2014 at 9:45 am
The previous solution works great on small ranges, however it doesn't handle larger datasets and ranges.
For example if the records are changed to something like
insert @ranges(Item, Start, [End])
values(1,1,99999)
,(2,1,99999)
...
,(1000000,1,99999)
insert @excludeRanges(Item, Start, [End])
values(1,90,110)
,(1,200,250)
,(1,290,310)
,(2,110,120)
,(2,150,155)
...
,(1000000,299,559)
the previous solution isn't able to handle it. It wouldn't take an extremely long time to finish.
Any other suggestions?
Thanks!
February 7, 2014 at 10:27 am
As I told you in the comments, you need to change the source for cteTally to make it work with larger ranges.
Check the following article: http://www.sqlservercentral.com/articles/T-SQL/62867/
February 7, 2014 at 10:38 am
This will build one million rows on the fly and use them as needed. To be fair, smallint won't accept 99,999 😛
declare @ranges table(Item int
,Start int
,[End] int)
declare @excludeRanges table(Item int
,Start int
,[End] int)
insert @ranges(Item, Start, [End])
values(1,100,300)
,(2,100,300)
,(1000000,1,99999)
insert @excludeRanges(Item, Start, [End])
values(1,90,110)
,(1,200,250)
,(1,290,310)
,(2,110,120)
,(2,150,155)
,(1000000,299,559);
WITH e1(N) AS(
SELECT N FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(N)
),
e2(N) AS(
SELECT e1.* FROM e1, e1 b --10*10 = 100 rows
),
e6(N) AS(
SELECT e2.* FROM e2, e2 b, e2 c --100*100*100 = 1,000,000 rows
),
cteTally AS(
--Use TOP to return only the number of rows needed for all ranges
SELECT TOP((SELECT MAX([End]) FROM @ranges) - (SELECT MIN([Start]) FROM @ranges) + 1)
--this will create all numbers from the start of the lower range to the end of the max range
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + (SELECT MIN([Start]) FROM @ranges) - 1 n
FROM e6 --for additional rows use additional cross joins
),
Grouped AS( --Need a CTE to use a window function value in the group by
SELECT Item,
n,
--This will generate sets of values to differentiate where the gaps are. Value minus a consecutive row counter (see example below)
n - ROW_NUMBER() OVER( PARTITION BY Item ORDER BY n) grouper
FROM cteTally t
JOIN @ranges r ON t.n BETWEEN r.Start AND r.[End] --Join to get a set of values for each item
AND NOT EXISTS(
SELECT *
FROM @excludeRanges e
WHERE e.Item = r.Item
AND t.n BETWEEN e.Start AND e.[End]) --exclude the excluded ranges from the set of values
)
SELECT Item, MIN(n) Start, MAX(n) [End] --just get the min and max values grouped by the Item and the constructed column
FROM Grouped
GROUP BY Item, grouper
ORDER BY Item, Start
/* Example of n - ROW_NUMBER() OVER( PARTITION BY Item ORDER BY n)
NValueROW_NUMBERGrouper
110
220
330
541
651
1064
1174
*/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply