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:

    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

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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

  • 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

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

  • 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