Thanks

I have added PID to the data below The PID is unique ID per customer so on the below i want to see

PID = 22

calldatetime 25/02/2012

NextCallDateTime = 28/02/2013

numberofdays = 3

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..HistoryTEST','U') IS NOT NULL

DROP TABLE HistoryTEST

--===== Create the test table with

CREATE TABLE HistoryTEST

(

[HistoryTESTID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[SystemID] [tinyint] NULL,

[PID] [int] NULL,

[Name] [varchar](35) NOT NULL,

[Source] [varchar](32) NULL,

[SourceField] [varchar](32) NULL,

[SourceID] [varchar](32) NULL,

[CallDateTime] [datetime] NULL,

[Num] [varchar](15) NULL,

[CallData] [varchar](240) NULL,

[ANI] [varchar](15) NULL,

[RC] [varchar](10) NULL,

[ID] [int] NULL,

)

--===== Insert the test data into the test table

INSERT INTO HistoryTEST

(ID, Name, CallDateTime, RC, Num, PID)

SELECT '26241','Carr_CHS_20120227','Feb 25 2012 3:07PM','Called','01912','22' UNION ALL

SELECT '28252','Carr_CHS_20120227','Feb 28 2012 4:07PM','PCO','01912','22' UNION ALL

SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443','23' UNION ALL

SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513','24' UNION ALL

SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519','25' UNION ALL

SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270','26' UNION ALL

SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938','27' UNION ALL

SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924','28' UNION ALL

SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875','29' UNION ALL

SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849','30' UNION ALL

SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476','31' UNION ALL

SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228','32' UNION ALL

SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048','33' UNION ALL

SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410','34' UNION ALL

SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379','35' UNION ALL

SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612','36' UNION ALL

SELECT '28265','Carr_CHS_20120227','Mar 2 2012 6:17PM','Other','01612' ,'36'UNION ALL

SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227','37' UNION ALL

SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209','38' UNION ALL

SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240','39' UNION ALL

SELECT '28654','Carr_CHS_20120227','Feb 28 2012 7:00PM','KKT','014240','40' UNION ALL

SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198','41' UNION ALL

SELECT '41345','Carr_CMM_20120227','Mar 4 2012 7:07PM','NIG','01642198' ,'41'UNION ALL

SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478','42'

SELECT HistoryTESTID, SystemID, PID, Name, Source, SourceField, SourceID, CallDateTime, Num, CallData, ANI, RC, ID

FROM HistoryTEST

ORDER BY Name, CallDateTime

;

with CTE as

(

SELECT ID, PID,Name, CallDateTime, Num, RC, row_number() over(PARTITION BY [Name] order by calldatetime ) as rn

FROM HistoryTEST

WHERE (CallDateTime > CONVERT(DATETIME, '2012-02-27 16:48:00.000', 102)) AND (rc = 'Called')

)

SELECT firstcall.ID

, firstcall.PID

, firstcall.Name

, firstcall.CallDateTime

, firstcall.Num

, firstcall.RC

, nextcall.CallDateTime AS NextCallDateTime

, datediff(d,firstcall.CallDateTime,nextcall.CallDateTime) as numberofdays

FROM

cte AS firstcall INNER JOIN cte AS nextcall

ON firstcall.Name = nextcall.Name

WHERE (firstcall.rn = 1)

AND (nextcall.rn = 2);