SQL Confessions

Comments: 1 Comment
Published on: December 13, 2010

I had a brainstorm of an idea for a group of articles on my blog a couple of weeks ago and am finally getting around to putting it together.  The idea came about while I was struggling with a problem at work and trying to meet the requirements in the code.  As the title states, this group is going to be about things done in SQL server that I would normally avoid.

The first article came about while trying to find a solution to some business requirements.  The requirements had an average to be calculated and then a revised average if certain criteria were met.  Then it was required to move things from one group to another group depending on a series of criteria.  When I first looked at these requirements, I thought I would need several cursors.  As it turns out, I had to implement just one cursor.  I could find no way of getting away from using a cursor for this one requirement.  The essence of it was that I had two variables.  One variable was the number of items to move and the second variable was the person to whom the items needed to be moved.  Not only did it require those two variables, but there was the additional requirement that it be randomized.

First, let’s take a look at how I did the randomizing.  I took care of this requirement by dumping all of my prep data into a temp table that had a default on a column explicitly to hold  a random value.  Here is the code for that table.

CREATE TABLE LeadRedist_PhaseThree2 (
	LeadRedistID			INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	ActivityOwner			UNIQUEIDENTIFIER,
	OppOwner				UNIQUEIDENTIFIER,
	OpportunityID			UNIQUEIDENTIFIER,
	ownershipbegindate		DATETIME,
	ScheduledDate			DATETIME,
	Disposition				VARCHAR(30),
	Randomizer				FLOAT DEFAULT RAND(CHECKSUM(NEWID())) * 10000,
	RowNum					INT,
	OwnerFullName			VARCHAR(50),
	OppAuditDetailID		UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
	ActAuditDetailID		UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
	OppAuditID				UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
	ActAuditID				UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),
	NewOwner				UNIQUEIDENTIFIER
)

Using this table, I can just run a set-based insert to populate the table and thus end up with random values in the “Randomizer” column due to the default constraint.

DEFAULT RAND(CHECKSUM(NEWID())) * 10000

That piece of the puzzle was fixed rather easily.  Now for the next part – the confession part.  I had to write a cursor.  Next part of that confession – I had to look up how to write the cursor.  Without going into a lot of details, this is the code of how I used a cursor to solve this particular problem.

DECLARE @NewUserID	UNIQUEIDENTIFIER
		,@OppstoRec	INT
 
DECLARE PhaseThree_Cursor CURSOR
	FOR
		SELECT LV.SystemUserID, LV.OppstoReceive
			FROM LeadRedist_Avgs2 LV
			WHERE LV.OppstoReceive > 0
			ORDER BY LV.OppstoReceive DESC
 
OPEN PhaseThree_Cursor;
 
	FETCH NEXT FROM PhaseThree_Cursor
		INTO @NewUserID, @OppstoRec;
 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		WITH reassign AS (
			SELECT TOP (@OppstoRec) LP.LeadRedistID
				FROM LeadRedist_PhaseThree2 LP
				WHERE LP.NewOwner IS null
				ORDER BY LP.Randomizer DESC
		)
 
		UPDATE LP
			SET LP.NewOwner = @NewUserID
			FROM LeadRedist_PhaseThree2 LP
				INNER Join reassign r
					ON LP.LeadRedistID = r.LeadRedistID
 
	FETCH NEXT FROM PhaseThree_Cursor
		INTO @NewUserID, @OppstoRec;
	END
 
CLOSE PhaseThree_Cursor
DEALLOCATE PhaseThree_Cursor

Note that this cursor is a little hybrid.  I am using this cursor to perform some set based actions.  I am fine with that.  I am happy with the random distribution and the fact that this performs well.  This was done for a one off requirement that will not be used again (or at least not planned to be used again) and it met all of the requirements.  I was blocked on this particular requirement by the required variables and couldn’t get past how to do this in a 100% set-based fashion.  If you have any input, let me know.

Do you have any such stories to share?

1 Comment - Leave a comment
  1. [...] that I either did wrong or couldn’t find a better way of doing it at the time.  In that first episode, I admitted to the use of a cursor (drat).  I should go back and update that posting because I got [...]

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>






Calendar
December 2010
M T W T F S S
« Nov   Jan »
 12345
6789101112
13141516171819
20212223242526
2728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Thursday, April 24, 2014