April 28, 2010 at 8:30 pm
I work for a telemarketing company and I have a process that loads telemarketing agent records every 15 minutes into a table as the example below:
CREATE TABLE [dbo].[TALK](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LoadId] [int] NOT NULL,
[dCalldate] [datetime] NOT NULL,
[RepId] [int] NOT NULL,
[CurrentTalkTime] [int] NULL,
[LastTalkTime] [int] NULL DEFAULT ((0)),
[IncrementalTalkTime] [int] NULL DEFAULT ((0)),
[dDateCreated] [datetime] NOT NULL default (getdate()))
INSERT TALK (LoadId, dCalldate, RepId, CurrentTalkTime, LastTalkTime,IncrementalTalkTime, dDateCreated) VALUES( 1 ,'2010-04-28', 510016, 120,0,120, '2010-04-28 14:52:03.520')
INSERT TALK (LoadId, dCalldate, RepId, CurrentTalkTime, LastTalkTime,IncrementalTalkTime, dDateCreated) VALUES( 1 ,'2010-04-28', 510017, 10,0, 10, '2010-04-28 14:52:03.520')
This first load (loadid 1) loaded 2 rep records.They both have a TalkTime value, but becuase this is the first load of the day there is no LastTalkTime value and the IncrementalTalk is Current - Last.
The following is a snapshot of the table after the 2nd load.
INSERT TALK (LoadId, dCalldate, RepId, CurrentTalkTime, LastTalkTime,IncrementalTalkTime, dDateCreated) VALUES( 2 ,'2010-04-28', 510016, 135, 120, 15, '2010-04-28 15:00:00.000')
INSERT TALK (LoadId, dCalldate, RepId, CurrentTalkTime, LastTalkTime,IncrementalTalkTime, dDateCreated) VALUES( 2 ,'2010-04-28', 510017, 15,10, 5, '2010-04-28 15:00:00.000')
INSERT TALK (LoadId, dCalldate, RepId, CurrentTalkTime, LastTalkTime,IncrementalTalkTime, dDateCreated) VALUES( 2 ,'2010-04-28', 510018, 5,0, 5, '2010-04-28 15:00:00.000')
Can anyone please help me with a query that can pull the value of the CurrentTalk of the prior record to set it to the LastTalk field of the next record so that the Incremental value can be calculated? (Hope that made sense)
Thanks,
Ninel
April 28, 2010 at 9:13 pm
ninel,
First, thanks for supplying the DDL and DML to create some test data... that helps!
Can anyone please help me with a query that can pull the value of the CurrentTalk of the prior record to set it to the LastTalk field of the next record so that the Incremental value can be calculated? (Hope that made sense)
Can you show us, based on the test data that you supplied, what you want the results to look like? There are still several questions, but I think that if you can provide that for us, we'll be able to see what you're trying to do and will be able to help you out.
Thanks!
Edit: to answer your question: NO, you don't need a cursor. If you supply the requested information, many people out here will be able to post a good set-based solution.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 29, 2010 at 12:48 am
By default you don't need a cursor ! :w00t:
double check your data !
I think you misplaced column names for [CurrentTalkTime] and [IncrementalTalkTime].
VALUES( 2 ,'2010-04-28', 510016, 135, 120, 15, '2010-04-28 15:00:00.000')
Considered the column names, I would suppose the 135 would be the IncrementalTalkTime and 15 would be the CurrentTalkTime.
I would load the calculated columns containing the value of 0 and then process all 0 using the process as described in Jeff's great article on running totals.
http://www.sqlservercentral.com/articles/T-SQL/68467/
At least test it !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 29, 2010 at 2:52 am
I had a similar requirement. I handled it through a Stored Procedure.. and was calliing the same from a .Net application.
May be this can help you. I hope I have understood the requirments correctly.
CREATE TABLE
TestIncremental
(
ID [INT] IDENTITY(1,1) NOT NULL,
TelePersonID [INT] NOT NULL,
RecordDate [DateTime],
CurrentTalkTime [INT],
LastTalkTime [INT] NOT NULL,
IncrementalTalkTime [INT]
)
/* Req: 1.) FOR EACH DAY THE FIRST RECORD OF THE TELE PERSON WILL HAVE INCREMENTAL TALKTIME AS THE CURRENTTALKTIME SINCE THAT WILL BE THE FIRST ENTRY OF THE DAY FOR THAT TELE PERSON.
2.) ALL SUBSEQUENT ENTRIES WILL HAVE LASTTALKTIME... AS THE CURRENT TALK TIME OF THE LAST INSERTED RECORD FOR Current DAY OF THAT PARTICULAR TELEPERSON
AND INCREMENTALTALKTIME WILL SHOW THE SUM TOTaL OF LASTINCREMENTAL + CurrentTalkTime
*/
ALTER PROCEDURE sp_InsertTeleRecord
@telePersonID INT,
@currentTalkTime INT
AS
DECLARE @dayCount INT
DECLARE @currentDateTime DateTime
DECLARE @lastLogID INT
DECLARE @lastInrementalTalkTime INT
Declare @lastCurrentTalkTime INT
SET @dayCount = 0
SET @currentDateTime = (Select GetDate())
SET @lastLogID = 0
--First Find IF THIS IS THE FIRST RECORD OF THE DAY... IF IT IS FIRST LOG OF THE DAY.. SIMPLY INSERT THE
--RECORD
--TO FIND OUT WHETHER THIS IS THE FIRST RECORD OF THE DAY OR NOT, SIMPLY CALCULATE WHETHER ANY RECORD EXIST FOR THE CURRENT
--DATE FOR THAT PARTICULAR Teleperson ID
SET @dayCount = (SELECT Count(*) FROM TestIncremental WHERE TelePersonID = @telePersonID AND
DAY(RECORDDATE) = DAY(@currentDateTime))
IF (@dayCount < 1 )
BEGIN
--THIS IS THE FIRST LOG OF THE DAY FOR THE PARTICULAR TELLER ID
INSERT INTO TestIncremental (TELEPERSONID, RECORDDATE, CurrentTalkTime, LASTTALKTIME, INCREMENTALTALKTIME)
VALUES(@telePersonID, GETDATE(), @CurrentTalkTime, 0, @currentTalkTime)
END
ELSE
BEGIN
--ALREADY A RECORD EXISTS AND YOU NEED TO GET THE DETAILS OF THE THE LAST ENTRY ...
--WE NEED LAST Record's INCREMENTALTALKTIME AND CURRENTTALKTIME
SELECT TOP 1 @lastInrementalTalkTime = INCREMENTALTALKTIME, @lastCurrentTalkTime = CurrentTalkTime FROM
TestIncremental WHERE TELEPERSONID = @telePersonID
ORDER BY RecordDate DESC
--USE THE ABOVE VALUES TO CREATE THE NEXT RECORD
INSERT INTO TestIncremental (TELEPERSONID, RECORDDATE, CurrentTalkTime, LASTTALKTIME, INCREMENTALTALKTIME)
VALUES(@telepersonID, GETDATE(), @currentTalkTime, @lastCurrentTalkTime, @lastInrementalTalkTime + @currentTalkTime)
END
Regards,
Kunal Uppal
April 29, 2010 at 2:55 am
Lots of alternatives here (triggers, stored procedures...).
Here's one of the more interesting ones:
USE tempdb;
GO
CREATE TABLE [dbo].[TALK]
(
[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[LoadId] [int] NOT NULL,
[dCalldate] [datetime] NOT NULL,
[RepId] [int] NOT NULL,
[CurrentTalkTime] [int] NULL,
[dDateCreated] [datetime] NOT NULL DEFAULT (GETDATE()),
);
GO
CREATE UNIQUE INDEX [UQ dbo.TALK LoadId, RepId (CurrentTalkTime)]
ON dbo.TALK (LoadId, RepId) INCLUDE (CurrentTalkTime);
GO
INSERT dbo.TALK (LoadId, dCalldate, RepId, CurrentTalkTime, dDateCreated) VALUES( 1 ,'2010-04-28', 510016, 120, '2010-04-28 14:52:03.520');
INSERT dbo.TALK (LoadId, dCalldate, RepId, CurrentTalkTime, dDateCreated) VALUES( 1 ,'2010-04-28', 510017, 10, '2010-04-28 14:52:03.520');
INSERT dbo.TALK (LoadId, dCalldate, RepId, CurrentTalkTime, dDateCreated) VALUES( 2 ,'2010-04-28', 510016, 135, '2010-04-28 15:00:00.000');
INSERT dbo.TALK (LoadId, dCalldate, RepId, CurrentTalkTime, dDateCreated) VALUES( 2 ,'2010-04-28', 510017, 15, '2010-04-28 15:00:00.000');
INSERT dbo.TALK (LoadId, dCalldate, RepId, CurrentTalkTime, dDateCreated) VALUES( 2 ,'2010-04-28', 510018, 5, '2010-04-28 15:00:00.000');
GO
CREATE FUNCTION dbo.CallData
(
@CurrentLoadId INTEGER,
@RepId INTEGER
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT LastTalkTime = ISNULL(Previous.CurrentTalkTime, 0),
IncrementalTalkTime = ISNULL(This.CurrentTalkTime - Previous.CurrentTalkTime, This.CurrentTalkTime)
FROM dbo.TALK This
LEFT
JOIN dbo.TALK Previous
ON Previous.LoadId = This.LoadId - 1
AND Previous.RepId = This.RepId
WHERE This.RepId = @RepId
AND This.LoadId = @CurrentLoadId;
GO
CREATE VIEW dbo.TalkData
WITH SCHEMABINDING
AS
SELECT T.LoadId, T.dCalldate, T.RepId, T.CurrentTalkTime,
CA.LastTalkTime, CA.IncrementalTalkTime, T.dDateCreated
FROM dbo.TALK T
CROSS
APPLY dbo.CallData (T.LoadId, T.RepId) CA
GO
SELECT *
FROM dbo.TalkData;
GO
DROP VIEW dbo.TalkData;
DROP FUNCTION dbo.CallData;
DROP TABLE dbo.Talk;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 29, 2010 at 8:28 am
Thank you so much Paul. Your function did excatly what I needed. One thing though, I noticed you are hardcoding the following:
This.LoadId - 1
The issue I have with this is what if the load fails before getting to this process? So for instance LoadId 1 processed successfully but LoadId 2 failed. LoadId 3 was successful. I would then have LoadId 1 and 3 in the table. How do I pickup the previous LoadId without hardcoding the LoadId -1?
Thanks,
Ninel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply