Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need Assistance for Groups and Islands Problem Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 5:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 133, Visits: 370
create table #sample (rowguid int identity ,id_frm int ,id_to int)

insert into #sample values( 1,5)
insert into #sample values( 7,13)
insert into #sample values( 17,20)

In the above table I have values starting Id and Ending Id. I need to prepare a table which has all the numbers falls between starting Id and Ending Id

i have tried it with looping but response is very slow in real world.

any body help me with query ???

This is what I have tried so far...
declare @sql varchar(8000)  = '  
select top '+cast((select max(id_to) from #sample) as varchar(100))+' identity(int,1,1) as guidid into tally from sysobjects,syscolumns '

exec (@sql)


alter table Tally add slab varchar(10)

create clustered index idx on Tally(guidid)
create clustered index idx on #sample(id_frm asc,id_to desc)

update Tally set slab = rowguid
from #sample join Tally on guidid between id_frm and id_to

delete from Tally where slab is null

select * from Tally

This query works fine with small numbers

But My real time table have 13 digit nos. It through Arithmetic overflow error
Post #1566333
Posted Wednesday, April 30, 2014 6:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
vignesh.ms (4/30/2014)
...
But My real time table have 13 digit nos. It through Arithmetic overflow error


Please provide some realistic sample data so that folks attempting to help won't have the same problem.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1566340
Posted Wednesday, April 30, 2014 6:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 268, Visits: 598
Use bigint. And you don't need the dynamic SQL:

create table #sample (rowguid bigint identity ,id_frm bigint ,id_to bigint)

insert into #sample values( 1,5)
insert into #sample values( 7,13)
insert into #sample values( 17,20)

select top((select max(id_to) from #sample)) identity(bigint,1,1) as guidid into tally from sysobjects,syscolumns

Post #1566341
Posted Wednesday, April 30, 2014 6:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 133, Visits: 370
ChrisM@Work (4/30/2014)

Please provide some realistic sample data so that folks attempting to help won't have the same problem.



Table structure is same... Data only differs

It will always starts and ends with 13 digit number ...

Here are the sample records to insert

insert into #sample values( 1254858693000,1254858693999) 

insert into #sample values( 9867668925500,9867668929999)

Post #1566356
Posted Wednesday, April 30, 2014 6:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
DROP table #sample
create table #sample (rowguid int identity ,id_frm int ,id_to int)

insert into #sample values( 1,5)
insert into #sample values( 7,13)
insert into #sample values( 17,20)
insert into #sample values( 30,1962)

SELECT
id_frm+n, -- n is BIGINT, result is BIGINT
*
FROM #sample
CROSS APPLY (
SELECT TOP(id_to-id_frm+1)
n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a (n), -- This iTally provides
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b (n), -- 10000 rows MAX.
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c (n), -- expand as necessary
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)
) iTally (n)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1566357
Posted Wednesday, April 30, 2014 6:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 268, Visits: 598
I just changed your table slightly:

create table #sample (rowguid int identity ,id_frm bigint ,id_to bigint)

using bigint instead of int. With that in place, the rest of your queries work fine for me.
Post #1566361
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse