Avoiding Cursors
Help me with this problem, please. There are three parts.
I have a table with milestones and dates:
FRID ResourceKey Milestone
1005 1 9/15/2008
1005 4 11/13/2008
1005 5 8/18/2008
1005 88 11/30/2008
1008 1 9/17/2008
1009 1 9/16/2008
1009 2 11/13/2008
1009 4 11/1/2008
1009 5 8/18/2008
1009 88 11/30/2008
1010 1 9/17/2008
1010 4 10/22/2008
1010 88 11/10/2008
1014 1 9/15/2008
1014 4 10/14/2008
1014 5 8/27/2008
And a table for key-text.
ResourceKey FormDescription SortOrder
1 CC Contacted 8
2 CC Meeting Held 9
3 CEO Meeting Held 10
4 Campaign Start 11
5 AI Completed 2
6 Match Agreement Signed 3
10 Recommendations Complete 14
12 Thank You Sent 16
13 Received Management Support 1
What I want to do is assemble a single text note for each company like this:
FRID Note
1005 08/21/2007--AI Completed;09/27/2007--CC Contacted;10/18/2007--CC Meeting Held;...
1014 09/06/2007--AI Completed;10/08/2007--CC Contacted;11/01/2007--CC Meeting Held;...
...
First part: How can I get those notes, the way they're supposed to be formatted, in one statement, without using a cursor and a temp table?
(That's how I did it for 2007... I'd like to do it The Right Way for 2008)
The notes table has a three-part primary key and a "note" text field.
Frid, notedate, notesequence
When I insert the data, each company will have a different sequence for the note (depending on how many other notes have been inserted today. I need to have it be one more than the current max for each one.
Second part: How can I insert the rows into the notes table all at once, without looping through?
A problem is that companies might have records merged during the year, for some reason. So, while in the milestones table, there's 3 separate FRIDs, there's only one frid in the notes table. Of course there's a table listing the merged frid and the new frid.
Third part: How can I insert notes all at once, when the data might have more than one row for a particular FRID?
I have a table with milestones and dates:
FRID ResourceKey Milestone
1005 1 9/15/2008
1005 4 11/13/2008
1005 5 8/18/2008
1005 88 11/30/2008
1008 1 9/17/2008
1009 1 9/16/2008
1009 2 11/13/2008
1009 4 11/1/2008
1009 5 8/18/2008
1009 88 11/30/2008
1010 1 9/17/2008
1010 4 10/22/2008
1010 88 11/10/2008
1014 1 9/15/2008
1014 4 10/14/2008
1014 5 8/27/2008
And a table for key-text.
ResourceKey FormDescription SortOrder
1 CC Contacted 8
2 CC Meeting Held 9
3 CEO Meeting Held 10
4 Campaign Start 11
5 AI Completed 2
6 Match Agreement Signed 3
10 Recommendations Complete 14
12 Thank You Sent 16
13 Received Management Support 1
What I want to do is assemble a single text note for each company like this:
FRID Note
1005 08/21/2007--AI Completed;09/27/2007--CC Contacted;10/18/2007--CC Meeting Held;...
1014 09/06/2007--AI Completed;10/08/2007--CC Contacted;11/01/2007--CC Meeting Held;...
...
First part: How can I get those notes, the way they're supposed to be formatted, in one statement, without using a cursor and a temp table?
(That's how I did it for 2007... I'd like to do it The Right Way for 2008)
The notes table has a three-part primary key and a "note" text field.
Frid, notedate, notesequence
When I insert the data, each company will have a different sequence for the note (depending on how many other notes have been inserted today. I need to have it be one more than the current max for each one.
Second part: How can I insert the rows into the notes table all at once, without looping through?
A problem is that companies might have records merged during the year, for some reason. So, while in the milestones table, there's 3 separate FRIDs, there's only one frid in the notes table. Of course there's a table listing the merged frid and the new frid.
Third part: How can I insert notes all at once, when the data might have more than one row for a particular FRID?
