Dwain,

LOL... after 2hrs, it tossed...

"An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown."

...on a server with 64gb RAM. Last I looked after 30min before...

Hmmm...no joy. No matter where I put 'sp_getapplock' in the...

dwain.c (6/30/2012)

---

Sorry but I do know why the CTE returns 0 rows. You must still have the population equality check on the last line. Try taking that out.

You'll...

G...

Here's the DDL...

USE [CentralServices]

GO

/****** Object: Table [dbo].[CollectorTransitionHistory] Script Date: 06/30/2012 12:24:29 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[CollectorTransitionHistory](

[endpointId] [int] NOT NULL,

[collectorId] [int] NOT NULL,

[dailyReadStartDate] [smalldatetime] NOT...

>>Do you want to have dup districts? Means can a tupple be like this 'dist1,dist1,dist1' = 300 if dist1 has 100 rows?

No. All old districts must be included, but distinct...

I believe it's multiple calls, though given the nature, I'm not positive.

I've never used sp_getapplock... but from what I've gleamed, I would copy the DML from this SP to another...

Thanks Lynn. I'd already checked the index, and added 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'. I just posted the original SP code. Still no joy.

This is a great CTE study for me...

BTW, I ran it against this server, and it ran in 7min...however, no rows were returned.

Singles - you can limit this to just...

dwain.c (6/28/2012)

---

I think this SQL is the correct algorithm, however I have not been able to arrive at a way yet to prune the resultset of unwanted results to pass...

dwain.c (6/28/2012)

---

Hello again Les!

Since I couldn't stop thinking about this today, I decided to learn what I could so as to ask you my questions prior to the weekend. ...

dwain.c (6/28/2012)

---

Les,

I did take a quick 15 minute look. It appears you didn't implement Chris's speed improvement suggestions. However I doubt that will be sufficient for the size...

jeffem (6/28/2012)

---

Okay, wow. Now that I'm thinking about this after some sleep, I see where I have erred.

We must divide by each combination reduction factor (2! for couples, 3! for...

Jeff,

jeffem (6/19/2012)

---

In the vein of being pedantic (because that's the only way I can contribute on this one 😉 ), probabilistically speaking, combinations are distinct. Permutations have repeats, in all...

Dwain,

Since #1 is not unique, unless I misunderstood, it shouldn't be included. Only 2, 3, 4 are unique in the set.

~Les

Dwain,

I must be doing something wrong here. I'm using the CTE to attempt the same logic as SW7 to resolve those new Sweden districts with two old districts (doubles), but...