August 2, 2009 at 8:16 pm
I need to distribute a schedule of calls in a CRM system. We need to take a table containing 2 columns (salesrep ID and customer ID) where each salesrep ID will have about 100 rows.
I need to assign each rep's rows into groups of 5 for day 1, day 2, day 3 etc until all 100 rows are assigned to a day and then schedule them from a starting date but skip weekends.
I did it "by hand" by using temp table with a datetime column containing just weekdays starting with the date they want the calls to begin and a sequence column starting from 1.
In a second table I added a sequence column that numbered each salesrep ID's rows from 1 to their max number of rows. In a forth column (call_day) I added 5 to each sequence number and then divided that number by 5 to get calling day 1, 2 etc.
This worked and now I'm asking for advice on the best way to build this logic into a stored procedure that can be fed the start date as a parameter and the rows containing salesrep ID and customer ID. I do more data migration, scrubbing etc than stored procedure development. I see a lot of people using cursors for this type of process but suspect that it could be done without them.
Thanks
August 2, 2009 at 8:47 pm
Your are correct... cursors are definitely not needed here. Sounds like you did a pretty good job avoiding them and your method seems good. With that in mind, I'm not really sure what your question is because it seems like you're done. If this were 2005 instead of 2000, I'd make a couple of suggestions in the area of CTE's and the use of ROW_NUM.
If you want to see what I might use as an alternative (and you didn't leave much room for improvement), I just need a bit of clarification. How many calls does a rep get assigned per day? From your description, it could be 1 or 5 or ???.
I need to assign each rep's rows into groups of 5 for day 1, day 2, day 3 etc until all 100 rows are assigned to a day and then schedule them from a starting date but skip weekends.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2009 at 7:27 am
Hi Jeff,
It's 5 calls per day. Of course they will probably change that at some point but whatever logic is used for 5 can probably used for more or less.
I'm interested in alternatives, that is pretty much the question I'm asking - "how would you, in general, approach this?"
In fact the "feed" rows are coming in the form of spreadsheets that are the result of data mining in Hummingbird. I'm using OPENROWSET now but perhaps I should use DTS. If an admin is going to submit the data I'm going to need to check the validity of the rows. What do people use for that - VB?
Doug
August 3, 2009 at 8:58 pm
One question - you want to validate the input, so is there a master data source to check it against, or you just looking to validate that a numeric field is indeed numeric? A DTS package is defnitely called for here to get the data in, but as all my data migration stuff was ad-hoc, I never got the chance to see what a DTS package on SQL Server 2000 could do. I know you could do any validation necessary with SSIS on SQL Server 2005, but there's definitely things you can do without any VB: e.g., if all the DTS package does is bring in raw data, you can then validate the resulting table against any master data source in a SQL Agent Job that places problem rows in an error table and removes them from the table where they were placed by the DTS package.
The code to do the work should probably be in a parameterized stored procedure, and you can make the number of calls per day one of the parameters. If you can supply some sample data and table structures (see Jeff Moden's signature for details on how to best do that), we can help you get the code written more easily.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 4, 2009 at 2:36 pm
smunson (8/3/2009)
If you can supply some sample data and table structures (see Jeff Moden's signature for details on how to best do that), we can help you get the code written more easily.
dsteinschneider,
What Steve said would make it really easy for us to help. What say thee?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 4, 2009 at 9:05 pm
Long day here wrestling with a data migration. I printed out Jeff's posting suggestions - I'm putting together a post with the tables and what I have so far - will post tomorrow. Really looking forward to your code recommendations.
August 5, 2009 at 9:39 am
dsteinschneider ,
Keep it simple... just a simple CREATE TABLE script and a couple dozen rows of readily consumable data so folks can work on the problem of distribution of 5 per day per rep would really do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2009 at 8:25 pm
dsteinschneider (8/4/2009)
Long day here wrestling with a data migration. I printed out Jeff's posting suggestions - I'm putting together a post with the tables and what I have so far - will post tomorrow. Really looking forward to your code recommendations.
Heh... that's one heck of a long day. I just want to make sure you got an answer or that it's no longer an issue. Let us know if it's still a problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply