Addendum:

To clarify , the WHILE @@ROWCOUNT=3 is just a way of looping until there are no more rows, it could just as easily have been WHILE @@ROWCOUNT<>0, but I...

ColdCoffee (1/24/2011)

---

Even i have a doubt in the code , Magoo... Do we really need UNPIVOT ? i guess, your code will work even without...

Dave Ballantyne (1/24/2011)

---

Im speaking from a point of pure ignorance about your code, ie i havent run in through, 😉 , but

WHILE @@ROWCOUNT=3

So , you are inserting...

Dave Ballantyne (1/24/2011)

---

mister.magoo (1/24/2011)

---

Dave - Good point, thanks - do you think SQL will ever Parallelise (?) a while loop - not that I am arguing against MAXDOP 1, just...

Jeff Moden (1/23/2011)

---

OMG! How did the old cartoon series used to say it? "Ahhhh, Magoo... You've done it again!". Well done. NOW I can go to...

Jeff Moden (1/23/2011)

---

Slight departure from the norm on this thread. One of our own made an absolute killing on a nasty problem. See "mister.magoo"s final code post on...

Jeff Moden (1/23/2011)

---

Glad to hear that my code isn't the only slow and ugly one on...

Jeff Moden (1/23/2011)

---

Glad to hear that my code isn't the only slow and ugly one on...

Sorry Peso, but while testing mine for more complicated datasets, I came across this one that your latest does not cope with...

INSERT INTO MVTS

(idMvt1,idMvt2,idMvt3)

SELECT 1, 2, 3 UNION ALL

SELECT...

... In which case, I guess you need an INSERT trigger that checks whether the current StartDate or EndData overlap with an existing one...

something like...

CREATE TRIGGER PreventOverlap

AFTER INSERT, UPDATE

AS

IF...

I don't know a good way - you could do it with a trigger but that's not nice...

Have you considered storing the data differently, for example having a table of...

In the interest of seeing a SQL solution even though you have a CLR solution...

I offer this

First just the solution (which assumes you have added an "ID" column)

SELECT *

FROM mvts

WHERE...

I have to disagree about the root problem.

There are two problems that I see.

1) The table variable with 200 rows is being SCANNED 390,000 times because of the IN.

...

You know you can post the sqlplan here and protect your security without too much trouble...

Save the execution plan as sqlplan.

Edit the sqlplan in notepad.

Replace ALL (Ctrl-h) myTable with GenericTable

replace...

Is it safe to say that the latest call for any phone number will also correspond to the highest "id" for that phone number?

SELECT

log.id,

log.DateCall,

...