December 19, 2013 at 7:17 am
Hi There,
Please consider the following data
create table #sampletbl
(
Category varchar(100),
Numbers varchar(100),
Status varchar(100)
)
insert into #sampletbl values ('A','29710000001','0')
insert into #sampletbl values ('A','29710000002','0')
insert into #sampletbl values ('A','29710000003','0')
insert into #sampletbl values ('A','29710000004','0')
insert into #sampletbl values ('A','29710000005','0')
insert into #sampletbl values ('A','29710000006','1')
insert into #sampletbl values ('A','29710000007','0')
insert into #sampletbl values ('A','29710000008','0')
insert into #sampletbl values ('A','29710000009','1')
insert into #sampletbl values ('A','29710000010','0')
insert into #sampletbl values ('A','29710000011','0')
insert into #sampletbl values ('A','29710000012','0')
insert into #sampletbl values ('A','29710000013','0')
insert into #sampletbl values ('A','29710000014','0')
insert into #sampletbl values ('A','29710000015','0')
insert into #sampletbl values ('A','29710000016','0')
insert into #sampletbl values ('A','29710000017','0')
insert into #sampletbl values ('A','29710000018','0')
insert into #sampletbl values ('A','29710000019','0')
insert into #sampletbl values ('A','29710000020','1')
if status = 0 then unused status = 1 then used
i need to find 10 continues unused numbers
This is the sample data. In live I'm having plenty of data like that . So quite complex.
note : In real world I need to find the 1000 continues number ranges
pls help....
December 19, 2013 at 7:53 am
vignesh.ms (12/19/2013)
Hi There,Please consider the following data
create table #sampletbl
(
Category varchar(100),
Numbers varchar(100),
Status varchar(100)
)
insert into #sampletbl values ('A','29710000001','0')
insert into #sampletbl values ('A','29710000002','0')
insert into #sampletbl values ('A','29710000003','0')
insert into #sampletbl values ('A','29710000004','0')
insert into #sampletbl values ('A','29710000005','0')
insert into #sampletbl values ('A','29710000006','1')
insert into #sampletbl values ('A','29710000007','0')
insert into #sampletbl values ('A','29710000008','0')
insert into #sampletbl values ('A','29710000009','1')
insert into #sampletbl values ('A','29710000010','0')
insert into #sampletbl values ('A','29710000011','0')
insert into #sampletbl values ('A','29710000012','0')
insert into #sampletbl values ('A','29710000013','0')
insert into #sampletbl values ('A','29710000014','0')
insert into #sampletbl values ('A','29710000015','0')
insert into #sampletbl values ('A','29710000016','0')
insert into #sampletbl values ('A','29710000017','0')
insert into #sampletbl values ('A','29710000018','0')
insert into #sampletbl values ('A','29710000019','0')
insert into #sampletbl values ('A','29710000020','1')
if status = 0 then unused status = 1 then used
i need to find 10 continues unused numbers
This is the sample data. In live I'm having plenty of data like that . So quite complex.
note : In real world I need to find the 1000 continues number ranges
pls help....
Some issues with your datatypes here. Why are you storing everything as varchar(100)??? You should be using appropriate datatypes for everything.
I would restructure this table like this.
create table #sampletbl
(
Category char(1),
Numbers bigint,
Status bit
)
Now it becomes trivial to find a value to start with. You just find MAX(Numbers) and you have a range nearly as big as you want.
_______________________________________________________________
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/
December 19, 2013 at 8:50 am
Sean Lange (12/19/2013)
vignesh.ms (12/19/2013)
Hi There,Please consider the following data
create table #sampletbl
(
Category varchar(100),
Numbers varchar(100),
Status varchar(100)
)
insert into #sampletbl values ('A','29710000001','0')
insert into #sampletbl values ('A','29710000002','0')
insert into #sampletbl values ('A','29710000003','0')
insert into #sampletbl values ('A','29710000004','0')
insert into #sampletbl values ('A','29710000005','0')
insert into #sampletbl values ('A','29710000006','1')
insert into #sampletbl values ('A','29710000007','0')
insert into #sampletbl values ('A','29710000008','0')
insert into #sampletbl values ('A','29710000009','1')
insert into #sampletbl values ('A','29710000010','0')
insert into #sampletbl values ('A','29710000011','0')
insert into #sampletbl values ('A','29710000012','0')
insert into #sampletbl values ('A','29710000013','0')
insert into #sampletbl values ('A','29710000014','0')
insert into #sampletbl values ('A','29710000015','0')
insert into #sampletbl values ('A','29710000016','0')
insert into #sampletbl values ('A','29710000017','0')
insert into #sampletbl values ('A','29710000018','0')
insert into #sampletbl values ('A','29710000019','0')
insert into #sampletbl values ('A','29710000020','1')
if status = 0 then unused status = 1 then used
i need to find 10 continues unused numbers
This is the sample data. In live I'm having plenty of data like that . So quite complex.
note : In real world I need to find the 1000 continues number ranges
pls help....
Some issues with your datatypes here. Why are you storing everything as varchar(100)??? You should be using appropriate datatypes for everything.
I would restructure this table like this.
create table #sampletbl
(
Category char(1),
Numbers bigint,
Status bit
)
Now it becomes trivial to find a value to start with. You just find MAX(Numbers) and you have a range nearly as big as you want.
How could we achieve it ? can u pls share the query ?
December 19, 2013 at 8:53 am
Not quite sure what you want for output? What should the query do or return?
_______________________________________________________________
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/
December 19, 2013 at 9:15 am
Sean Lange (12/19/2013)
Not quite sure what you want for output? What should the query do or return?
query should return if 10 continues nos is available in unused state ...
December 19, 2013 at 9:20 am
This problem is known as "Finding Islands". I would suggest to read this book to learn more about the solutions.
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
http://shop.oreilly.com/product/0790145323088.do
WITH C1 AS (
SELECT
Category,
Numbers,
[Status],
ROW_NUMBER() OVER(PARTITION BY Category ORDER BY Numbers) -
ROW_NUMBER() OVER(PARTITION BY Category ORDER BY [Status], Numbers) AS grp
FROM
#sampletbl
)
SELECT
Category,
MIN(Numbers) AS snum,
MAX(Numbers) AS enum
FROM
C1
WHERE
[Status] = '0'
GROUP BY
Category,
grp
HAVING
COUNT(*) >= 10;
GO
The idea is to identify each island with a unique group identifier. The rest is select those groups with number of members greater than or equal to the desired amount.
December 19, 2013 at 9:30 am
vignesh.ms (12/19/2013)
Sean Lange (12/19/2013)
Not quite sure what you want for output? What should the query do or return?query should return if 10 continues nos is available in unused state ...
Then as hunchback suggested this is an "Islands and Gaps" issues. In this case all you really need to do is group "Islands" of contiguous numbers where the status is unused.
Take a look at Jeff Moden's article that explains how to do this pretty easily.
http://qa.sqlservercentral.com/articles/T-SQL/71550/[/url]
_______________________________________________________________
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/
December 21, 2013 at 3:20 am
Thanks Guys... thanks for your assistance ....
Really helpful... and awesome.
December 21, 2013 at 8:56 pm
I guess my question would be, what are you going to do if you don't have, say, the 1,000 contiguous numbers that you're looking for?
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply