May 18, 2013 at 5:04 am
What I need is to be able to find out when a customer next called in from the date I have select with the next date & time on any Purchase, does don't need to be same. It’s like using MIN & MAX but that does not work on this query as it select the same date & time for both.
SELECT ID, Name, CallDateTime, Num, RC
FROM History
WHERE (CallDateTime > CONVERT(DATETIME, '2013-05-01 00:00:00', 102)) AND (Outcome = 'Called')
As you can see in the query above that all the data is in one overall table called History, this records all the purchases.
So I want to know that if a customer after the 1/05/2013 called in with the outcome of called what was he next purchases date, some customer might not have come so that can stay blank.
So the query is like this now
ID NameCallDateTime Num RC
3936803Name101/05/2013 11:16:2784Called
5211387Name201/05/2013 12:14:21604Called
5185689Name301/05/2013 12:15:28298Called
4811923Name401/05/2013 12:29:36170Called
but i also want it to show the below,
ID Name CallDateTime Num RC Next CallDateTimeTotal Number Of Days
3936803Name101/05/2013 11:16 84Called 04/05/2013 11:16 3
5211387Name201/05/2013 12:14 604Called 04/05/2013 12:14 3
5185689Name301/05/2013 12:15 298Called 04/05/2013 12:15 3
4811923Name401/05/2013 12:29 170Called 04/05/2013 12:29 3
This is the query I have at the moment BUT not show how to add two more columns to it they are next called in date & time after the first called in. I want it to show the next time & date does not matter what the RC code is next visit.
So I want mine output to show the below, adding the next called time & date and how many days it take from the first date to the next date,
May 18, 2013 at 6:46 pm
Hi,
Could you please post a script to show the structure of History table ?
About Next CallDateTime : This colum already exists in the table or it should be calculated ?
May 20, 2013 at 1:05 am
Next CallDateTime information is in the data but its not called Next CallDateTime, its CallDateTime.
So if records 1 CallDateTime was after the select date e.g 01/05/2013 then they revisited on the 05/05/2013 and then 10/05/2013.
on the nextcalldatetime i just want to see CallDateTime as 01/05/2013 & the next CallDateTime as 05/05/2013
[dbo].[History](
[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,
alezar (5/18/2013)
Hi,Could you please post a script to show the structure of History table ?
About Next CallDateTime : This colum already exists in the table or it should be calculated ?
May 20, 2013 at 8:14 am
Hi Tyekhan and welcome to the forums. The main issue here is that we have no idea what you are trying to do. We can't see your screen, we don't know the project and we have no idea what your tables are like. Can you please post ddl (create table statements), sample data (insert statements) and desired output based on your sample data? Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2013 at 1:14 pm
Hi, as Sean asked it will be great if you could improve description of your req.
What I understand is that if you have two different rows for NAME1, one with calldatetime first of may and the second fifth of may, you want to show both in the result. If more rows are available you want to show always the first date and the following.
Please confirm if this is correct.
May 20, 2013 at 5:13 pm
Yes that's what i want, if anyone can help me with it.thanks
alezar (5/20/2013)
Hi, as Sean asked it will be great if you could improve description of your req.What I understand is that if you have two different rows for NAME1, one with calldatetime first of may and the second fifth of may, you want to show both in the result. If more rows are available you want to show always the first date and the following.
Please confirm if this is correct.
May 20, 2013 at 5:34 pm
Tyekhan (5/20/2013)
Yes that's what i want, if anyone can help me with it.thanksalezar (5/20/2013)
Hi, as Sean asked it will be great if you could improve description of your req.What I understand is that if you have two different rows for NAME1, one with calldatetime first of may and the second fifth of may, you want to show both in the result. If more rows are available you want to show always the first date and the following.
Please confirm if this is correct.
Great. What do you want to do if NAME1 has 3 calls each on the first and fifth of May?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 21, 2013 at 3:22 am
I would like the first call on both day (first & fifth), they will only be 1 for each day any way as it come every 2-4 days.
Jeff Moden (5/20/2013)
Tyekhan (5/20/2013)
Yes that's what i want, if anyone can help me with it.thanksalezar (5/20/2013)
Hi, as Sean asked it will be great if you could improve description of your req.What I understand is that if you have two different rows for NAME1, one with calldatetime first of may and the second fifth of may, you want to show both in the result. If more rows are available you want to show always the first date and the following.
Please confirm if this is correct.
Great. What do you want to do if NAME1 has 3 calls each on the first and fifth of May?
May 21, 2013 at 6:25 am
Tyekhan (5/21/2013)
I would like the first call on both day (first & fifth), they will only be 1 for each day any way as it come every 2-4 days.Jeff Moden (5/20/2013)
Tyekhan (5/20/2013)
Yes that's what i want, if anyone can help me with it.thanksalezar (5/20/2013)
Hi, as Sean asked it will be great if you could improve description of your req.What I understand is that if you have two different rows for NAME1, one with calldatetime first of may and the second fifth of may, you want to show both in the result. If more rows are available you want to show always the first date and the following.
Please confirm if this is correct.
Great. What do you want to do if NAME1 has 3 calls each on the first and fifth of May?
Perfect. If no one get's to it before me (I'm on my way to work inn 5), I'll give it a crack tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2013 at 2:43 pm
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?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 25, 2013 at 2:59 pm
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?
May 25, 2013 at 7:49 pm
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
Change is inevitable... Change for the better is not.
May 28, 2013 at 5:21 am
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.
May 30, 2013 at 7:33 am
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
Change is inevitable... Change for the better is not.
May 30, 2013 at 7:49 am
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.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply