I am glad I could help!  I found the problem to be interesting and fun to work on.

Been a while since I had a fun, challenging SQL query like that...

This sounds like a homework assignment...

Question 1 is pretty trivial as you are just using datediff on the Cases table with a WHERE to put the date greater than or...

I didn't even notice it dropped trip id 28754... Ok, did ore refactoring and now I get 19 results with most being overlaps of 2 except the last one which...

I think it is close, but thinking we are going about this from the wrong approach.

How about something like this:

-- Get initial data
WITH [cte]
AS
(
SELECTDISTINCT
[t].[rn]  AS [rn]
,...

So, if I understand correctly, as soon as there is an overlap, it is grouped by that.  So, if you have for example the following:

INSERT #TestTrips ([rn],...

I do agree with you Jeff, I have xp_cmdshell enabled in a lot of places.  You just have to be careful with it.  It is very easy to turn it...

That is what I said it would do: "The new method above only allows 2 trips per overlap grouping.  So using my example above of trip 1, 2, and 3...

I'm not seeing duplicates... change the "order by" to be by "grouping" and you will see that it is showing you multiple overlaps (ie groups); not duplicates.  If trip 1...

do you have some sample data where it is doing duplicates?  There shouldn't be duplicates as it is doing a distinct already in the CTE...

Which is the slow portion of that query?  Are ALL of the UPDATEs and INSERTs slow and taking roughly the same time to complete?

Is this a "run-once" query or is...

Yep; xp_cmdshell would solve that, but you'd need to enable it and some auditors don't like having that enabled.

UNC paths are likely going to be the safest and most reliable...

What is your disk I/O?

One thing that can help is to change your "between" to a > and <.  Between comparisons have been known to have performance issues.

On top of...

Network drive letters are mapped per user,  not per machine.  So mapping the drive with "net use" will map the drive for you not the SQL Server Service account.  You...

The first issue is easy to fix - you are only showing "t" not "t2". So when you join things t and t2 are he ones that overlap.  If you...

• This reply was modified 5 years, 4 months ago by Mr. Brian Gale.
• This reply was modified 5 years, 4 months ago by Mr. Brian Gale.

I agree with Ken here that the application side is better suited for this.

Personally, I would refrain from using undocumented features as they can change behavior OR be removed completely...