Tyekhan (6/4/2013)
Hope this helps a bit,--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
[HistoryID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SystemID] [tinyint] NOT NULL,
[PID] [int] NOT NULL,
[Name] [varchar](35) NOT NULL,
[Source] [varchar](32) NOT NULL,
[SourceField] [varchar](32) NOT NULL,
[SourceID] [varchar](32) NOT NULL,
[CallDateTime] [datetime] NOT NULL,
[Num] [varchar](15) NOT NULL,
[CallData] [varchar](240) NOT NULL,
[ANI] [varchar](15) NOT NULL,
[RC] [varchar](5) NOT NULL,
[ID] [int] NOT NULL,
--===== Insert the test data into the test table
INSERT INTO #History
(ID, Name, CallDateTime, RC, Num)
SELECT '26241','Carr_CHS_20120227','Feb 27 2012 2:07PM','Called','01912' UNION ALL
SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443' UNION ALL
SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513' UNION ALL
SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519' UNION ALL
SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270' UNION ALL
SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938' UNION ALL
SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924' UNION ALL
SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875' UNION ALL
SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849' UNION ALL
SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476' UNION ALL
SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228' UNION ALL
SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048' UNION ALL
SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410' UNION ALL
SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379' UNION ALL
SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612' UNION ALL
SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227' UNION ALL
SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209' UNION ALL
SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240' UNION ALL
SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198' UNION ALL
SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478'
I'm using Server 2008 Management Studio.
ok....will throw in an idea for you to consider.
first off...the script you posted above will not work...you need to test this before you post.
I leave you to sort that..
second you fail to provide data for all columns
third...your sample of dates (days) is only two dates....???
anyways, based on what I think you need here is some code to play with
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..History','U') IS NOT NULL
DROP TABLE History
--===== Create the test table with
CREATE TABLE History
(
[HistoryID] [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 History
(ID, Name, CallDateTime, RC, Num)
SELECT '26241','Carr_CHS_20120227','Feb 27 2012 2:07PM','Called','01912' UNION ALL
SELECT '26263','Carr_CHS_20120227','Feb 27 2012 2:10PM','Called','01443' UNION ALL
SELECT '39395','Carr_CMM_20120227','Feb 27 2012 2:14PM','Called','01513' UNION ALL
SELECT '27237','Carr_CHS_20120227','Feb 27 2012 4:14PM','Called','01519' UNION ALL
SELECT '27254','Carr_CHS_20120227','Feb 27 2012 4:16PM','Called','01270' UNION ALL
SELECT '27440','Carr_CHS_20120227','Feb 27 2012 4:34PM','Called','01938' UNION ALL
SELECT '40522','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01924' UNION ALL
SELECT '40528','Carr_CMM_20120227','Feb 27 2012 4:48PM','Called','01875' UNION ALL
SELECT '40595','Carr_CMM_20120227','Feb 27 2012 4:58PM','Called','34849' UNION ALL
SELECT '27712','Carr_CHS_20120227','Feb 27 2012 5:03PM','Called','30476' UNION ALL
SELECT '40654','Carr_CMM_20120227','Feb 27 2012 5:08PM','Called','82228' UNION ALL
SELECT '40664','Carr_CMM_20120227','Feb 27 2012 5:09PM','Called','13048' UNION ALL
SELECT '40677','Carr_CMM_20120227','Feb 27 2012 5:11PM','Called','81410' UNION ALL
SELECT '40740','Carr_CMM_20120227','Feb 27 2012 5:19PM','Called','52379' UNION ALL
SELECT '28262','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','01612' UNION ALL
SELECT '28263','Carr_CHS_20120227','Feb 27 2012 6:17PM','Called','0148227' UNION ALL
SELECT '41207','Carr_CMM_20120227','Feb 27 2012 6:47PM','Called','02891209' UNION ALL
SELECT '28654','Carr_CHS_20120227','Feb 27 2012 7:00PM','Called','014240' UNION ALL
SELECT '41393','Carr_CMM_20120227','Feb 27 2012 7:07PM','Called','01642198' UNION ALL
SELECT '30313','Carr_CHS_20120227','Feb 28 2012 12:36PM','Called','01142478'
SELECT HistoryID, SystemID, PID, Name, Source, SourceField, SourceID, CallDateTime, Num, CallData, ANI, RC, ID
FROM History
ORDER BY Name, CallDateTime
;
with CTE as
(
SELECT ID, Name, CallDateTime, Num, RC, row_number() over(PARTITION BY [Name] order by calldatetime ) as rn
FROM History
WHERE (CallDateTime > CONVERT(DATETIME, '2012-02-27 16:48:00.000', 102)) AND (rc = 'Called')
)
SELECT firstcall.ID
, 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);
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day