Do I need a cursor? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Do I need a cursor?

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2010-04-28 : 22:23:02

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-04-29 : 03:51:57

cursors are rarely needed.

could you show us sample "Expected results"?
how many records will the table have, and how active it will be (inserts per hour, selects per hour, etc)....(in case it matters to the type of solution proposed)

ninel
Posting Yak Master

141 Posts

Posted - 2010-04-29 : 10:35:05

The load will occur every 15 minutes and will contain data for every telemarketing rep on the floor.

The code provided in the previous post includes a sample of "expected results"

Thanks,
Ninel

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-04-29 : 10:39:24

Hhhmmm, I see code for the inserts but nothing to produce output results?

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

ninel
Posting Yak Master

141 Posts

Posted - 2010-04-29 : 10:50:40

Sorry, I forgot to include the Select statement.
[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.

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

SELECT * FROM TALK
[/Code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources