Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    32

    Unanswered: Stored proc to get single person from a table based on earliest datetime

    Hi,

    I'm having problems with a stored procedure, that i'm hoping someone can help me with.

    I have a table with 2 columns - Username (varchar), LastAllocation (datetime)

    The Username column will always have values, LastAllocation may have NULL values. Example

    Username | LastAllocation
    ------------------------
    Greg | 02 October 2005 15:30
    John | 02 October 2005 18:00
    Mike | <NULL>

    My stored procedure needs to pull back a user name with the following criteria:

    If any <NULL> dates send username of first person where date is null, sorted alphabetically, otherwise send username of person with earliest date from LastAllocation

    Then update the LastAllocation column with GETDate() for that username.

    This SP will be called repeatedly, so all users will eventually have a date, then will be cycled through from earliest date. I wrote an SP to do this, but it seems to be killing my server - the sp works, but I then can't view the values in the table in Enterprise Manager. SP is below - can anyone see what could be causing the problem, or have a better soln?
    Thanks
    Greg
    ------------------------------------------------------------------------------
    ------------------------------------------------------------------------------
    CREATE PROCEDURE STP_GetNextSalesPerson AS
    DECLARE @NextSalesPerson varchar(100)

    BEGIN TRAN

    IF (SELECT COUNT(*) FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL) > 0
    BEGIN
    SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL ORDER BY eUserName ASC
    END
    ELSE
    BEGIN
    SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam ORDER BY LeadLastAllocated ASC
    END

    SELECT @NextSalesPerson
    UPDATE REF_SalesTeam SET LeadLastAllocated = GETDATE() WHERE eUserName = @NextSalesPerson


    COMMIT TRAN
    GO

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Nulls sort to top....
    Code:
    CREATE PROCEDURE STP_GetNextSalesPerson AS
    
    UPDATE REF_SalesTeam
    SET	LeadLastAllocated = GETDATE()
    WHERE	eUserName =
    		(SELECT	TOP 1 eUserName
    		FROM	REF_SalesTeam
    		ORDER BY LeadLastAllocated ASC,
    			eUserName ASC)
    
    GO
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    32
    Thanks, that did the trick..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •