Kiril,

at least your script can run a good deal faster, if you'll choose to go this way. You needn't copy the whole source table into #TBL and needn't so...

Well..., in short it is not the way how SQL code should be created. Really. This code is slow and dangerous.

First, you shouldn't do this row by row thing. It's...

TheSQLGuru (12/9/2015)

---

You simply MUST NOT order all rows to get one!! That is an immensely costly operation!

One quick-and-easy way is to get min and max ID values and create a...

Or may be quite opposite

declare @tbl table(dt datetime, completeName varchar(100))

INSERT INTO @tbl values('20151130', 'Me'), ('20151110', 'other people'), ('20151201', 'candidat 1'), ('20151201', 'candidat 2')

SELECT * FROM @tbl order by dt ;

select...

Pivot is just a convenience method to say

select id,title,

ParentIdLevel1 = max(case ParentLevel when 1 then ParentId end),

ParentTitleLevel1 = max(case ParentLevel when 1 then ParentTitle end),

ParentIdLevel2 = max(case ParentLevel when...

Most probably there were a lot of DELETEs an INSERTs on a table with 1million+ rows. So IDs hardly are evenly distributed on (1 .. count(*)) interval or even on...

See my next post, don't know how to delete this error posting.

In short, no you can't. You really need some fancy things like those you mentioned above to tie new short id back to original GUID.

I recall OP was saying some rows do have not null IndexStartDate.

Provided explicitly defined intervals do not overlap try this to infer unknown start dates

declare @AsAtDate Datetime = '2015-06-01';

with cte as (

select *,

strt = isnull([IndexStartDate],lag([IndexEndDate],1,cast('19710101' as date))

over(partition by [PatientID] order...

Original table looks like bill of material type which is generally not restricted to tree structure, it may be a network hierarchy as well.

I added 'bike20' to demonstrate it.

I'd suggest...

Luis Cazares (12/7/2015)

---

Some other ways. The last one is similar to serg's, but should perform better because it should read less rows. However, I can't assure it will perform better...

You've said letter.

What about strings like Select @name = 'test2' or Select @name = ' test' , what result is expected?

More general way to do things like that

select GoodId

from T1

group by GoodId

having count(distinct case when LocNum in (501,502) then LocNum end) = 2

--having count(distinct case when LocNum in...

Looks like Sql CLR is your last resource. Create CLR proc3 which will call proc1, consume result sets and insert them into temp tables. In proc2 create temp tables and...