Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Help with next date in SQL query Expand / Collapse
Author
Message
Posted Saturday, May 25, 2013 2:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 8, 2013 3:05 AM
Points: 12, Visits: 98
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?
Post #1456819
Posted Saturday, May 25, 2013 7:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:55 PM
Points: 35,618, Visits: 32,214
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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1456827
Posted Tuesday, May 28, 2013 5:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 8, 2013 3:05 AM
Points: 12, Visits: 98
Thank you for the code i have just tested it, its coming up with the below code error,

'Randomizing ContactID's and CallDateTime's...

(1000000 row(s) affected)
Building/populating the #History table...
Msg 208, Level 16, State 1, Line 19
Invalid object name 'AdventureWorks.Person.Contact'.,



Hope you could help with the error.


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.
Post #1457266
Posted Thursday, May 30, 2013 7:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:55 PM
Points: 35,618, Visits: 32,214
So, any feedback on my previous comment of...

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.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1458220
Posted Thursday, May 30, 2013 7:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 8, 2013 3:05 AM
Points: 12, Visits: 98

Yes the num column is unique, but the best thing to use would be ID as the same records would have the same ID.

Jeff Moden (5/30/2013)
So, any feedback on my previous comment of...

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.
Post #1458230
Posted Thursday, May 30, 2013 10:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:55 PM
Points: 35,618, Visits: 32,214
Tyekhan (5/30/2013)

Yes the num column is unique, but the best thing to use would be ID as the same records would have the same ID.

Jeff Moden (5/30/2013)
So, any feedback on my previous comment of...

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.


WHICH ID? Certainly not the HistoryID, right?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1458324
Posted Thursday, May 30, 2013 3:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 8, 2013 3:05 AM
Points: 12, Visits: 98
Sorry the PID in the table that is unique ID per customer. --- [PID] [int] NOT NULL,




Jeff Moden (5/30/2013)
Tyekhan (5/30/2013)

Yes the num column is unique, but the best thing to use would be ID as the same records would have the same ID.

Jeff Moden (5/30/2013)
So, any feedback on my previous comment of...

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.


WHICH ID? Certainly not the HistoryID, right?
Post #1458471
Posted Tuesday, June 4, 2013 6:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 8, 2013 3:05 AM
Points: 12, Visits: 98
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.
Post #1459707
Posted Tuesday, June 4, 2013 9:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 1,942, Visits: 20,007
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
Post #1459841
Posted Wednesday, June 5, 2013 4:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 8, 2013 3:05 AM
Points: 12, Visits: 98
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);

Post #1460135
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse