Row distribution to rows from another table

Question

Row distribution to rows from another table

Raul Undreiner

SSCommitted

Points: 1554
More actions
July 30, 2009 at 10:28 am

#156043

I need to distribute case numbers from a table between the AEs that show up each day.
Case Table
Case number, Name
1, Smith
4, Jones
5, Holmes
9, Bowes
12, Charles
13, Polinsky
Acct.Exec. Table
Name,IsIn
AE1,0
AE2,1
AE3,1
AE4,0
AE5,1
The result should be
1,Smith,AE2
4, Jones,AE2
5, Holmes,AE3
9, Bowes ,AE3
12, Charles,AE5
13, Polinsky,AE5
Thanks in advance,
Raul

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

You're missing some stuff here. First, you haven't actually asked a question, that makes it hard for us to answer it.

Secondly, you haven't told us what "assignment" means in terms of your database: Are you just looking for a SELECT statement that will produce that result? Or, do you have some table where that data is supposed to go? Or do you need us to tell what kind of table(s) you need to represent these assignments?

See, those are all different questions.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Thirdly, you haven't told us what the criteria for assignment is. Yes, I see the little flag that indicates that an AE is eligible for assignment, but what determines which AE's get which assignments?

Finally, you haven't told us the context of this: Is this an after-hours report? Is this a periodic Job that assigns all of the unassigned cases at once? Or do you need new cases to be assigned immediately, as they are created? Different contexts means different solutions.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Raul Undreiner SSCommitted Points: 1554 More actions · Answer 3

I'm sorry for being vague in my post.

I need the query to make this distribution only once per day. This means that all available (not previously assigned) cases will be distributed first thing in the morning between all the present AEs.

Cases should be evenly distributed between the AEs trying to keep the files in an incremental order so it will be easier to distribute since they will be in the same box.

Obviously this example is just a minuscule part of the real data. Real data should be around 600 cases between the present from 8 AEs

Thanks

drew.allen SSC Guru Points: 77014 More actions · Answer 4

This approach should work, but it is UNTESTED. It divides the clients evenly, but, and this is a big but, it does not retain the order.

1) Create a CTE on the AEs present that day that includes the row number.

2) Create a CTE on the Clients that includes (the row number modulus the count of present AEs) plus one.

3) Join the two CTEs on the columns based on the row number expressions.

There may be a way to specify the order in the row number calculation for the clients so that it does keep the clients in the correct order, but I don't see it immediately.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

Raul Undreiner (7/30/2009)
I'm sorry for being vague in my post.
I need the query to make this distribution only once per day. This means that all available (not previously assigned) cases will be distributed first thing in the morning between all the present AEs.
Cases should be evenly distributed between the AEs trying to keep the files in an incremental order so it will be easier to distribute since they will be in the same box.
Obviously this example is just a minuscule part of the real data. Real data should be around 600 cases between the present from 8 AEs
Thanks

They will all end up trying to hunt you down and kill you because you have no "throttle" on this. What happens if there's a meeting and only two of the folks are "dismissed" from the meeting or some such other occurance? You'll end up distributing around 600 cases between the present 2 AE's.

There has to be a "max" assigned number even if only one person shows up AND it has to be based on the skill set of the person(s) that do show up...

... or, learn how to dodge some really high velocity pork chops as you stroll down the hall. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Raul Undreiner SSCommitted Points: 1554 More actions · Answer 6

Alright, alright. Lets just forget about the numbers. I just need a query that will distribute the rows from one table evenly between the rows from another table trying to keep the order they where inserted in the first table.

As simple as that.

Thanks for the replies but I would like to see the actual query that produces the desired result if it isn't too much too ask.