Jeff Moden (5/25/2013)

---

Tyekhan (5/25/2013)

---

No solution found that works yet, Still need help 🙁

Jeff Moden (5/25/2013)

---

Dammit. I'm sorry. I lost track of this thread and it took me a while to find it again.

Have you found a solution for your problem or do you still need some help?

Ok... First, I suspect that part of the reason why most folks were reluctant to help you is because there was no test data and you didn't explain things real well. For example, it's suicide to try to use name as a unique key but you didn't say anything about the other columns being unique by person. It makes it real tough to come up with something decent. Even I made an assumption that you can easily change... I assumed that NUM was going to be unique Could be wrong there and you'd need to change it but you can't correct a blank piece of paper.

Please see the first link in my signature line before you post another problem. If you follow the suggestions in that article for how to post data, a whole lot more people will try to help a whole lot more quickly.

Here's all of the code. As is normal for me, the details are in the comments. It includes the construction of a million row test table just to show it runs fairly fast.

First, the test data with some suggested indexes.

--=======================================================================================

-- Build a million row test table for this problem.

-- Nothing in this section is a part of a solution except maybe for indexes.

-- This takes less than a minute on my 11 year old desktop box.

-- There is a very small chance you could get a duplicate error in this section

-- If that happens, just run the section again.

--=======================================================================================

--===== Conditionally drop the test table(s) to make reruns easier in SSMS

IF OBJECT_ID('tempdb..#Randomize','U') IS NOT NULL DROP TABLE #Randomize;

IF OBJECT_ID('tempdb..#History' ,'U') IS NOT NULL DROP TABLE #History;

GO

--===== Create some random call dates from random ContactID's.

-- This generates random call dates from 2000-01-01 through 2013-05-25 (not 26).

RAISERROR('Randomizing ContactID''s and CallDateTime''s...',0,1) WITH NOWAIT;

SELECT TOP 1000000

ContactID = ISNULL(ABS(CHECKSUM(NEWID()))%19977+1,0),

CallDateTime = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'20000101','20130526')

+ CAST('20000101' AS DATETIME)

INTO #Randomize

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

--===== Create/Populate the History table by joining a known source to the random data.

-- The ISNULL's make the columns NOT NULL.

-- Also notice the ORDER BY is building the table in the same order expected

-- in real life so the HistoryID simulates what happens in the real table.

-- The History table may end up with less than a million rows because the source

-- table has gaps in the ContactID's like any real table.

RAISERROR('Building/populating the #History table...',0,1) WITH NOWAIT;

SELECT HistoryID = IDENTITY(INT,1,1),

Name = ISNULL(c.LastName,'') + ',' + ISNULL(c.FirstName,''),

CallDateTime = ISNULL(r.CallDateTime,0),

Num = ISNULL(c.Phone,''),

RC = ISNULL('Called','')

INTO #History

FROM AdventureWorks.Person.Contact c

JOIN #Randomize r

ON c.ContactID = r.ContactID

ORDER BY r.CallDateTime, r.ContactID

;

--===== Add a unique clustered index to the #History table.

-- Note that this is NOT a PK.

RAISERROR('Adding the UNIQUE Clustered Index...',0,1) WITH NOWAIT;

CREATE UNIQUE CLUSTERED INDEX IXC_#History_CallDateTime_HistoryID

ON #History (CallDateTime,HistoryID)

;

--===== Add a non-clustered PK

RAISERROR('Adding the NON-Clustered PK...',0,1) WITH NOWAIT;

ALTER TABLE #History

ADD PRIMARY KEY NONCLUSTERED (Num,CallDateTime) WITH FILLFACTOR = 90

;

... and this is one solution. I used "Divide'n'Conquer" methods so that you can run the code within each CTE separately to see what's happening. The code is physically a bit longer than what someone else might come up with but it's damned easy to understand and troubleshoot this way. The performance isn't bad, either.

--=======================================================================================

-- Solve the problem

--=======================================================================================

DECLARE @pGivenDT DATETIME, --This could be a parameter for a stored proc.

@StartDay DATETIME,

@nextday DATETIME

;

SELECT @pGivenDT = '2013-05-01',

@StartDay = DATEDIFF(dd,0,@pGivenDT),

@nextday = DATEADD(dd,1,@StartDay)

;

WITH

cteFindGiven AS

( --=== Find the calls on the day desired

SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY Num ORDER BY Num,CallDateTime),

HistoryID,

Name,

CallDateTime,

Num,

RC

FROM #History

WHERE CallDateTime >= @StartDay

AND CallDateTime < @nextday

),

cteFindNext AS

( --=== Find the first call for each Num after the given day.

SELECT Num,

CallDateTime = MIN(CallDateTime)

FROM #History

WHERE CallDateTime >= @nextday

GROUP BY Num

)

--===== Join the two CTE's to produce the desired output

SELECT g.HistoryID,

g.Name,

g.CallDateTime,

g.Num,

g.RC,

[Next CallDateTime] = n.CallDateTime,

[Total Number of Days] = DATEDIFF(dd,g.CallDateTime,n.CallDateTime)

FROM cteFindGiven g

LEFT JOIN cteFindNext n --Remove "LEFT" to get just the matches after QA

ON n.Num = g.Num

WHERE g.RowNum = 1

;

Like I said, if the "Num" column is the wrong thing to consider to be unique, then we'll have to change a couple of indexes and a couple of column names.