Looping In SQL 2000 (Can it be a nested loop)

I have to automate a process that assigns sales leads to sales people.

For example:
Every day we buy a list of sales leads, it ranges in size from 50 -
100 records.
We have a team of sales people that also can range from 5 - 8 people.

I need to take the new records and divide them evenly among the sales
people.

If i get 50 records, and have 5 sales people, then each sales person
gets 10 leads.
--
So, im guessing that I may need to have a nested loop inside this. I
have tried it several different ways, but cant seem to get it quite
right.

DECLARE @.TotalRecordCount int, @.TotalSalesPeopleCount int,
@.AmountForEach int, @.LooperSalesPeoplerecords int,
@.LooperNewSalesLeadsRecords int, @.SalesPersonID int

SELECT @.TotalSalesPeopleCount = COUNT(UserId)
FROM SalesPeople
WHERE Active = 1
--
SELECT @.TotalRecordCount = COUNT(*)
FROM NewSalesLeads
--
SELECT @.AmountForEach = (@.TotalRecordCount/@.TotalSalesPeopleCount)
--
SELECT @.LooperSalesPeoplerecords = 1
SELECT @.LooperNewSalesLeadsRecords = 1
--
WHILE @.LooperSalesPeoplerecords <= @.TotalSalesPeopleCount
BEGIN
WHILE @.LooperNewSalesLeadsRecords <= @.TotalRecordCount
BEGIN
SELECT @.SalesPersonID = (SELECT UserID
FROM SalesPeople
WHERE UniqueId = @.LooperSalesPeoplerecords)

SELECT @.LooperSalesPeoplerecords =
(@.LooperSalesPeoplerecords + 1)

UPDATE SalesLeads
SET SalesPerson_ID = @.SalesPersonID
WHERE UNIQUEID = @.LooperSalesPeoplerecords

SELECT @.LooperSalesPeoplerecords =
(@.LooperSalesPeoplerecords + 1)
END
END

--
Table structures

CREATE TABLE [dbo].[SalesPeople] (
[SalesPerson_ID] [int] NOT NULL ,
[FirstName] [varchar](20)NOT NULL
) ON [PRIMARY]
--
INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (26, 'Bill')
INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (28, 'Bob')
INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (37,
'Chris')
---------------
CREATE TABLE [dbo].[SalesLeads] (
[SalesLeadID] [int]NOT NULL ,
[SalesPerson_ID] [int]NOT NULL
) ON [PRIMARY]
--
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1001,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1002,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1003,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1004,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1005,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1006,0)
---------------

So in this case, all 3 salespeople should receive 2 salesleads each.

I dummied this down quite a bit. It actually ends up being more like
15 sales people, and about 400,000 sales leads. But it should work on
any level.

Thanks for any help you might shed on this.Hi

Something like this may help. It assumes the sales IDs are sequential, and
does not worry if there are a an exact division.

UPDATE L
SET SalesPerson_ID = M.SalesPerson_ID
FROM SalesLeads L JOIN
( SELECT P.[FirstName], P.[SalesPerson_ID] ,
( SELECT COUNT(*) FROM [dbo].[SalesPeople] S WHERE S.[SalesPerson_ID] <
P.[SalesPerson_ID] ) AS Rank
FROM [dbo].[SalesPeople] P ) M
ON L.SalesLeadID % ( SELECT COUNT(*) FROM [dbo].[SalesPeople] ) = M.Rank

John

"Dave" <funkdm1@.yahoo.com> wrote in message
news:f5174e0f.0406180540.2afaed20@.posting.google.c om...
> I have to automate a process that assigns sales leads to sales people.
> For example:
> Every day we buy a list of sales leads, it ranges in size from 50 -
> 100 records.
> We have a team of sales people that also can range from 5 - 8 people.
> I need to take the new records and divide them evenly among the sales
> people.
> If i get 50 records, and have 5 sales people, then each sales person
> gets 10 leads.
> --
> So, im guessing that I may need to have a nested loop inside this. I
> have tried it several different ways, but cant seem to get it quite
> right.
> DECLARE @.TotalRecordCount int, @.TotalSalesPeopleCount int,
> @.AmountForEach int, @.LooperSalesPeoplerecords int,
> @.LooperNewSalesLeadsRecords int, @.SalesPersonID int
> SELECT @.TotalSalesPeopleCount = COUNT(UserId)
> FROM SalesPeople
> WHERE Active = 1
> --
> SELECT @.TotalRecordCount = COUNT(*)
> FROM NewSalesLeads
> --
> SELECT @.AmountForEach = (@.TotalRecordCount/@.TotalSalesPeopleCount)
> --
> SELECT @.LooperSalesPeoplerecords = 1
> SELECT @.LooperNewSalesLeadsRecords = 1
> --
> WHILE @.LooperSalesPeoplerecords <= @.TotalSalesPeopleCount
> BEGIN
> WHILE @.LooperNewSalesLeadsRecords <= @.TotalRecordCount
> BEGIN
> SELECT @.SalesPersonID = (SELECT UserID
> FROM SalesPeople
> WHERE UniqueId = @.LooperSalesPeoplerecords)
> SELECT @.LooperSalesPeoplerecords =
> (@.LooperSalesPeoplerecords + 1)
> UPDATE SalesLeads
> SET SalesPerson_ID = @.SalesPersonID
> WHERE UNIQUEID = @.LooperSalesPeoplerecords
> SELECT @.LooperSalesPeoplerecords =
> (@.LooperSalesPeoplerecords + 1)
> END
> END
> --
> Table structures
> CREATE TABLE [dbo].[SalesPeople] (
> [SalesPerson_ID] [int] NOT NULL ,
> [FirstName] [varchar](20)NOT NULL
> ) ON [PRIMARY]
> --
> INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (26, 'Bill')
> INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (28, 'Bob')
> INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (37,
> 'Chris')
> ---------------
> CREATE TABLE [dbo].[SalesLeads] (
> [SalesLeadID] [int]NOT NULL ,
> [SalesPerson_ID] [int]NOT NULL
> ) ON [PRIMARY]
> --
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1001,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1002,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1003,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1004,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1005,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1006,0)
> ---------------
> So in this case, all 3 salespeople should receive 2 salesleads each.
> I dummied this down quite a bit. It actually ends up being more like
> 15 sales people, and about 400,000 sales leads. But it should work on
> any level.
> Thanks for any help you might shed on this.|||John,

Yeah, we arent that concerned about having an exactly even
distribution so uneven divisors are not a problem.

And, unfortunately the sales people arent in sequential order. WE have
hundreds of sales people that are assigned to various campaigns at any
given time. So this particular campaign may change on a daily basis.
Meaning that the salespersonid is always non sequential for this
campaign.

Thanks anyway.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<_mDAc.3861$Is4.34270449@.news-text.cableinet.net>...
> Hi
> Something like this may help. It assumes the sales IDs are sequential, and
> does not worry if there are a an exact division.
> UPDATE L
> SET SalesPerson_ID = M.SalesPerson_ID
> FROM SalesLeads L JOIN
> ( SELECT P.[FirstName], P.[SalesPerson_ID] ,
> ( SELECT COUNT(*) FROM [dbo].[SalesPeople] S WHERE S.[SalesPerson_ID] <
> P.[SalesPerson_ID] ) AS Rank
> FROM [dbo].[SalesPeople] P ) M
> ON L.SalesLeadID % ( SELECT COUNT(*) FROM [dbo].[SalesPeople] ) = M.Rank
> John
> "Dave" <funkdm1@.yahoo.com> wrote in message
> news:f5174e0f.0406180540.2afaed20@.posting.google.c om...
> > I have to automate a process that assigns sales leads to sales people.
> > For example:
> > Every day we buy a list of sales leads, it ranges in size from 50 -
> > 100 records.
> > We have a team of sales people that also can range from 5 - 8 people.
> > I need to take the new records and divide them evenly among the sales
> > people.
> > If i get 50 records, and have 5 sales people, then each sales person
> > gets 10 leads.
> > --
> > So, im guessing that I may need to have a nested loop inside this. I
> > have tried it several different ways, but cant seem to get it quite
> > right.
> > DECLARE @.TotalRecordCount int, @.TotalSalesPeopleCount int,
> > @.AmountForEach int, @.LooperSalesPeoplerecords int,
> > @.LooperNewSalesLeadsRecords int, @.SalesPersonID int
> > SELECT @.TotalSalesPeopleCount = COUNT(UserId)
> > FROM SalesPeople
> > WHERE Active = 1
> > --
> > SELECT @.TotalRecordCount = COUNT(*)
> > FROM NewSalesLeads
> > --
> > SELECT @.AmountForEach = (@.TotalRecordCount/@.TotalSalesPeopleCount)
> > --
> > SELECT @.LooperSalesPeoplerecords = 1
> > SELECT @.LooperNewSalesLeadsRecords = 1
> > --
> > WHILE @.LooperSalesPeoplerecords <= @.TotalSalesPeopleCount
> > BEGIN
> > WHILE @.LooperNewSalesLeadsRecords <= @.TotalRecordCount
> > BEGIN
> > SELECT @.SalesPersonID = (SELECT UserID
> > FROM SalesPeople
> > WHERE UniqueId = @.LooperSalesPeoplerecords)
> > SELECT @.LooperSalesPeoplerecords =
> > (@.LooperSalesPeoplerecords + 1)
> > UPDATE SalesLeads
> > SET SalesPerson_ID = @.SalesPersonID
> > WHERE UNIQUEID = @.LooperSalesPeoplerecords
> > SELECT @.LooperSalesPeoplerecords =
> > (@.LooperSalesPeoplerecords + 1)
> > END
> > END
> > --
> > Table structures
> > CREATE TABLE [dbo].[SalesPeople] (
> > [SalesPerson_ID] [int] NOT NULL ,
> > [FirstName] [varchar](20)NOT NULL
> > ) ON [PRIMARY]
> > --
> > INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (26, 'Bill')
> > INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (28, 'Bob')
> > INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (37,
> > 'Chris')
> > ---------------
> > CREATE TABLE [dbo].[SalesLeads] (
> > [SalesLeadID] [int]NOT NULL ,
> > [SalesPerson_ID] [int]NOT NULL
> > ) ON [PRIMARY]
> > --
> > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1001,0)
> > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1002,0)
> > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1003,0)
> > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1004,0)
> > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1005,0)
> > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1006,0)
> > ---------------
> > So in this case, all 3 salespeople should receive 2 salesleads each.
> > I dummied this down quite a bit. It actually ends up being more like
> > 15 sales people, and about 400,000 sales leads. But it should work on
> > any level.
> > Thanks for any help you might shed on this.|||Hi

The solution does not rely on salesperson id being sequential as it ranks
them separately. If the salesleads id are reasonably sequential then you
should be ok.

John

"Dave" <funkdm1@.yahoo.com> wrote in message
news:f5174e0f.0406181026.4c69acff@.posting.google.c om...
> John,
> Yeah, we arent that concerned about having an exactly even
> distribution so uneven divisors are not a problem.
> And, unfortunately the sales people arent in sequential order. WE have
> hundreds of sales people that are assigned to various campaigns at any
> given time. So this particular campaign may change on a daily basis.
> Meaning that the salespersonid is always non sequential for this
> campaign.
> Thanks anyway.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<_mDAc.3861$Is4.34270449@.news-text.cableinet.net>...
> > Hi
> > Something like this may help. It assumes the sales IDs are sequential,
and
> > does not worry if there are a an exact division.
> > UPDATE L
> > SET SalesPerson_ID = M.SalesPerson_ID
> > FROM SalesLeads L JOIN
> > ( SELECT P.[FirstName], P.[SalesPerson_ID] ,
> > ( SELECT COUNT(*) FROM [dbo].[SalesPeople] S WHERE S.[SalesPerson_ID] <
> > P.[SalesPerson_ID] ) AS Rank
> > FROM [dbo].[SalesPeople] P ) M
> > ON L.SalesLeadID % ( SELECT COUNT(*) FROM [dbo].[SalesPeople] ) = M.Rank
> > John
> > "Dave" <funkdm1@.yahoo.com> wrote in message
> > news:f5174e0f.0406180540.2afaed20@.posting.google.c om...
> > > I have to automate a process that assigns sales leads to sales people.
> > > > For example:
> > > Every day we buy a list of sales leads, it ranges in size from 50 -
> > > 100 records.
> > > We have a team of sales people that also can range from 5 - 8 people.
> > > > I need to take the new records and divide them evenly among the sales
> > > people.
> > > > If i get 50 records, and have 5 sales people, then each sales person
> > > gets 10 leads.
> > > --
> > > So, im guessing that I may need to have a nested loop inside this. I
> > > have tried it several different ways, but cant seem to get it quite
> > > right.
> > > > DECLARE @.TotalRecordCount int, @.TotalSalesPeopleCount int,
> > > @.AmountForEach int, @.LooperSalesPeoplerecords int,
> > > @.LooperNewSalesLeadsRecords int, @.SalesPersonID int
> > > > SELECT @.TotalSalesPeopleCount = COUNT(UserId)
> > > FROM SalesPeople
> > > WHERE Active = 1
> > > --
> > > SELECT @.TotalRecordCount = COUNT(*)
> > > FROM NewSalesLeads
> > > --
> > > SELECT @.AmountForEach = (@.TotalRecordCount/@.TotalSalesPeopleCount)
> > > --
> > > SELECT @.LooperSalesPeoplerecords = 1
> > > SELECT @.LooperNewSalesLeadsRecords = 1
> > > --
> > > WHILE @.LooperSalesPeoplerecords <= @.TotalSalesPeopleCount
> > > BEGIN
> > > WHILE @.LooperNewSalesLeadsRecords <= @.TotalRecordCount
> > > BEGIN
> > > SELECT @.SalesPersonID = (SELECT UserID
> > > FROM SalesPeople
> > > WHERE UniqueId = @.LooperSalesPeoplerecords)
> > > > SELECT @.LooperSalesPeoplerecords =
> > > (@.LooperSalesPeoplerecords + 1)
> > > > UPDATE SalesLeads
> > > SET SalesPerson_ID = @.SalesPersonID
> > > WHERE UNIQUEID = @.LooperSalesPeoplerecords
> > > > SELECT @.LooperSalesPeoplerecords =
> > > (@.LooperSalesPeoplerecords + 1)
> > > END
> > > END
> > > > --
> > > Table structures
> > > > CREATE TABLE [dbo].[SalesPeople] (
> > > [SalesPerson_ID] [int] NOT NULL ,
> > > [FirstName] [varchar](20)NOT NULL
> > > ) ON [PRIMARY]
> > > --
> > > INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (26, 'Bill')
> > > INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (28, 'Bob')
> > > INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (37,
> > > 'Chris')
> > > ---------------
> > > CREATE TABLE [dbo].[SalesLeads] (
> > > [SalesLeadID] [int]NOT NULL ,
> > > [SalesPerson_ID] [int]NOT NULL
> > > ) ON [PRIMARY]
> > > --
> > > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1001,0)
> > > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1002,0)
> > > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1003,0)
> > > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1004,0)
> > > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1005,0)
> > > INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1006,0)
> > > ---------------
> > > > So in this case, all 3 salespeople should receive 2 salesleads each.
> > > > I dummied this down quite a bit. It actually ends up being more like
> > > 15 sales people, and about 400,000 sales leads. But it should work on
> > > any level.
> > > > Thanks for any help you might shed on this.|||I think meant to have something like this, with the keys shown and
some extra columns addded:

CREATE TABLE SalesPeople
(salesperson_id INTEGER NOT NULL PRIMARY KEY,
firstname VARCHAR(20)NOT NULL,
seq_nbr INTEGER NOT NULL,
.. );

Run this guy when then table is changed.

UPDATE SalesPeople
SET seq_nbr
= (SELECT COUNT(*)
FROM SalesPeople AS P1
WHERE P1.saleperson_id <= SalesPeople.salesperson_id)

Now a table for the leads as leads and not what you had:

CREATE TABLE SalesLeads
(saleslead_id INTEGER NOT NULL PRIMARY KEY,
grp_nbr INTEGER,
..);

First group the leads based on the number of sales people:

UPDATE SalesLeads
SET grp_nbr
= (SELECT COUNT(*)
FROM SalesLeads AS L1
WHERE L1.saleslead_id <= SalesLeads.saleslead_id)
% (SELECT COUNT(*) FROM SalesPeople);

Now look at a VIEW to get each sales person the group of leads they
are to work.

CREATE VIEW LeadAssignments (saleslead_id, salesperson_id)
AS SELECT L1.saleslead_id, P1.salesperson_id
FROM Salesleads AS L1, SalesPeople AS P1
WHERE L1.grp_nbr = P1.seq_nbr;|||I think I understand what you are saying here. However, This thing has
to be comletely automated and just do the update everynight.

I do appreciate the suggestions.

I have to think that this is used fairly often, and cant be this
difficult to find some folks that have written something similar.

Thanks,
Dave

jcelko212@.earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0406181437.187bf80a@.posting.google.com>...
> I think meant to have something like this, with the keys shown and
> some extra columns addded:
> CREATE TABLE SalesPeople
> (salesperson_id INTEGER NOT NULL PRIMARY KEY,
> firstname VARCHAR(20)NOT NULL,
> seq_nbr INTEGER NOT NULL,
> .. );
> Run this guy when then table is changed.
> UPDATE SalesPeople
> SET seq_nbr
> = (SELECT COUNT(*)
> FROM SalesPeople AS P1
> WHERE P1.saleperson_id <= SalesPeople.salesperson_id)
> Now a table for the leads as leads and not what you had:
> CREATE TABLE SalesLeads
> (saleslead_id INTEGER NOT NULL PRIMARY KEY,
> grp_nbr INTEGER,
> ..);
> First group the leads based on the number of sales people:
> UPDATE SalesLeads
> SET grp_nbr
> = (SELECT COUNT(*)
> FROM SalesLeads AS L1
> WHERE L1.saleslead_id <= SalesLeads.saleslead_id)
> % (SELECT COUNT(*) FROM SalesPeople);
> Now look at a VIEW to get each sales person the group of leads they
> are to work.
> CREATE VIEW LeadAssignments (saleslead_id, salesperson_id)
> AS SELECT L1.saleslead_id, P1.salesperson_id
> FROM Salesleads AS L1, SalesPeople AS P1
> WHERE L1.grp_nbr = P1.seq_nbr;|||How about something like this:

----------------------
Create Table #Leads
(
LeadID Int Identity Not Null,
SalesID Int,
UniqueId Int -- Bad name
)

Create Table #SalesPeople
(
SalesID Int Identity (0, 1) Not Null,
UserID Char(30) -- type?
)

DECLARE @.SalesPeopleCount int

-- Put sales people in the temp table.
Insert Into #SalesPeople (UserID)
Select UserID From SalesPeople Where Active = 1

-- How many were there?
Set @.SalesPeopleCount = @.@.RowCount

-- Put leads in the temp table.
Insert Into #Leads (UniqueId)
Select UniqueId From NewSalesLeads

-- Match sales people to leads
Update #Leads Set SalesID = LeadID % @.SalesPeopleCount

-- Save the matches.
UPDATE SalesLeads
SET SalesPerson_ID = UserID
From
SalesLeads
Join #Leads On SalesLeads.UniqueId = #Leads.UniqueId
Join #SalesPeople On #Leads.SalesID = #SalesPeople.SalesID

----------------------

Temporary IDs will always be sequential. The only problem with
this is that lower sales IDs will consistently get more leads.
You could fix that by adding something variable to LeadID before
performing the mod.

If you're going to use ID columns, you need to treat them as keys.
Joe Celko was using something like this as a bad example a few
weeks ago, trying to convince me that ID columns are a bad idea.
Used halfway like this, I would agree.

Bill

Dave wrote:
> I have to automate a process that assigns sales leads to sales people.
> For example:
> Every day we buy a list of sales leads, it ranges in size from 50 -
> 100 records.
> We have a team of sales people that also can range from 5 - 8 people.
> I need to take the new records and divide them evenly among the sales
> people.
> If i get 50 records, and have 5 sales people, then each sales person
> gets 10 leads.
> --
> So, im guessing that I may need to have a nested loop inside this. I
> have tried it several different ways, but cant seem to get it quite
> right.
> DECLARE @.TotalRecordCount int, @.TotalSalesPeopleCount int,
> @.AmountForEach int, @.LooperSalesPeoplerecords int,
> @.LooperNewSalesLeadsRecords int, @.SalesPersonID int
> SELECT @.TotalSalesPeopleCount = COUNT(UserId)
> FROM SalesPeople
> WHERE Active = 1
> --
> SELECT @.TotalRecordCount = COUNT(*)
> FROM NewSalesLeads
> --
> SELECT @.AmountForEach = (@.TotalRecordCount/@.TotalSalesPeopleCount)
> --
> SELECT @.LooperSalesPeoplerecords = 1
> SELECT @.LooperNewSalesLeadsRecords = 1
> --
> WHILE @.LooperSalesPeoplerecords <= @.TotalSalesPeopleCount
> BEGIN
> WHILE @.LooperNewSalesLeadsRecords <= @.TotalRecordCount
> BEGIN
> SELECT @.SalesPersonID = (SELECT UserID
> FROM SalesPeople
> WHERE UniqueId = @.LooperSalesPeoplerecords)
> SELECT @.LooperSalesPeoplerecords =
> (@.LooperSalesPeoplerecords + 1)
> UPDATE SalesLeads
> SET SalesPerson_ID = @.SalesPersonID
> WHERE UNIQUEID = @.LooperSalesPeoplerecords
> SELECT @.LooperSalesPeoplerecords =
> (@.LooperSalesPeoplerecords + 1)
> END
> END
> --
> Table structures
> CREATE TABLE [dbo].[SalesPeople] (
> [SalesPerson_ID] [int] NOT NULL ,
> [FirstName] [varchar](20)NOT NULL
> ) ON [PRIMARY]
> --
> INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (26, 'Bill')
> INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (28, 'Bob')
> INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (37,
> 'Chris')
> ---------------
> CREATE TABLE [dbo].[SalesLeads] (
> [SalesLeadID] [int]NOT NULL ,
> [SalesPerson_ID] [int]NOT NULL
> ) ON [PRIMARY]
> --
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1001,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1002,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1003,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1004,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1005,0)
> INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1006,0)
> ---------------
> So in this case, all 3 salespeople should receive 2 salesleads each.
> I dummied this down quite a bit. It actually ends up being more like
> 15 sales people, and about 400,000 sales leads. But it should work on
> any level.
> Thanks for any help you might shed on this.

looking for nested XML document as output on a recursive query from sql table

Is it possible to finally have a nested XML document on a recursive query? Let's say that we have a classic parent-child relationship inside a table that is self-refernced. An example of this situation can be found in the Employees table of Northwind DB or in the shining new AdventureWorks DB, HumanResources.Employee table.
In both cases we have somthing like that:

EmployeeID ManagerID Title Firstname LastName

Where ManagerID refers to an existing EmployeeID. I'd like to create and organizational tree.

Using a query like that:

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level, Path)
AS
(
SELECT
ManagerID, EmployeeID, Title, DepartmentID, 0 AS Level, CAST(EmployeeID AS VARCHAR(MAX)) AS Path
FROM
HumanResources.Employee
WHERE
ManagerID IS NULL

UNION ALL

SELECT
e.ManagerID, e.EmployeeID, e.Title, e.DepartmentID, Level + 1, Path + '/'+ CAST(e.EmployeeID AS VARCHAR(MAX))
FROM
HumanResources.Employee AS e
JOIN
DirectReports AS d
ON
e.ManagerID = d.EmployeeID
)
SELECT *
FROM DirectReports
ORDER BY Path

The query is a sample one present in the BOL and modified a little. I also remeber the I've see something similar to a document present at the MSDN site.

Now i'd like to have this result in XML, with the correct nesting:

<Emp ManagerID="109" EmployeeID="12" ... >
<Emp ManagerID="12" EmployeeID="3" ... >
<Emp ManagerID="3" EmployeeID="4" ... />
<Emp ManagerID="3" EmployeeID="9" ... />
<Emp ManagerID="3" EmployeeID="11" ... />
<Emp ManagerID="3" EmployeeID="158" ... >
<Emp ManagerID="158" EmployeeID="79" ... />
<Emp ManagerID="158" EmployeeID="114" ... />
<Emp ManagerID="158" EmployeeID="217" ... />
</Emp>
<Emp ManagerID="3" EmployeeID="263" ... >
<Emp ManagerID="263" EmployeeID="5" ... />
<Emp ManagerID="263" EmployeeID="265" ... />
</Emp>
<Emp ManagerID="3" EmployeeID="267" ... />
<Emp ManagerID="3" EmployeeID="270" ... />
</Emp>
</Emp>

but without the need to use SQLXML (as I did to obtain the result above).
I've made some attempts, but seems impossible! :-(

Using FOR XML clause will produce a similar xml document but with no nesting, will I was expecting it since a simple FOR XML on a simple JOIN does create them.

It works using UDF but UDF has limit like maximum of 32 recursively nested function invocations. So it will not work for unlimited depth.

Feedback appreciated :-)

vshah:

I am really just starting to learn XML and so I am not sure that I understand your question correctly. Here is some code that seems to produce the output you have requested. Would you please give this a look and tell me if this is headed in the right direction?

Code Snippet

declare @.employee table
( EmployeeId integer,
ManagerId integer,
DepartmentId integer,
Title varchar(18)
)
insert into @.employee
select 109, null, 1, 'Top Dog' union all
select 12, 109, 1, 'Second Dog' union all
select 3, 12, 1, '2-Dog''s Dog' union all
select 4, 3, 1, 'Just Another Dog' union all
select 9, 3, 1, 'Just Another Dog' union all
select 11, 3, 1, 'Just Another Dog' union all
select 158, 3, 1, 'Just Another Dog' union all
select 79, 158, 1, 'Just Another Dog' union all
select 114, 158,1, 'Just Another Dog' union all
select 217, 158,1, 'Just Another Dog' union all
select 263, 3, 1, 'Just Another Dog' union all
select 5, 263, 1, 'Just Another Dog' union all
select 265,263, 1, 'Just Another Dog' union all
select 267, 3, 1, 'Just Another Dog' union all
select 270, 3, 1, 'Just Another Dog'
--select * from @.employee

;with hierarchyCTE as
( select 1 as level,
EmployeeId,
ManagerId,
DepartmentId,
Title,
cast(right(' ' + convert(varchar(11), employeeId), 11) as varchar(240))
as path
from @.employee
where ManagerId is null
union all
select a.level + 1,
b.EmployeeId,
a.EmployeeId,
b.DepartmentId,
b.Title,
cast( a.Path + '/' +
right(' ' + convert(varchar(11), b.employeeId), 11)
as varchar(240))
from hierarchyCTE a
join @.employee b
on a.EmployeeId = b.ManagerId
), sequenceCTE as
( select row_number() over (order by path) as Seq,
level,
EmployeeId,
ManagerId,
DepartmentId,
Title,
Path
from hierarchyCTE
), maxSeqCTE as
( select max(seq) as maxSeq
from sequenceCTE
), nextLevelCTE as
( select -1 + row_number() over (order by path) as Seq,
Level
from hierarchyCTE
union all
select seq,
1
from sequenceCTE
join maxSeqCTE
on seq = maxSeq
), outputCTE as
( select cast(a.Seq as integer) as Seq1,
1 as Seq2,
a.level,
b.level as nextLevel,
a.EmployeeId,
a.ManagerId,
a.DepartmentId,
a.Title,
a.Path
from sequenceCTE a
join nextLevelCTE b
on a.Seq = b.Seq
union all
select Seq1,
Seq2 + 1,
level - 1,
nextLevel,
EmployeeId,
ManagerId,
DepartmentId,
Title,
Path
from outputCte
where level > nextLevel
)
select replicate (' ', Level - 1) +
case when Seq2 = 1
then '<Emp ' + 'ManagerID="' +
case when ManagerId is null then ''
else convert(varchar(11), ManagerId)
end + '" EmployeeId="' +
convert(varchar(11), EmployeeId) +
'" DepartmentId="' +
convert(varchar(11), Departmentid) +
'" Title="' +
case when Title is null then ''
else Title
end + '"' +
case when level >= nextLevel
then '/' else ''
end + '>'
else '</Emp>'
end as xmlOutput
from outputCTE
order by Seq1, Seq2

/*
xmlOutput
--
<Emp ManagerID="" EmployeeId="109" DepartmentId="1" Title="Top Dog">
<Emp ManagerID="109" EmployeeId="12" DepartmentId="1" Title="Second Dog">
<Emp ManagerID="12" EmployeeId="3" DepartmentId="1" Title="2-Dog's Dog">
<Emp ManagerID="3" EmployeeId="4" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="9" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="11" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="158" DepartmentId="1" Title="Just Another Dog">
<Emp ManagerID="158" EmployeeId="79" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="158" EmployeeId="114" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="158" EmployeeId="217" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
<Emp ManagerID="3" EmployeeId="263" DepartmentId="1" Title="Just Another Dog">
<Emp ManagerID="263" EmployeeId="5" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="263" EmployeeId="265" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
<Emp ManagerID="3" EmployeeId="267" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="270" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
</Emp>
</Emp>
*/

|||

Kent,

Isn't there a way to simplify that solution?

The actual code lenght?

looking for nested XML document as output on a recursive query from sql table

Is it possible to finally have a nested XML document on a recursive query? Let's say that we have a classic parent-child relationship inside a table that is self-refernced. An example of this situation can be found in the Employees table of Northwind DB or in the shining new AdventureWorks DB, HumanResources.Employee table.
In both cases we have somthing like that:

EmployeeID ManagerID Title Firstname LastName

Where ManagerID refers to an existing EmployeeID. I'd like to create and organizational tree.

Using a query like that:

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level, Path)
AS
(
SELECT
ManagerID, EmployeeID, Title, DepartmentID, 0 AS Level, CAST(EmployeeID AS VARCHAR(MAX)) AS Path
FROM
HumanResources.Employee
WHERE
ManagerID IS NULL

UNION ALL

SELECT
e.ManagerID, e.EmployeeID, e.Title, e.DepartmentID, Level + 1, Path + '/'+ CAST(e.EmployeeID AS VARCHAR(MAX))
FROM
HumanResources.Employee AS e
JOIN
DirectReports AS d
ON
e.ManagerID = d.EmployeeID
)
SELECT *
FROM DirectReports
ORDER BY Path

The query is a sample one present in the BOL and modified a little. I also remeber the I've see something similar to a document present at the MSDN site.

Now i'd like to have this result in XML, with the correct nesting:

<Emp ManagerID="109" EmployeeID="12" ... >
<Emp ManagerID="12" EmployeeID="3" ... >
<Emp ManagerID="3" EmployeeID="4" ... />
<Emp ManagerID="3" EmployeeID="9" ... />
<Emp ManagerID="3" EmployeeID="11" ... />
<Emp ManagerID="3" EmployeeID="158" ... >
<Emp ManagerID="158" EmployeeID="79" ... />
<Emp ManagerID="158" EmployeeID="114" ... />
<Emp ManagerID="158" EmployeeID="217" ... />
</Emp>
<Emp ManagerID="3" EmployeeID="263" ... >
<Emp ManagerID="263" EmployeeID="5" ... />
<Emp ManagerID="263" EmployeeID="265" ... />
</Emp>
<Emp ManagerID="3" EmployeeID="267" ... />
<Emp ManagerID="3" EmployeeID="270" ... />
</Emp>
</Emp>

but without the need to use SQLXML (as I did to obtain the result above).
I've made some attempts, but seems impossible! :-(

Using FOR XML clause will produce a similar xml document but with no nesting, will I was expecting it since a simple FOR XML on a simple JOIN does create them.

It works using UDF but UDF has limit like maximum of 32 recursively nested function invocations. So it will not work for unlimited depth.

Feedback appreciated :-)

vshah:

I am really just starting to learn XML and so I am not sure that I understand your question correctly. Here is some code that seems to produce the output you have requested. Would you please give this a look and tell me if this is headed in the right direction?

Code Snippet

declare @.employee table
( EmployeeId integer,
ManagerId integer,
DepartmentId integer,
Title varchar(18)
)
insert into @.employee
select 109, null, 1, 'Top Dog' union all
select 12, 109, 1, 'Second Dog' union all
select 3, 12, 1, '2-Dog''s Dog' union all
select 4, 3, 1, 'Just Another Dog' union all
select 9, 3, 1, 'Just Another Dog' union all
select 11, 3, 1, 'Just Another Dog' union all
select 158, 3, 1, 'Just Another Dog' union all
select 79, 158, 1, 'Just Another Dog' union all
select 114, 158,1, 'Just Another Dog' union all
select 217, 158,1, 'Just Another Dog' union all
select 263, 3, 1, 'Just Another Dog' union all
select 5, 263, 1, 'Just Another Dog' union all
select 265,263, 1, 'Just Another Dog' union all
select 267, 3, 1, 'Just Another Dog' union all
select 270, 3, 1, 'Just Another Dog'
--select * from @.employee

;with hierarchyCTE as
( select 1 as level,
EmployeeId,
ManagerId,
DepartmentId,
Title,
cast(right(' ' + convert(varchar(11), employeeId), 11) as varchar(240))
as path
from @.employee
where ManagerId is null
union all
select a.level + 1,
b.EmployeeId,
a.EmployeeId,
b.DepartmentId,
b.Title,
cast( a.Path + '/' +
right(' ' + convert(varchar(11), b.employeeId), 11)
as varchar(240))
from hierarchyCTE a
join @.employee b
on a.EmployeeId = b.ManagerId
), sequenceCTE as
( select row_number() over (order by path) as Seq,
level,
EmployeeId,
ManagerId,
DepartmentId,
Title,
Path
from hierarchyCTE
), maxSeqCTE as
( select max(seq) as maxSeq
from sequenceCTE
), nextLevelCTE as
( select -1 + row_number() over (order by path) as Seq,
Level
from hierarchyCTE
union all
select seq,
1
from sequenceCTE
join maxSeqCTE
on seq = maxSeq
), outputCTE as
( select cast(a.Seq as integer) as Seq1,
1 as Seq2,
a.level,
b.level as nextLevel,
a.EmployeeId,
a.ManagerId,
a.DepartmentId,
a.Title,
a.Path
from sequenceCTE a
join nextLevelCTE b
on a.Seq = b.Seq
union all
select Seq1,
Seq2 + 1,
level - 1,
nextLevel,
EmployeeId,
ManagerId,
DepartmentId,
Title,
Path
from outputCte
where level > nextLevel
)
select replicate (' ', Level - 1) +
case when Seq2 = 1
then '<Emp ' + 'ManagerID="' +
case when ManagerId is null then ''
else convert(varchar(11), ManagerId)
end + '" EmployeeId="' +
convert(varchar(11), EmployeeId) +
'" DepartmentId="' +
convert(varchar(11), Departmentid) +
'" Title="' +
case when Title is null then ''
else Title
end + '"' +
case when level >= nextLevel
then '/' else ''
end + '>'
else '</Emp>'
end as xmlOutput
from outputCTE
order by Seq1, Seq2

/*
xmlOutput
--
<Emp ManagerID="" EmployeeId="109" DepartmentId="1" Title="Top Dog">
<Emp ManagerID="109" EmployeeId="12" DepartmentId="1" Title="Second Dog">
<Emp ManagerID="12" EmployeeId="3" DepartmentId="1" Title="2-Dog's Dog">
<Emp ManagerID="3" EmployeeId="4" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="9" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="11" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="158" DepartmentId="1" Title="Just Another Dog">
<Emp ManagerID="158" EmployeeId="79" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="158" EmployeeId="114" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="158" EmployeeId="217" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
<Emp ManagerID="3" EmployeeId="263" DepartmentId="1" Title="Just Another Dog">
<Emp ManagerID="263" EmployeeId="5" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="263" EmployeeId="265" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
<Emp ManagerID="3" EmployeeId="267" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="270" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
</Emp>
</Emp>
*/

|||

Kent,

Isn't there a way to simplify that solution?

The actual code lenght?

looking for nested XML document as output on a recursive query from sql table

Is it possible to finally have a nested XML document on a recursive query? Let's say that we have a classic parent-child relationship inside a table that is self-refernced. An example of this situation can be found in the Employees table of Northwind DB or in the shining new AdventureWorks DB, HumanResources.Employee table.
In both cases we have somthing like that:

EmployeeID ManagerID Title Firstname LastName

Where ManagerID refers to an existing EmployeeID. I'd like to create and organizational tree.

Using a query like that:

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level, Path)
AS
(
SELECT
ManagerID, EmployeeID, Title, DepartmentID, 0 AS Level, CAST(EmployeeID AS VARCHAR(MAX)) AS Path
FROM
HumanResources.Employee
WHERE
ManagerID IS NULL

UNION ALL

SELECT
e.ManagerID, e.EmployeeID, e.Title, e.DepartmentID, Level + 1, Path + '/'+ CAST(e.EmployeeID AS VARCHAR(MAX))
FROM
HumanResources.Employee AS e
JOIN
DirectReports AS d
ON
e.ManagerID = d.EmployeeID
)
SELECT *
FROM DirectReports
ORDER BY Path

The query is a sample one present in the BOL and modified a little. I also remeber the I've see something similar to a document present at the MSDN site.

Now i'd like to have this result in XML, with the correct nesting:

<Emp ManagerID="109" EmployeeID="12" ... >
<Emp ManagerID="12" EmployeeID="3" ... >
<Emp ManagerID="3" EmployeeID="4" ... />
<Emp ManagerID="3" EmployeeID="9" ... />
<Emp ManagerID="3" EmployeeID="11" ... />
<Emp ManagerID="3" EmployeeID="158" ... >
<Emp ManagerID="158" EmployeeID="79" ... />
<Emp ManagerID="158" EmployeeID="114" ... />
<Emp ManagerID="158" EmployeeID="217" ... />
</Emp>
<Emp ManagerID="3" EmployeeID="263" ... >
<Emp ManagerID="263" EmployeeID="5" ... />
<Emp ManagerID="263" EmployeeID="265" ... />
</Emp>
<Emp ManagerID="3" EmployeeID="267" ... />
<Emp ManagerID="3" EmployeeID="270" ... />
</Emp>
</Emp>

but without the need to use SQLXML (as I did to obtain the result above).
I've made some attempts, but seems impossible! :-(

Using FOR XML clause will produce a similar xml document but with no nesting, will I was expecting it since a simple FOR XML on a simple JOIN does create them.

It works using UDF but UDF has limit like maximum of 32 recursively nested function invocations. So it will not work for unlimited depth.

Feedback appreciated :-)

vshah:

I am really just starting to learn XML and so I am not sure that I understand your question correctly. Here is some code that seems to produce the output you have requested. Would you please give this a look and tell me if this is headed in the right direction?

Code Snippet

declare @.employee table
( EmployeeId integer,
ManagerId integer,
DepartmentId integer,
Title varchar(18)
)
insert into @.employee
select 109, null, 1, 'Top Dog' union all
select 12, 109, 1, 'Second Dog' union all
select 3, 12, 1, '2-Dog''s Dog' union all
select 4, 3, 1, 'Just Another Dog' union all
select 9, 3, 1, 'Just Another Dog' union all
select 11, 3, 1, 'Just Another Dog' union all
select 158, 3, 1, 'Just Another Dog' union all
select 79, 158, 1, 'Just Another Dog' union all
select 114, 158,1, 'Just Another Dog' union all
select 217, 158,1, 'Just Another Dog' union all
select 263, 3, 1, 'Just Another Dog' union all
select 5, 263, 1, 'Just Another Dog' union all
select 265,263, 1, 'Just Another Dog' union all
select 267, 3, 1, 'Just Another Dog' union all
select 270, 3, 1, 'Just Another Dog'
--select * from @.employee

;with hierarchyCTE as
( select 1 as level,
EmployeeId,
ManagerId,
DepartmentId,
Title,
cast(right(' ' + convert(varchar(11), employeeId), 11) as varchar(240))
as path
from @.employee
where ManagerId is null
union all
select a.level + 1,
b.EmployeeId,
a.EmployeeId,
b.DepartmentId,
b.Title,
cast( a.Path + '/' +
right(' ' + convert(varchar(11), b.employeeId), 11)
as varchar(240))
from hierarchyCTE a
join @.employee b
on a.EmployeeId = b.ManagerId
), sequenceCTE as
( select row_number() over (order by path) as Seq,
level,
EmployeeId,
ManagerId,
DepartmentId,
Title,
Path
from hierarchyCTE
), maxSeqCTE as
( select max(seq) as maxSeq
from sequenceCTE
), nextLevelCTE as
( select -1 + row_number() over (order by path) as Seq,
Level
from hierarchyCTE
union all
select seq,
1
from sequenceCTE
join maxSeqCTE
on seq = maxSeq
), outputCTE as
( select cast(a.Seq as integer) as Seq1,
1 as Seq2,
a.level,
b.level as nextLevel,
a.EmployeeId,
a.ManagerId,
a.DepartmentId,
a.Title,
a.Path
from sequenceCTE a
join nextLevelCTE b
on a.Seq = b.Seq
union all
select Seq1,
Seq2 + 1,
level - 1,
nextLevel,
EmployeeId,
ManagerId,
DepartmentId,
Title,
Path
from outputCte
where level > nextLevel
)
select replicate (' ', Level - 1) +
case when Seq2 = 1
then '<Emp ' + 'ManagerID="' +
case when ManagerId is null then ''
else convert(varchar(11), ManagerId)
end + '" EmployeeId="' +
convert(varchar(11), EmployeeId) +
'" DepartmentId="' +
convert(varchar(11), Departmentid) +
'" Title="' +
case when Title is null then ''
else Title
end + '"' +
case when level >= nextLevel
then '/' else ''
end + '>'
else '</Emp>'
end as xmlOutput
from outputCTE
order by Seq1, Seq2

/*
xmlOutput
--
<Emp ManagerID="" EmployeeId="109" DepartmentId="1" Title="Top Dog">
<Emp ManagerID="109" EmployeeId="12" DepartmentId="1" Title="Second Dog">
<Emp ManagerID="12" EmployeeId="3" DepartmentId="1" Title="2-Dog's Dog">
<Emp ManagerID="3" EmployeeId="4" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="9" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="11" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="158" DepartmentId="1" Title="Just Another Dog">
<Emp ManagerID="158" EmployeeId="79" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="158" EmployeeId="114" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="158" EmployeeId="217" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
<Emp ManagerID="3" EmployeeId="263" DepartmentId="1" Title="Just Another Dog">
<Emp ManagerID="263" EmployeeId="5" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="263" EmployeeId="265" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
<Emp ManagerID="3" EmployeeId="267" DepartmentId="1" Title="Just Another Dog"/>
<Emp ManagerID="3" EmployeeId="270" DepartmentId="1" Title="Just Another Dog"/>
</Emp>
</Emp>
</Emp>
*/

|||

Kent,

Isn't there a way to simplify that solution?

The actual code lenght?