Do I need a cursor?

[Ninel]

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

[rhettss]

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

[mikr0s]

--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--