April 28, 2010 at 8:25 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:08 pm
Duplicate post. Please post responses at at the other post.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply