-
Do I need a cursor?
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:
Code:
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.
Code:
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
-
a general example
I'm not going to give you the exact code, but here is a general example that you will be able to work through. I may be off on what you exactly need due to a misinterpretation of your question. If anyone else has another solution, I'd love to see it.
table T
id [int]
dt [datetime]
DECLARE @dt_first as datetime
DECLARE @dt_second as datetime
SELECT TOP 2 dt
INTO #T
FROM T
WHERE [whatever]
ORDER BY dt desc
SELECT TOP 1 @dt_first = dt
FROM #T
ORDER BY dt desc
DELETE FROM #T
WHERE dt = @dt_first
SELECT TOP 1 @dt_second = dt
FROM #T
ORDER BY dt desc
-
--based on the volume of inserted data, the outer apply might not be the best choice
SELECT toinsert.loadid, toinsert.dCalldate, toinsert.RepId, toinsert.CurrentTalkTime, ISNULL(previous_call.pTalkTime, 0) AS LastTalkTime, toinsert.CurrentTalkTime - ISNULL(previous_call.pTalkTime, 0) AS IncrementalTalkTime
FROM
(
select 3 AS loadid, '20100428' AS dCalldate, 510016 as RepId, 180 as CurrentTalkTime
union
select 3, '20100428', 510017, 30
union
select 3, '20100428', 510018, 20
) AS toinsert
OUTER APPLY (SELECT TOP 1 currentTalkTime AS pTalkTime FROM dbo.TALK as b WHERE b.dCalldate = toinsert.dCalldate AND b.RepID = toinsert.RepID ORDER BY dDateCreated DESC) AS previous_call
--HTH--
Last edited by mikr0s; 04-29-2010 at 11:03 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|