tsql - Collapse data ranges and overlapping data in SQL Server 2014 -

-

i have table ranges in below:

id  actioncode  group1  type    low         high 33            840     mm      000295800   000295899 34            840     mm      000295900   000295999

i need collapse 2 rows consecutive data 1 row instance above 

actioncode  group1  type    low         high           840     mm      000295800   000295999

for actioncode, group1, type...

there can overlapping data ranges, preceeding zeros, etc.

sample table:

if object_id('tempdb..#testtable') not null     drop table #testtable  create table #testtable(     [id] [int] identity(1,1) not null,     [actioncode] [char](1) not null,     [group1] [varchar](50) not null,     [type] [varchar](2) null,     [low] [varchar](50) not null,     [high] [varchar](50) not null,     constraint [pk_#testtable] primary key clustered ([id] asc)   )   go  insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401299870','401299879') insert #testtable (actioncode, group1, type, low, high) values ('a','840','aa','401644000','401646999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401378000','401378999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401644000','401646999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401299970','401299979') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','400424000','400424999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401299990','401299996') -- ds insert #testtable (actioncode, group1, type, low, high) values ('d','840','jj','401198000','401198999') insert #testtable (actioncode, group1, type, low, high) values ('d','840','jj','401649000','401649999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401299997','401299997') insert #testtable (actioncode, group1, type, low, high) values ('d','840','jj','401376000','401390999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401655000','401668999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','400411000','400411999') insert #testtable (actioncode, group1, type, low, high) values ('d','840','jj','400414000','400414999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401646000','401646999') insert #testtable (actioncode, group1, type, low, high) values ('d','840','jj','400413000','400413999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','jj','401654000','401654999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','gg','522892000','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','gg','522892100','522892199') insert #testtable (actioncode, group1, type, low, high) values ('a','356','gg','522892400','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','aa','522892400','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','aa','522892300','522892399') -- different types overlap range insert #testtable (actioncode, group1, type, low, high) values ('a','356','aa','522892200','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','kk','522892000','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','kk','522892200','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','kk','522892300','522892399') insert #testtable (actioncode, group1, type, low, high) values ('a','356','kk','522892400','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','kk','522892100','522892199') insert #testtable (actioncode, group1, type, low, high) values ('a','356','gg','522892200','522892999') insert #testtable (actioncode, group1, type, low, high) values ('a','356','gg','522892300','522892399') insert #testtable (actioncode, group1, type, low, high) values ('a','356','aa','522892100','522892199') insert #testtable (actioncode, group1, type, low, high) values ('a','356','aa','522892000','522892999') -- leading zeros insert #testtable (actioncode, group1, type, low, high) values ('a','840','mm','000295800','000295899') insert #testtable (actioncode, group1, type, low, high) values ('a','840','mm','000295900','000295999') -- overlap insert #testtable (actioncode, group1, type, low, high) values ('a','840','nn','623295800','623295999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','nn','623295900','623295999') insert #testtable (actioncode, group1, type, low, high) values ('a','840','nn','623295900','623296099') insert #testtable (actioncode, group1, type, low, high) values ('a','840','nn','623296100','623296299') go   select * #testtable order low

i can small table using recursive cte, table has little less million rows. once on size takes long time run. there index on "grouped" columns.

there must way quickly, i'm hitting roadblocks.

^

i think looking this:

with cte (     select  actioncode,             group1,             [type],             low,             high,             next_low =  lead(low,1) on (partition actioncode,group1,[type] order id),             next_high = lead(high,1) on (partition actioncode,group1,[type] order id)     #testtable )  select  actioncode,         group1,         [type],         low,         high cte       low != next_low          , high!= next_high

Comments

Post a Comment

Popular posts from this blog

python - How to create jsonb index using GIN on SQLAlchemy? -

-
here's current code creating index jsonb. index("mytable_data_idx_id_key", mytable.data['id'].astext, postgresql_using='gin') but got error. sqlalchemy.exc.programmingerror: (psycopg2.programmingerror) data type text has no default operator class access method "gin" hint: must specify operator class index or define default operator class data type. [sql: "create index event_data_idx_id_key on event using gin ((data ->> 'id'))"] is there way create index on sqlalchemy? the postgresql specific sqlalchemy docs @ http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#operator-classes mention postgresql_ops dictionary provide "operator class" used postgresql, , provide example illustrating use: index('my_index', my_table.c.id, my_table.c.data, postgresql_ops={ 'data': 'text_pattern_ops', ...
Read more

PHP DOM loadHTML() method unusual warning -

-
i had following code works fine on localhost: $document = new domdocument(); $document->loadhtml($p_result); $form = $document->getelementsbytagname('form')->item(0); // code continues using $form variable after same code updated on outside server, loadhtml() failed , issued warning. warning: domdocument::loadhtml() expects parameter 1 valid path, string given in path/name/to/script.php returned null instead of object code pretty gets fatal error. note contents of $p_result absolutely same on outside server , on localhost. but why displays kind of warning , why not work? doesn't loadhtml() expects argument 1 string in first place? why method expects parameter 1 valid path ? just make clear i'm not calling loadhtmlfile() , i'm calling loadhtml() . thanks. you're affected one of php bugs . issue present in php 5.6.8 , 5.6.9. have affected php version on server, , bug-free version on localhost. the bug forbids null ...
Read more

c# - TransactionScope not rolling back although no complete() is called -

-
i'm using transactionscope rollback transaction fail bool errorreported = false; action<importerrorlog> newerrorcallback = e => { errorreported = true; errorcallback(e); }; using (var transaction = new transactionscope()) { foreach (importtaskdefinition task in taskdefinition) { loader.load(streamfile, newerrorcallback, task.destinationtable, processingtaskid); } if (!errorreported) transaction.complete(); } i'm sure there no transactionscope started ahead or after code. i'm using entity framework insert in db. regardless state of errorreported transaction never rolled in case of error. what missing ? transactionscope sets transaction.current . that's does. wants transacted must @ property. i believe ef each time connection opened reason. probably, connection open when scope installed. open connection inside of scope or enlist manually. ef has nasty "design decision": default opens ...
Read more